Is there way to display the output of a Ref cursor (Oracle Package), in the results pane of the SQL editor?
IF I right click on the Package body & then click “Execute”, it generates code & executes, but there is no result output.
For some reason the SQLNV stubs in comment code which says “Unknown cursor return type,If required, edit code to display cursor output”
And the Output window (DBMS_OUTPUT) says execution was succesful, but the output is Not displayed in this window either.
I do Not want to do FETCH/LOOP cursor routine & display this Ref cursor output which has so many columns.
I want a easier & quicker way to displays the results in the grid.
SQL Nav Ver: 4.4.1.501
Code sample:
DECLARE
TYPE CURSOR_TYPE_1 IS REF CURSOR;
MyRefcursor CURSOR_TYPE_1;
BEGIN
– Now call the stored program
MyApp_ora_package.MyApp_Rpt_Proc(MyRefcursor);
– Output the results
– Unknown cursor return type
– If required, edit code to display cursor output
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SubStr('Error ‘||TO_CHAR(SQLCODE)||’: '||SQLERRM, 1, 255));
RAISE;
END;
Hi Rajeev,
can you please refer to the attachments...
Is the sort of functionality you are after?
If not, please send us some screen shots.
regards
sekhar
Hi Rajeev,
can you please refer to the attachments...
Is the sort of functionality you are after?
If not, please send us some screen shots.
regards
sekhar
Yes, Your screenshots are pointing to the problem in the right direction.
If you refer to your screenshot, the Refcursor of ‘get_products_ref_cursor’,
The ‘Results’ tab, should have displayed the output of ‘products_ref_cursor’ , which is a result of "Select product_id, name, price from products’, in the grid pane below.
But , it just says “return value (cursor)”
Where is the output of the cursor in the grid?
What use is it anyways just to show a value called “(cursor)” ?
HI Rajeev,
I forgot to say that you have to click on the browse button on the extreme right hand side of the execution console where it says the "Return Value".Then you can view all the results.Please have a look on the attachments.
regards
sekhar
HI Rajeev,
I forgot to say that you have to click on the browse button on the extreme right hand side of the execution console where it says the "Return Value".Then you can view all the results.Please have a look on the attachments.
regards
sekhar
As I mentioned I have SQL Nav 4.4.1.501.
I dont see this browse button functionality
Can this be done anyway, using this version?
Attached screeshot of the SQLNav version I use.
Hi Rajeev,
We have the browse button in sqlnav 4.4 as well, but from the Execution console.
I agree it can not be done from sql editor.
Here I am giving the steps the way you can see the results...
-
Open the package or procedure in Stored program editor. Click on the 'Run' button.
-
It opens the Execution console. Now in execution console click on 'Run' (Green button ) again.
-
Then it opens the entry point for the cursor. Then Results tab in execution console will be highlighted.
-
Now click on the right extreme browse button then you can see the results in the data grid as a separate window.
-
But it can not be done from SQL editor, being a reference cursor. Until we execute it, we don't know the values of the record. But you can view the record values as mentioned in step4.
regards
sekhar
Thanks for your replies.
Finally, got it to work the way I wanted.
Initially, could not replicate your steps from step#3 onwards.
Both of us have different screen/settings , despite the same version.
The important point here is to UNCHECK the settings "Direct values of OUT parameters to DBMS_OUTPUT". This will give the ellipses button "...the right extreme browse button" as you mentioned, & thereby, can view the Ref Cursor output.
Hope, the attached screenshots better explains, what I did.
Issue resolved.
Thanks a lot!
