Reference cursor and Stored procedures in Oracle

Question

I have written a simple stored procedure in PL/SQL to perform the equivalent of

   SELECT * FROM tablename

Here is the procedure that returns a reference cursor

   create or replace
   procedure proc_name (outVal OUT sys_refcursor) as
   myCursor sys_refcursor;
   myRecords records%ROWTYPE;
   l_sql VARCHAR(2000);
   begin
        l_sql := 'SELECT * FROM RECORDS';
        open myCursor for l_sql;
        loop
            FETCH myCursor INTO myRecords;
            EXIT WHEN myCursor%NOTFOUND;
            "DBMS_OUTPUT.put_line('myRecords.name =' || myRecords.name);"
        end loop;
   close myCursor;
   end;

Now when i want to execute my procedure in the SQL Command Prompt without including the line in quotes in my code , I go about doing it this way

   VARIABLE cr REFCURSOR
   EXEC proc_name(:cr)

Now i get a prompt saying

   PL/SQL procedure successfully completed

Now when i try executing the command

   print cr;

I get an error

   ORA-24338: Statement handle not executed

Please help me with this . I need to know how to print the table using just this reference cursor returned by my stored procedure . I do not want to print anything within the procedure itself and hence want to remove the line in quotes from the procedure all together .

Vincent Malgrat · Accepted Answer · 2012-07-10 09:19:59Z

1

You get an error because the cursor is closed by the procedure, therefore it can't be "printed". I would suggest the following:

always run SET SERVEROUTPUT ON before executing your procedures in SQL*Plus so that you can see your DBMS_OUTPUT log. Use this output for debugging/logging.

if you want to return a cursor that will be displayed later, don't fetch the cursor in the procedure:

SQL> CREATE OR REPLACE PROCEDURE p (x OUT SYS_REFCURSOR) IS
  2  BEGIN
  3     OPEN x FOR SELECT * FROM DUAL;
  4  END;
  5  /

Procedure created.

SQL> VARIABLE x REFCURSOR;
SQL> EXEC p(:x);

PL/SQL procedure successfully completed.

SQL> PRINT x;

DUM
---
X

Note that it is the responsibility of the calling application (here SQL*Plus PRINT) to fetch and close the cursor properly, not the PL/SQL procedure.

answered Jul 10, 2012 at 9:19

Vincent Malgrat

67.9k9 gold badges122 silver badges176 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Reference cursor and Stored procedures in Oracle

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related