locked
RefCursor in CLOUMN from select !? RRS feed

  • Question

  • User-785466571 posted

    Hello
     Is it possible to access to column with CURSOR by ODP.NET ?

     Below is select which will be return by function, but the results have also cursors in 1 column. IS IT POSSIBLE to access to all returned data?

     EXAMPLE:

     CREATE OR REPLACE FUNCTION SDMS_DB_TEST.F_TEL return Sys_Refcursor IS
     outRecordset Sys_Refcursor;
     BEGIN

     OPEN outRecordset for
     select
     3,4
     from dual;

     return outRecordset;

     END F_TEL;

     select
     1,
     2,
     F_TEL() as cur
     from dual;

    Friday, August 5, 2011 8:58 AM

All replies

  • User269602965 posted

    Example

    Get all Birdnames from a Lookup table LKUP_BIRDNAME

     

    VB.NET code in ASP.NET application
    
        ' Get data from stored procedure '
        Try
          Dim connstr As String = ConfigurationManager.ConnectionStrings("{OracleConnectionStringNameFromWebConfig}").ConnectionString
          Using conn As New OracleConnection(connstr)
            Using cmd As New OracleCommand("{SCHEMANAME}.{PACKAGENAME}.rcSelectBirdNames", conn)
              cmd.CommandType = CommandType.StoredProcedure
              cmd.Parameters.Clear()
              cmd.Parameters.Add("ListBirdNames", OracleDbType.RefCursor, ParameterDirection.Output)
              conn.Open()
              Using oda As New OracleDataAdapter(cmd)
                Dim ds As New DataSet()
                oda.Fill(ds)
                Me.RadGrid1.MasterTableView.DataSource = ds.Tables(0)
              End Using
            End Using
          End Using
        Catch ex As Exception
        End Try
    
    Oracle PL/SQL code
        
    CREATE OR REPLACE PACKAGE {SCHEMANAME}.{PACKAGENAME} AS TYPE refCursor IS REF CURSOR;
      PROCEDURE rcSelectBirdNames(ListBirdNames OUT refCursor);
    END {PACKAGENAME};
    /
    
    CREATE OR REPLACE PACKAGE BODY {PACKAGENAME} AS
    
    PROCEDURE rcSelectBirdNames(ListBirdNames OUT refCursor)
    IS
    BEGIN
      OPEN ListBirdNames FOR
        SELECT BIRDNAME FROM {SCHEMANAME}.LKUP_BIRDNAME;
    END;
    
    END {PACKAGENAME};
    /
    
    Friday, August 5, 2011 9:02 PM
  • User-785466571 posted

    Thanks a lot for answer. Unfortunately I don't want to use PACKAGE. Is there any other way to do the trick?

    Tuesday, August 9, 2011 2:56 AM
  • User269602965 posted

    You do not have to call a package (collection of PL/SQL procedures)

    You can call a single PL/SQL procedure as well.

    Tuesday, August 9, 2011 12:54 PM
  • User-785466571 posted

    When I used Your solution with my procedure above I'm getting error:

    'NullReferenceException was unhandled by user code

    Object reference not set to an instance of an object.'

    in line when I'm filling DataSet with OracleDataAdapter (oda.Fill(ds);)

    But when I comment only function F_TEL() like this:

    CREATE OR REPLACE PROCEDURE P_TEST_Q( outRecordset OUT Sys_Refcursor) IS
    tmpVar NUMBER;
    
    BEGIN
       OPEN outRecordset for 
           select 1,2 
            --,F_TEL() as cur 
            from dual;
    
    END P_TEST_Q;

    which returns nested cursor, everything works fine. 

    Wednesday, August 10, 2011 2:57 AM
  • User269602965 posted

    True, my birdname list has no NULL values.

    Oracle has NVL function to handle NULLs o the fly.

    Wednesday, August 10, 2011 9:25 PM
  • User-785466571 posted

    You are right, i know this function but my F_TEL() function has no NULL values also

    OPEN outRecordset for 
      select 
      3,4 
      from dual;
    
     return outRecordset;


    This function always return vaules '3' and '4', so I think this is not reason why I get NullRefference;

    Thursday, August 11, 2011 7:32 AM