Using function returning a SYS_REFCURSOR type

Using function returning a SYS_REFCURSOR type

Postby Sergey Schukin » Mon 08 Feb 2010 14:24

I have a function:

Code: Select all
CREATE OR REPLACE PACKAGE BODY TEST.PKG_MY
IS
   FUNCTION GetRegions
      RETURN SYS_REFCURSOR
   IS
      cur   SYS_REFCURSOR;
   BEGIN
      OPEN cur FOR
         SELECT *
           FROM REGIONS;

      RETURN cur;
   END GetRegions;
END PKG_MY;


How do I fill the DataTable using Devart components? Thanks.
Sergey Schukin
 
Posts: 10
Joined: Thu 22 Jan 2009 12:10

Postby StanislavK » Tue 09 Feb 2010 10:51

Provided you have an active OracleConnection object "connection", you can use the following code:

Code: Select all
// Create an OraclePackage object corresponding to your package
OraclePackage pack = new OraclePackage();
pack.Connection = connection;
pack.PackageName = "PKG_MY";

// Get the collection of parameters needed to execute your function
OracleParameterCollection parms = pack.DescribeProcedure("GetRegions");

// Execute the function, get the resulting object, which is of the OracleCursor type
object o = pack.ExecuteProcedure("GetRegions", parms);
OracleCursor cursor = (OracleCursor)o;

// Get DataReader from the OracleCursor object and populate DataTable with the reader:
OracleDataReader reader = cursor.GetDataReader();
DataTable dt = new DataTable();
dt.Load(reader);
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48


Return to dotConnect for Oracle