Re: PIPELINE FUNCTION varchar parameters no rows returned

From: Anurag Varma <avoracle_at_gmail.com>
Date: 22 Mar 2006 04:36:33 -0800
Message-ID: <1143030993.237427.203480@t31g2000cwb.googlegroups.com>

Well the real solution is not what you describe.

Just to clarify your problem. Your code (after correcting typos) will fail when cursor_sharing is set to "SIMILAR" or "FORCE".

The following example should show the problem and the solution:

ORA92> show parameter cursor_sharing

NAME                                 TYPE

VALUE

------------------------------------ --------------------------------
------------------------------
cursor_sharing                       string

SIMILAR ORA92> select * from table(emp_name('ZZZ')); select * from table(emp_name('ZZZ'))

                *

ERROR at line 1:
ORA-22905: cannot access rows from a non-nested table item

ORA92> select * from table( cast(emp_name('ZZZ')as my_emp_rec_table));

        ID FNAME
---------- --------------------

       170 ZZZ ORA92> alter session set cursor_sharing= exact;

Session altered.

ORA92> select * from table(emp_name('ZZZ'));

        ID FNAME
---------- --------------------

       170 ZZZ For others testing this item: If you test the above by first parsing/executing it using cursor_sharing=exact, the problem will not appear when you change it to similar/force because the parsed version is taken. Here is what I'm talking about:

ORA92> alter session set cursor_sharing=exact;

Session altered.

ORA92> select * from table(emp_name('ZZZ'));

        ID FNAME
---------- --------------------

       170 ZZZ ORA92> alter session set cursor_sharing=similar;

Session altered.

ORA92> select * from table(emp_name('ZZZ'));

        ID FNAME
---------- --------------------

       170 ZZZ ORA92> -- now add a space in select to have it parsed again ORA92> select * from table(emp_name('ZZZ')); select * from table(emp_name('ZZZ'))

                 *

ERROR at line 1:
ORA-22905: cannot access rows from a non-nested table item

ORA92> select * from table(cast(emp_name('ZZZ') as my_emp_rec_table));

        ID FNAME
---------- --------------------

       170 ZZZ Anurag Received on Wed Mar 22 2006 - 06:36:33 CST