RefCursor in CLOUMN from select !?

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;

Answer 1

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};
/

Answer 2

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?

Answer 3

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.

Answer 4

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. 

Answer 5

User269602965 posted

True, my birdname list has no NULL values.

Oracle has NVL function to handle NULLs o the fly.

Answer 6

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;