So you want to use pipelined table functions and cursor_sharing in 10.1.0. Check this out…
First create this test package:
CREATE OR REPLACE PACKAGE ptf_test AS TYPE t_ptf_row IS RECORD ( dummy dual.dummy%TYPE ); TYPE t_ptf_tab IS TABLE OF t_ptf_row; FUNCTION get_data_1 RETURN t_ptf_tab PIPELINED; FUNCTION get_data_2 (p_in IN VARCHAR2) RETURN t_ptf_tab PIPELINED; END; / SHOW ERRORS CREATE OR REPLACE PACKAGE BODY ptf_test AS FUNCTION get_data_1 RETURN t_ptf_tab PIPELINED AS l_row t_ptf_row; BEGIN SELECT dummy INTO l_row FROM dual; PIPE ROW(l_row); RETURN; END; FUNCTION get_data_2 (p_in IN VARCHAR2) RETURN t_ptf_tab PIPELINED AS l_row t_ptf_row; BEGIN SELECT dummy INTO l_row FROM dual; PIPE ROW(l_row); RETURN; END; END; / SHOW ERRORS
Then turn on cursor_sharing and query the pipelined table functions:
SQL> ALTER SESSION SET CURSOR_SHARING=SIMILAR; Session altered. SQL> SELECT * 2 FROM TABLE(ptf_test.get_data_1); D - X 1 row selected. SQL> SELECT * 2 FROM TABLE(ptf_test.get_data_2('x')); FROM TABLE(ptf_test.get_data_2('x')) * ERROR at line 2: ORA-22905: cannot access rows from a non-nested table item SQL>
Now turn it off and try again:
SQL> ALTER SESSION SET CURSOR_SHARING=EXACT; Session altered. sys@db10g> SELECT * 2 FROM TABLE(ptf_test.get_data_1); D - X 1 row selected. SQL> SELECT * 2 FROM TABLE(ptf_test.get_data_2('x')); D - X 1 row selected. SQL>
So you can’t use cursor_sharing and pipelined table functions with parameters together. It took me a while to figure out the problem, but when I did it wasn’t long before support came back with an unfixed bug relating to it (Bug 4178323).
The solution? Don’t use cursor_sharing 🙂
Cheers
Tim…
Note. Script changed to use ALTER SESSION to prevent anyone leaving their test instance in an dodgy state. Thanks for the heads-up Gary 😉