Using a Ref Cursor in a .NET Application v188.8.131.52
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_REFCURSORbuilt-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:
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
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.