Toad World® Forums

Ref Cursor Question


#1

Hi, I am struggling with a test case. I have a procedure which if provided with a transfer id returns the details of the transfer in a ref cursor.
I defined the cursor as follows within the “Changed by program” properties as a query.
SELECT schedule_id, schedule_end_date, schedule_frequency_name,
schedule_frequency_parameter1, schedule_frequency_parameter2,
from_account_number, from_company_number, from_hogan_product_cd,
to_account_number, to_company_number, to_hogan_product_cd,
transfer_date, transfer_reference_date, transfer_status_name,
create_date, create_by_ecn, create_channel_name, create_by_banker,
modify_channel_date, modify_by_ecn, modify_channel_name,
modify_by_banker
FROM DUAL

I changed the operator to “#=”.
I changed the expected results to “Integer” and under “Single Value or Expression” I entered 1.
When I try to run, I get a compile error which reads “The properties of Changed by Program or Expected Results (whichever is a cursor variable) must specify a valid SELECT statement, which Quest Code Tester will use to determine the list of elements in the SELECT list of the cursor variable. You cannot use an expression for this purpose.”

What am I doing wrong?


#2

The last time I looked, the dual table had one column: DUMMY. So this query:

SELECT schedule_id, schedule_end_date, schedule_frequency_name,
schedule_frequency_parameter1, schedule_frequency_parameter2,
from_account_number, from_company_number, from_hogan_product_cd,
to_account_number, to_company_number, to_hogan_product_cd,
transfer_date, transfer_reference_date, transfer_status_name,
create_date, create_by_ecn, create_channel_name, create_by_banker,
modify_channel_date, modify_by_ecn, modify_channel_name,
modify_by_banker
FROM DUAL

is NOT valid. That makes sense doesn’t it? I assume that your CV has all of these columns.

Can you tell how you are defining your outcome? Are you comparing the output of your CV to a query? If so, then just provide the query that contains the expected values and you do not have to provide a “dummy” query in the Changed by Program CV properties; we will use the structure of the expected query.

I know this can get tricky. Here’s the way to think about it:

For us to test the contents of a CV, we have to know the select list: how many, what types? If you compare CV to CV, then for one at least of those CVs, you must tell us the structure of the select list for the CV, either by specifying a table or query.

If you compare a CV to a table or query, we will then use that information automatically (assume that it applies to the CV).

Does this help?

SF


#3

I wrote dual column as in the help it was specified:

“Provide the name of a table or enter a query that will provide the correct number and type of columns. This doesn’t have to be a “real” query; it could be a select from dual that “presents” the right information, as in:
SELECT sysdatehire_date, 1 salary, 1 commission FROM dual”

Anyways, I was able to resolve the issue. The mistake I was commiting was in the query I was specifying the column name from the ref cursor instead of the column names from the table. I changed that and I changed the from to the actual tables, and it worked.

Thanks Steve!