Life, Universe and Everything Distributed.

// TODO: Place some witty description about Sajay's Blog here.

Oracle and ref cursors in .NET

Now when unlike SQL server, filling datasets were a little different on Oracle Stored Procedures.

For starters lets define and procedure.

The sys_refcursor is an object in Oracle 9i that would be used to fill the dataset.

 

 

CREATE

OR REPLACE PROCEDURE sp_getItems (

   p_refcursor   OUT   sys_refcursor,

)

IS

BEGIN

   OPEN p_refcursor

    FOR

       SELECT itemid FROM itemmas;

END;

 

 

From now on calling the procedure would be the same.

 

 

 OrCmd.CommandText = "sp_getItems"

 OrCmd.CommandType = CommandType.StoredProcedure

 OrCmd.Parameters.Add(new OracleParameter("rc",OracleType.Cursor)).Direction =           ParameterDirection.Output 

 OrDA.Fill(lDbDs, Invoice)

 

 

 

Now just a tip for testing the sp in SQL+ is as follow

 

 

SQL> var rc refcursor;

SQL> exec sp_getItems(:rc);

SQL> print rc;