Home > Database, Tech > Displaying Oracle ref cursor results using Toad

Displaying Oracle ref cursor results using Toad

Oftentimes it is the case that the calling middleware of your code will call an Oracle procedure using an in/out ref cursor variable that will contain the results when the procedure returns, and the results will then be subsequently loaded into a DataTable or equivalent data store upon return. If you have to debug the results of the ref cursor, however, that can be difficult to do unless you can evaluate the results directly against the database. Although there are many websites providing the solution of how to display ref cursor output in table format when you call an Oracle proc with an in/out ref cursor in Toad, they can be a bit difficult to find, depending on the search terms used. So I wanted to add this post to the list, in case it could help.

Displaying the results directly in Toad is very possible (and very useful!), since Toad uses bind variables. In order to get your output display to show the tabular results of a call to a ref variable when calling a proc, simply put a colon (:) in front of any made-up name when you actually execute your procedure. For those inexperienced with Toad, the easiest method in my opinion is to right-click on the procedure you want to execute (in the schema browser window) and select, ‘Execute procedure’. A window will come up with fields to fill in in the top (you may have to scroll down in the pop up window if the procedure you selected is part of a package), and a script on the bottom. Copy the script part to your clipboard and close the window. Open a new script (SQL Editor) window in Toad and paste what you copied before – it will look something like this:

DECLARE
 SOME_VAR_1 NUMBER;
 SOME_VAR_2 VARCHAR2(4000);
 IO_CURSOR SYS_REFCURSOR;
BEGIN
 SOME_VAR_1 := NULL;
 SOME_VAR_2 := NULL;
 -- IO_CURSOR := NULL;  Modify the code to initialize this parameter

   my_proc (SOME_VAR_1, SOME_VAR_2, IO_CURSOR);

COMMIT;
END;
/

Put in the variables you want, and change the ‘IO_CURSOR’ variable to any name beginning with a colon, like so:

DECLARE
 SOME_VAR_1 NUMBER;
 SOME_VAR_2 VARCHAR2(4000);
 IO_CURSOR SYS_REFCURSOR;
BEGIN
 SOME_VAR_1 := 1;
 SOME_VAR_2 := 'foo';
 -- IO_CURSOR := NULL;  Modify the code to initialize this parameter

   my_proc (SOME_VAR_1, SOME_VAR_2, :ref_cur);

COMMIT;
END;
/

-substituting ‘:ref_cur‘ (or whatever – the name doesn’t really matter as long as it is preceded by a colon) for the default ref cursor name, and filling in the rest of the parameters that you want to test. Then, run it as a program, not as a script. Toad will ask you to tell it what the :ref_cur variable is – select ‘cursor’ as the variable type in the dropdown list, and the output results should be displayed in tabular format in the Data Grid tab, the same as if you had run a simple SELECT statement.

The reason that I prefer to copy the procedure script to a separate script window is that then it is exceedingly easy to re-run the same test script if you find something you need to change in the procedure code – instead of having to find the procedure again in the schema browser window (after adjusting the procedure), and then selecting Run, all you have to do is hit the green Run button at the top of the same script as before.

One caveat: Apparently, Toad version 10.1 has a bug which prevents the option of ‘cursor’ from being displayed in the dropdown list in the variables window (I just ran into this issue earlier today!). You will need to upgrade to 10.5 or above, or revert to 9.whatever.

Happy ref cursor coding!

Heather Koyuk Database, Tech

  1. Manoj
    September 18th, 2012 at 04:43 | #1

    Excellent!!

  2. Lien Gilhooley
    September 19th, 2012 at 05:59 | #2

    Hi Heather,

    Thanks for posting this article. It describes what I’m trying to do in Toad exactly. I’m new to Toad so I don’t know what you mean by “run it as a program, not as a script”. How do you accomplish that?

    Thanks.

  3. Lien Gilhooley
    September 19th, 2012 at 06:08 | #3

    Heather – figured it out! Fantastic tip!

  4. September 19th, 2012 at 06:57 | #4

    Glad you figured it out Lien!

  5. Aaron
    October 5th, 2012 at 01:13 | #5

    This is highly useful and we can see the results in data grid rather than in disordered state.

    Many Thanks

  6. October 8th, 2012 at 16:02 | #6

    Thanks Aaron, glad it helped!

  7. Manoj
    October 15th, 2012 at 03:20 | #7

    Now the problem is I have two ref cursor as output. Following the above procedure the data grid displays only one cursor. Please let me know how to print multiple ref cursors in the data grid.

  8. Vishal
    November 4th, 2012 at 01:52 | #8

    Hi Manoj,

    Found a workaround to show the result ..

    declare
    type xx is REF CURSOR;
    a xx;
    b xx;
    begin
    proc_v_test(:a , b);
    end;

    Here “a” and “b” both are the ref cursor. run the block for first time to show the result of first refcursor “a” and after that just pass the parameter proc_v_test( a , :b) to check the another ref cursor result in data grid.

  9. LJConnolly
    November 26th, 2012 at 08:12 | #9

    Thank you so much for this info. I am new to Oracle and pl/sql programming and I have been working for a few days trying to figure out why I was not getting any errors but, also not getting any results to display. I had even asked a couple of the more experienced guys in my group and they were no help.
    Linda

  10. Sarika
    February 5th, 2013 at 07:01 | #10

    Life Saver ! Thanks a lot !!

  11. Saha
    February 27th, 2013 at 06:00 | #11

    Thanks Manoj for asking good question. This one of my colleagues has faced. He has 3 ref cursors output and running below query would show only the first value.
    proc_v_test(:a , :b);

    Thanks Vishal for the solution. I would try it out.

    Question: There is some output called “REF CURSOR Output”. what is this and where is it used?

    Thanks!

  12. KodaliP
    March 30th, 2013 at 01:27 | #12

    DECLARE
    P_EMP_ID NUMBER;
    P_EMP SYS_REFCURSOR;
    V_EMP EMP%ROWTYPE;

    BEGIN

    SCOTT.TEST_OUT ( P_EMP_ID=>7782, P_EMP =>P_EMP);

    IF (P_EMP IS NOT NULL)
    THEN
    LOOP
    FETCH P_EMP INTO V_EMP;
    EXIT WHEN P_EMP%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(V_EMP.ENAME);
    END LOOP;
    CLOSE P_EMP;
    ELSE
    DBMS_OUTPUT.PUT_LINE(’OUT SIDE’);
    END IF;

    COMMIT;
    END;

  13. coder
    August 15th, 2013 at 11:02 | #13

    Excellent tip! Thanks!

  14. VPINNDAARREDDY
    March 18th, 2014 at 20:23 | #14

    THANKS…. ONE HOLE DAY I SPEND TO FOR THIS LOGIC. USING THIS ITS SIMPLE.. THANK U ….

  1. No trackbacks yet.