title: Quick-n-dirty results: View results of Procedure OUT cursor in SQL Worksheet?
body: <p>Platform: Oracle<br>
  Language: PL/SQL<br>
  Issue: Want to output a procedure OUT cursor into the SQLDeveloper SQLWosksheet. </p>
  
  <p>Anyone know how to use the Oracle "Select * from Table( PipelinedFunction( Param ) ) " to check procedure code output cursors?</p>
  
  <p>I am using Crsytal Reports off of an Oracle stored procedure.  Crystal requires that a procedure return a cursor, which it fetchs and reads.</p>
  
  <p>The procedure code I have is currently working, but I want to find the easiest way to view the effects of changes to the procedure code.  I have SQLDeveloper available, and I'm doing my creation and sql testing in that.  I would like to get a quick result visible in the SQL Developer Query Result window ("SQL Worksheet").</p>
  
  <p>Is there a (simple) way to use a Function to read the cursor from the procedure? (and pipe that out to the Table function?)</p>
  
  <p>Convoluted, I know, but I deal best when I can just see the results of code changes.  If I can view the record results directly, it will speed up development of the report.</p>
  
  <p>I know of the Table function and a little about pipelining in Oracle.  I know a little about cursors in general and sys_refcursor.  I know diddly about types and why I need them.  (Isn't sys_regCursor supposed to get us away from that?)</p>
  
  <p>The current procedure does an adequate but ungraceful series of queries, inserts to global temp tables (GTT), joins from GTT and original tables, more inserts, and more self-joins and then SELECTS the results into the OUT cursor.  I might be able to do better relying on just cursors and such, but the current method is good enough to get results to the report.</p>
  
  <p>I think I can handle SQL pretty well (for our purposes), but I am not an Oracle-specific developer...  but I need help.</p>
  
  <p>Anybody run across this?  The whole idea was to speed my development for the procedure code, but I've spent a couple of days looking for a way to just get at the output... not what I had in mind.</p>
  
  <p>Update:<br>
    I have tried some hare-brained schemes based on slivers that I've seen on the web... such as</p>
  
  <pre><code>Create or replace FUNCTION GET_BACKPLANE (
     Node VARCHAR2 ) RETURN SYS_REFCURSOR
  AS
  
  RESULTS SYS_REFCURSOR;
  
  BEGIN
      Open Results for
          Select Backplane(Results, Node) from Dual ;
      ... etc.
  </code></pre>
  
  <p>and</p>
  
  <pre><code>Create or replace Function GET_BACKPLANE ( 
      NODE VARCHAR2 )  RETURN  My_Table_Stru%ROWTYPE  PIPELINED
  AS
  
  BEGIN ...
  </code></pre>
  
  <p>I don't think that Oracle is even considering letting me re-reference the output cursor from the procedure ("Results" is a sys_refcursor that holds the results of the last SELECT in the procedure).  I don't know how to define it, open it, and reference it from the procedure.</p>
  
  <p>I never got to the place where I could try</p>
  
  <pre><code>SELECT * FROM TABLE(GET_BACKPLANE( ... etc )
  </code></pre>
  
  <p>Sorry for any typos and bad Oracle Grammar... it's been a long several days.</p>