Toad World® Forums

compare stored procedure ref cursor output?


#1

The reports for our application are driven by stored procedures that return ref cursors. In order to test changes to these stored procedures, I’d like to create a new version of the stored procedure (with the same input and output parameters, but a different name) and then compare the ref cursor output from the original stored procedure with the ref cursor output from the new stored procedure. Admittedly, this isn’t really a repeatable unit test, but it would help with regression testing. I can think of various ways that I could write wrapper code that I could then call from Quest Code Tester (e.g., write a PL/SQL table function for each SP that calls the SP, then write a query to get the results of that table function), but is there any way to avoid that step, i.e., can Quest Code Tester directly compare the ref cursor outputs of two stored procedures?

I am new to Quest Code Tester, so it’s possible that I may have missed something obvious…


#2

Here’s what I would do:

  1. Create an outcome for the OUT argument of the procedure you are testing. This is a ref cursor.

  2. Choose “=” as the test type.

  3. For the expected result type, choose Ref Cursor.

  4. Then open the properties window for the value of this cursor. In the initialization section of that window, write a block of code that looks like this:

begin
my_other_proc (ref_cursor_arg => e_REF_CURSOR);
end;

Hopefully you can parse this: substitute the name of YOUR second procedure and arguments, but the OUT ref cursor should be assigned to the name of the variable shown at the top of the form. Should be e_REF_CURSOR.

You will also need to provide a query or table that matches the row returned by the ref cursor: same number and type of columns.

Then we will do the rest for you.

Let me know if any of this is too cryptic. I wish I could paste screenshots here!

SF


#3

Thanks – it works great! The piece I was missing was the code in the Initialization section. I just didn’t quite grasp how to connect the second SP to the outcome, although it seems obvious now.

By the way, I also found valuable your previous post related to the -6502 error. I’m running a 1.6 version of Code Tester, got that error, and used your work-around.