title: Reference cursor and Stored procedures in Oracle
body: <p>I have written a simple stored procedure in PL/SQL to perform the equivalent of </p>
  
  <pre><code>   SELECT * FROM tablename
  </code></pre>
  
  <p>Here is the procedure that returns a reference cursor</p>
  
  <pre><code>   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;
  </code></pre>
  
  <p>Now when i want to execute my procedure in the SQL Command Prompt without including the <strong>line in quotes</strong> in my code , I go about doing it this way</p>
  
  <pre><code>   VARIABLE cr REFCURSOR
     EXEC proc_name(:cr)
  </code></pre>
  
  <p>Now i get a prompt saying </p>
  
  <pre><code>   PL/SQL procedure successfully completed
  </code></pre>
  
  <p>Now when i try executing the command</p>
  
  <pre><code>   print cr;
  </code></pre>
  
  <p>I get an error</p>
  
  <pre><code>   ORA-24338: Statement handle not executed
  </code></pre>
  
  <p>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 <strong>line in quotes</strong> from the procedure all together .</p>