Using a Ref Cursor in a .NET Application v4.1.6.1
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
. It specifies the data type of the ref cursor being passed as an OUT parameter. 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:
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.