Toad World® Forums

nested sys_refcursors


#1

Does anyone have any experience testing functions that return a nested sys_refcursor with Quest Code Tester? This structure is common in the company where I work. Manually coding a test for it in utPLSQL is labor intensive. I wonder if there is an easier, less labor-intensive way of writing a test for this kind of function with Code Tester. Here is an example of such a function:
FUNCTION AIP_GET_BOOK_AND_BOOK_LISTS
RETURN SYS_REFCURSOR
IS
V_CRSR SYS_REFCURSOR;
BEGIN
OPEN V_CRSR FOR
SELECT BK.BOOK_NM, BK.SORT_ORDER_NUM, BK.BOOK_ARCHIVE_URL
,CURSOR
(SELECT RL.BOOK_LIST_ID, RLI.IBSN_ID, RL.BOOK_LIST_NM
,RLI.SORT_ORDER_NUM
,COUNT (RRI.BOOK_LIST_ID) NUMBER_OF_BOOKS
,CURSOR
(SELECT BLC.CONTENT_TYPE_CD
,BLC.BOOK_LIST_CONTENT_CLOB BOOK_LIST_CONTENT_TXT
FROM BOOK_LIST_CONTENT BLC
WHERE BLC.BOOK_LIST_ID = RL.BOOK_LIST_ID
) BOOK_LIST_CONTENTS
FROM RECOMMENDED_BOOK_LISTS RL
,RECOMMENDED_LIBRARY_IBSNS RLI
,RECOMMENDED_READER_IDENTIFIERS RRI
WHERE RL.BOOK_NM = BK.BOOK_NM
AND RL.BOOK_LIST_ID = RLI.BOOK_LIST_ID
AND RL.BOOK_LIST_ID = RRI.BOOK_LIST_ID
AND RL.BOOK_NM = RLI.BOOK_NM
AND RLI.ACTIVE_IND = ‘Y’
GROUP BY RL.BOOK_LIST_ID
,RLI.IBSN_ID
,RL.BOOK_LIST_NM
,RLI.SORT_ORDER_NUM
) RECOMENDED_BOOK_LISTS
FROM READER_RECOMMENDATION_BOOKS BK
ORDER BY BK.SORT_ORDER_NUM ASC;

RETURN V_CRSR;
END AIP_GET_BOOK_AND_BOOK_LISTS;


#2

We do handle this much more elegantly than utPLSQL.

You can simply provide the query or table/view against which you want to compare the rows and columns of the CV and we do the rest of the work for you.

Have you checked out the video example for this?

SF


#3

I’m creating a test for a procedure that returns as output a refcursor that includes a nested cursor. Just verifying that refcursor returns rows.

My outcome is something like:

SELECT ‘a’ column1,
0 column2,
CURSOR (SELECT ‘a’ column3, ‘a’ column4
FROM DUAL
)
FROM DUAL

I’m getting invalid package compiled.

looking at the line error I found:

BEGIN
/* Ref cursor is empty setup */
[tf_get_data_name_nopkg] (‘f_P_RLOCATIONS1’, f_P_RLOCATIONS1, [
nt_from_fp_cv_nopkg], [fp_count_nopkg] );

I’m using 1.8.2.374 with Oracle 10g.

Any idea?

Thanks in advance.


#4

This is from test builder. Under errors and warnings I’m getting:

ORA-01001: invalid cursor
ORA-06512: at “SYS.DBMS_SYS_SQL”, line 902
ORA-06512: at “SYS.DBMS_SQL”, line 19
ORA-06512: at “UNIT.QU_COLUMN_SET_XP”, line 635
ORA-06502: PL/SQL: numeric or value error: Null index table key value

ORA-06512: at “SYS.DBMS_SYS_SQL”, line 902
ORA-06512: at “SYS.DBMS_SQL”, line 19
ORA-06512: at “UNIT.QU_COLUMN_SET_XP”, line 635
ORA-06512: at “UNIT.QU_COLUMN_SET_XP”, line 694

this is on Oracle 10.2.0.4.

Could someone with any experience doing unit test with nested cursors help?


#5

Sorry about the delay in answering your question.

In the Code Tester release notes, you will see this:

Testing contents of tables with non-scalar columns

Quest Code Tester does not perform dataset comparison checks for tables with CLOB columns and other complex datatypes. Instead, Code Tester will automatically ignore any columns of these types that cannot be used in the comparisons.

Now, it should say “Testing contents of tables and queries with non-scalar columns” and I will ask my doc person to adjust that for 1.8.4.

But what it does mean is that we do not support testing of queries that contain within them a sub-cursor.

If you simply want to know if the query returns any rows, I suggest you build a view on top of this query, hide the complex type, and build the outcome against that.

Sorry about that!
SF


#6

I would like to correct myself: we should not be raising an exception with a subquery like this. We should be ignoring it, as we do collections and object types. I will log a bug and see if we can get it fixed for 1.8.4. If you would like Quest Support to open a ticket for this so that you will be notified when it is fixed, please send me a note at steven.feuerstein@quest.com.

Regards, SF


#7

Sadly, after doing some research, I must retract my previous post. The approach we take with queries that contain non-comparable expressions, like a collection type (that is, a type that Oracle SQL will not support comparison analysis via UNION, MINUS, etc.), is to “filter out” that column by putting it inside an outer query, as in:

SELECT “COLUMN1”, “COLUMN2”
FROM (SELECT ‘a’ column1
, 0 column2
, cursor (SELECT ‘a’ column3, ‘a’ column4
FROM DUAL)
FROM DUAL)

Unfortunately, cursor expressions are treated differently in SQL, and an attempt to execute this query yields the error:

ORA-22902: CURSOR expression not allowed

Now, with more time and resources, we could revamp our technology to parse out the query and completely REMOVE the cursor expression from the query, but currently we are not able to do that.

You also cannot build a view on top of a query with a cursor expression.

So I am afraid this has to be logged as a new Known Issue:

You cannot test queries that contain cursor expressions.