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!