Using a Ref Cursor in a .NET Application v4.0.10.2
A ref cursor
is a cursor variable that contains a pointer to a query result set. The result set is determined by the execution of the OPEN FOR
statement using the cursor variable. A cursor variable is not tied to a particular query like a static cursor. The same cursor variable may be opened a number of times with the OPEN FOR
statement containing different queries and each time, a new result set will be created for that query and made available via the cursor variable. There are two ways to declare a cursor variable:
- Use the
SYS_REFCURSOR
built-in data type to declare a weakly-typed ref cursor. - Define a strongly-typed ref cursor that declares a variable of that type.
SYS_REFCURSOR
is a ref cursor type that allows any result set to be associated with it. This is known as a weakly-typed ref cursor. The following example is a declaration of a weakly-typed ref cursor:
name SYS_REFCURSOR
;
Following is an example of a strongly-typed ref cursor:
TYPE <cursor_type_name> IS REF CURSOR RETURN emp%ROWTYPE
;
Creating the Stored Procedure
The following sample code creates a stored procedure called refcur_inout_callee
. To create the sample procedure, invoke EDB-PSQL and connect to the Advanced Server host database. Enter the following SPL code at the command line:
To use the above defined procedure from .NET code, you must specify the data type of the ref cursor being passed as an IN
parameter, as shown in the above script.
The following C# code uses the stored procedure to retrieve employee names from the emp
table:
The following .NET code snippet displays the result on the console:
Please note that you must bind the EDBDbType.RefCursor
type in EDBParameter()
if you are using a ref cursor parameter.