Test the contents of a cursor variable
Cursor variables are variables that point to underlying (Oracle SGA-based) result sets. You can pass a cursor variable from one PL/SQL program to another, or even pass it back to a non-PL/SQL host language, like Java, which can easily and simply fetch rows from the cursor variable. Quest Code Tester for Oracle makes it very easy to test the contents of cursor variables by generating all the code needed to transfer the data to collections which can then be compared to other datasets.

Flash tutorial explaining the topic: A brief video that shows you how to use Quest Code Tester to solve the problem posed by this topic.

Step-by-step instructions: Follow these steps to replicate what we show you in the tutorial.

Steps

From the Test Dashboard, ask to create a new test definition. Select qctod# rows_from_table from the list and press OK. Now you are inside Test Builder.

All rows and columns

  • Change test case name to "All rows and columns of employees".
  • Type "QCTOD#EMPLOYEES" into the table name argument.
  • Leave the column list empty.
  • In Outcome grid, set Operator to "= (equal to)".
  • Set Expected Results datatype to TABLE.
  • Press […] to open up the Properties and choose QCTOD#EMPLOYEES
  • Press OK to return to the grid.
  • Press Add Now and then shift your attention back up to the test case name.

In other words, that is all I have to do to have Quest Code Tester generate all necessary code to compare the contents of the cursor variable to the employees table!

Just last name and salary, all rows

  • Change test case name to "Just last name and salary, all rows".
  • Leave "QCTOD#EMPLOYEES" in the table name argument.
  • Change the column list value to "last_name, salary".
  • In Outcome grid, set Expected Results datatype to QUERY.
  • Press […] to open up the Properties and type this query into the box provided:

select last_name,salary from qctod#employees

  • Press OK to return to the grid.
  • Press Add Now and then shift your attention back up to the test case name.

In other words, that is all I have to do to have Quest Code Tester generate all necessary code to compare the contents of the cursor variable to a query (subset of a table)!

Just last name and salary for all employees in department 10

  • Change test case name to "Just last name and salary, for all employees in department 10".
  • Change table input value to "QCTOD#EMPLOYEES WHERE DEPARTMENT_ID = 10" in the table name argument.
  • Change the column list value to "last_name, salary".
  • In Outcome grid, set Expected Results datatype to QUERY.
  • Press […] to open up the Properties and type this query into the box provided:

select last_name,salary from qctod#employees where department_id = 10

  • Press OK to return to the grid.
  • Press Add Now and then shift your attention back up to the test case name.

Time to run your test!

On the bottom right of Test Builder, select Save, Close and Run.

Quest Code Tester for Oracle will then save your definition to the repository, and run your test. You should see the Results Viewer and a green flag, indicating a successful run.

Source code of program tested: Compile this code in your schema so that you can build your test definition for it. 

CREATE OR REPLACE FUNCTION qctod#rows_from_table (
   table_in IN VARCHAR2
 , column_list_in IN VARCHAR2 DEFAULT NULL
)
   RETURN sys_refcursor
IS
   CV sys_refcursor;
BEGIN
   OPEN CV FOR 'select ' || NVL (column_list_in, '*') || ' from ' || table_in;

   RETURN CV;
END qctod#rows_from_table;
/