How to use REF CURSOR in PL/SQL?

Post Info: 1,563 views 0 Comments Post a comment

A REF CURSOR is basically a data type for creating Cursor variables. A cursor variable can be associated with different queries at run-time.  The primary advantage of using cursor variables is their capability to pass result sets between sub programs like stored procedures, functions, packages etc.

Here is  a simple example to implement REF CURSOR in PL/SQL programming.

First create a Procedure with REF CURSOR as IN OUT Parameter.

CREATE OR REPLACE PROCEDURE rc_proc (rc_data IN OUT SYS_REFCURSOR)
IS
BEGIN
OPEN rc_data FOR
SELECT col1, col2
FROM table1;
END;

Then declare a Variable of REF CURSOR type.  Run the procedure created above with the variable as Parameter.

VARIABLE rc REFCURSOR
EXEC rc_proc(:rc)

Then print the cursor variable which will print the results from the SQL used in the procedure.

PRINT rc

There are lot of examples in java2s.com website.

Return to top

Leave a Reply

 

Are you finished?

Return to top