How to return a RefCursor from Oracle function?

Question

I am trying to execute a user-defined Oracle function that returns a RefCursor using ODP.NET. Here is the function:

CREATE OR REPLACE FUNCTION PKG.FUNC_TEST (ID IN TABLE.ID%type)
   RETURN SYS_REFCURSOR
AS
   REF_TEST   SYS_REFCURSOR;
BEGIN
   OPEN REF_TEST FOR
      SELECT   *
        FROM   TABLE;
   RETURN REF_TEST;
END;
/

I can call this function in Toad (select func_test(7) from dual) and get back a CURSOR. But I need to get the cursor using C# and ODP.NET to fill a DataSet, but I keep getting a NullReferenceException - "Object reference not set to an instance of an object". Here is what I have for that:

OracleConnection oracleCon = new OracleConnection(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString);
OracleCommand sqlCom = new OracleCommand("select func_test(7) from dual", oracleCon);
sqlCom.Parameters.Add("REF_TEST", OracleDbType.RefCursor, ParameterDirection.ReturnValue);
OracleDataAdapter dataAdapter = new OracleDataAdapter();
dataAdapter.SelectCommand = sqlCom;

DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet);  //FAILS HERE with NullReferenceException

I was able to find lots of info and samples on using stored procedures and ODP.NET, but not so much for returning RefCursors from functions.

EDIT: I do not want to explicitly add input parameters to the OracleCommand object (i.e. sqlCom.Parameters.Add("id", OracleDbType.Int32,ParameterDirection.Input).Value = 7;) as that makes it difficult to implement this as a generic RESTful web service, but I'm reserving it as my last resort but would use stored procedures instead.

Any help is much appreciated!

You're adding a parameter but you're running it as a query; don't you want to do something like :REF_TEST := func_test(7)? — Alex Poole
– Alex Poole, Commented Nov 8, 2010 at 17:10
Not sure where to use this. Is this used instead of the parameter? — Gady
– Gady, Commented Nov 8, 2010 at 17:21
as the OracleCommand string, instead of the select. (I should maybe point out I know nothing about ODP.Net, but that still looks wrong *8-) — Alex Poole
– Alex Poole, Commented Nov 8, 2010 at 17:24

Dan · Accepted Answer · 2015-11-12 20:46:57Z

15

I think you are missing the sqlCom.ExecuteNonQuery();

also, instead of running the select func_test(7) from dual; lets switch it to run the function and pass in the param

  OracleConnection oracleCon = new OracleConnection(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString);


  // Set the command

  string anonymous_block = "begin " +
                              "  :refcursor1 := func_test(7) ;" +
                              "end;";  
 //fill in your function and variables via the above example
  OracleCommand sqlCom= con.CreateCommand();
  sqlCom.CommandText = anonymous_block;

  // Bind 
  sqlCom.Parameters.Add("refcursor1", OracleDbType.RefCursor);
  sqlCom.Parameters[0].Direction = ParameterDirection.ReturnValue;

  try 
  {
    // Execute command; Have the parameters populated
    sqlCom.ExecuteNonQuery();

    // Create the OracleDataAdapter
    OracleDataAdapter da = new OracleDataAdapter(sqlCom);

    // Populate a DataSet with refcursor1.
    DataSet ds = new DataSet();
    da.Fill(ds, "refcursor1", (OracleRefCursor)(sqlCom.Parameters["refcursor1"].Value));

    // Print out the field count the REF Cursor
    Console.WriteLine("Field count: " + ds.Tables["refcursor1"].Columns.Count);
  }
  catch (Exception e)
  {
    Console.WriteLine("Error: {0}", e.Message);
  }
  finally
  {
    // Dispose OracleCommand object
    cmd.Dispose();

    // Close and Dispose OracleConnection object
    con.Close();
    con.Dispose();}

this is based on the example ODP that can be found @ %ora_home%\Client_1\ODP.NET\samples\RefCursor\Sample5.csproj

If you want to avoid (for better or worst!) the custom built param collection for each proc/function call you can get around that by utilizing anonymous blocks in your code, I have ammended (once again untested!) the code above to reflect this technique. Here is a nice blog (from none other than Mark Williams) showing this technique. http://oradim.blogspot.com/2007/04/odpnet-tip-anonymous-plsql-and.html

edited Nov 12, 2015 at 20:46

Dan

2,8437 gold badges42 silver badges56 bronze badges

answered Nov 8, 2010 at 18:41

Harrison

9,1401 gold badge34 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Gady Over a year ago

This could work, but as this will be used in a RESTful web service, I'm trying to avoid the explicit adding of input parameters (i.e. sqlCom.Parameters.Add("id", OracleDbType.Int32,ParameterDirection.Input).Value = 7;) so I can easily build a SQL statement on the fly.

Harrison Over a year ago

@Guddie, you can use anonymous blocks to give it the 'generic' feel you are going for. I have changed the above example to reflect this

Gady Over a year ago

YES! You win! Exactly what I was looking for!

Collectives™ on Stack Overflow

How to return a RefCursor from Oracle function?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related