Quick-n-dirty results: View results of Procedure OUT cursor in SQL Worksheet?

Question

Platform: Oracle
Language: PL/SQL
Issue: Want to output a procedure OUT cursor into the SQLDeveloper SQLWosksheet.

Anyone know how to use the Oracle "Select * from Table( PipelinedFunction( Param ) ) " to check procedure code output cursors?

I am using Crsytal Reports off of an Oracle stored procedure. Crystal requires that a procedure return a cursor, which it fetchs and reads.

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").

Is there a (simple) way to use a Function to read the cursor from the procedure? (and pipe that out to the Table function?)

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.

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?)

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.

I think I can handle SQL pretty well (for our purposes), but I am not an Oracle-specific developer... but I need help.

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.

Update:
I have tried some hare-brained schemes based on slivers that I've seen on the web... such as

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.

and

Create or replace Function GET_BACKPLANE ( 
    NODE VARCHAR2 )  RETURN  My_Table_Stru%ROWTYPE  PIPELINED
AS

BEGIN ...

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.

I never got to the place where I could try

SELECT * FROM TABLE(GET_BACKPLANE( ... etc )

Sorry for any typos and bad Oracle Grammar... it's been a long several days.

Answer 1

SQL Developer allows us to use SQL*Plus commands in the Worksheet. So all you need to do is define a variable to hold the output of the ref cursor.

I may have misinterpreted the actual code you want to run but I'm assuming your actual program is a procedure Backplane(Results, Node) where results is an OUT parameter of datatype sys_refcursor and node is some input parameter.

var rc refcursor

exec  Backplane(results=>:rc, Node=>42) 

print rc

The output of the print statement is written to the Script Output pane.

Note that the use of SQL*Plus commands means we have to use the Run Script option F5 rather than execute statement.

Answer 2

Thanks for the help. In the end, I wound up brute-force-ing it... Step by step: Make a query, test a query, create a global temp table from the structure, add code to make another query off of that GTT, test the query, create a global temp table from the structure, etc.

In the end, I wound up running (anonymous block) scripts and checking the GTT contents at every stage.

The last part was to use the same last query from the original procedure, stuffing everything into the Cursor that crystal likes...

tomorrow, I test that.

But, I'll just force it through for the next procedure, and get it done in a day and a half instead of 2+ weeks (embarrassed).

Thanks,

Marc