Get Dataset from Stored Procedure in Oracle

Stored Procedure (SP) can return data through one variable. We need to get dataset some times. Only one way to get the target is return cursor which can store dataset in SP.

A cursor is a mechanism by which you can assign a name to a “Select statement” and manipulate the information with that SQL statement.

l  Following is an example.

CREATE OR REPLACEPROCEDURE SP_NAME(

                                             , TEST1 OUT sys_refcursor //refers to a cursor that can be pass cursors from and to a SP

                                )

AS

v_created VARCHAR2(100);

 

BEGIN

OPEN TEST1 FOR SELECT created  FROM TABLE_NAME;

   

LOOP

FETCH TEST1 INTO v_created;

EXIT WHEN TEST1%NOTFOUND;

dbms_output.put_line(v_created);

END LOOP;

CLOSE TEST1;

 

END SP_TEMPLATE;

/

l  Using the SQL to execute the SP

DECLARE

  TEST1 sys_refcursor;

BEGIN

  SYSTEM.KTEST (TEST1 );

END;

 

l  Use the SQL1 to initial a sys_refcursor, also a string can be used to initial a sys_refcursor as SQL2

SQL1:

OPENTEST1 FOR SELECT created  FROM TABLE_NAME;

 

SQL2:

         initialStr VARCHAR2(100);

         initialStr := 'SELECT created

                FROM siebel.s_srv_req';

OPENTEST1 FOR initialStr;

Note: If you want to call the SP with cursor with JAVA, remove the code after LOOP clause.