How to display Ref cursor output in Result/Grid Pane? ( SQL Nav 4.4.1.501)

How to display Ref cursor output in Result/Grid Pane? ( SQL Nav 4.4.1.501)

Follow / 14 Mar 2007 at 7:09pm

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;

Replies

  • Follow / 15 Mar 2007 at 2:38am
    snap1.jpg
    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
  • Follow / 15 Mar 2007 at 2:38am
    snap3.jpg
    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
  • Follow / 16 Mar 2007 at 12:29am

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

  • Follow / 16 Mar 2007 at 12:49am
    snap2.jpg
    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
  • Follow / 16 Mar 2007 at 12:49am
    snap1.jpg
    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
  • Follow / 16 Mar 2007 at 1:03am
    sql-nav4.4.1_no output from ref cursor.jpg

    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.

  • Follow / 16 Mar 2007 at 1:53am
    snap1.jpg
    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...

    1) Open the package or procedure in Stored program editor. Click on the 'Run' button.

    2) It opens the Execution console. Now in execution console click on 'Run' (Green  button ) again.

    3) Then it opens the entry point for the cursor. Then Results tab in execution console will be highlighted.

    4) Now click on the right extreme browse button then you can see the results in the data grid as a separate window.

    5) 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
  • Follow / 16 Mar 2007 at 6:51pm
    sql-nav4.4.1_correct settings to get ref cursor output .jpg

    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!