Pipelined table functions and cursor_sharing don’t mix…

 

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 😉

Author: Tim...

DBA, Developer, Author, Trainer.

5 thoughts on “Pipelined table functions and cursor_sharing don’t mix…”

  1. If you use object types (i don’t like the plsql types – they just create “fake” object types that make people go “what is this type in my dictionary”), you can solve this with CAST as well:

    ops$tkyte@ORA10G> create or replace type myscalarType as object ( dummy varchar2(1) )
    2 /

    Type created.

    ops$tkyte@ORA10G> create or replace type myArrayType as table of myScalarType
    2 /

    Type created.

    ops$tkyte@ORA10G>
    ops$tkyte@ORA10G>
    ops$tkyte@ORA10G> CREATE OR REPLACE PACKAGE ptf_test AS
    2 FUNCTION get_data_1
    3 RETURN myArrayType PIPELINED;
    4
    5 FUNCTION get_data_2 (p_in IN VARCHAR2)
    6 RETURN myArrayType PIPELINED;
    7
    8 END;
    9 /

    Package created.

    ops$tkyte@ORA10G> SHOW ERRORS
    No errors.
    ops$tkyte@ORA10G>
    ops$tkyte@ORA10G> CREATE OR REPLACE PACKAGE BODY ptf_test AS
    2 FUNCTION get_data_1
    3 RETURN myArrayType PIPELINED
    4 AS
    5 l_row dual%rowtype;
    6 BEGIN
    7 SELECT dummy
    8 INTO l_row
    9 FROM dual;
    10
    11 PIPE ROW( myScalarType( l_row.dummy ) );
    12 RETURN;
    13 END;
    14
    15 FUNCTION get_data_2 (p_in IN VARCHAR2)
    16 RETURN myArrayType PIPELINED
    17 AS
    18 l_row dual%rowtype;
    19 BEGIN
    20 SELECT dummy
    21 INTO l_row
    22 FROM dual;
    23
    24 PIPE ROW( myScalarType( l_row.dummy ) );
    25 RETURN;
    26 END;
    27 END;
    28 /

    Package body created.

    ops$tkyte@ORA10G> SHOW ERRORS
    No errors.
    ops$tkyte@ORA10G>
    ops$tkyte@ORA10G>
    ops$tkyte@ORA10G> ALTER SYSTEM SET CURSOR_SHARING=SIMILAR;

    System altered.

    ops$tkyte@ORA10G>
    ops$tkyte@ORA10G> SELECT * FROM TABLE(ptf_test.get_data_1);

    D
    –
    X

    ops$tkyte@ORA10G> SELECT * FROM TABLE(ptf_test.get_data_2(‘x’));
    SELECT * FROM TABLE(ptf_test.get_data_2(‘x’))
    *
    ERROR at line 1:
    ORA-22905: cannot access rows from a non-nested table item

    ops$tkyte@ORA10G> SELECT * FROM TABLE(cast( ptf_test.get_data_2(‘x’) as myArrayType ) );

    D
    –
    X

  2. I quite like the PL/SQL way of doing it. It’s nice to have the type definition in with the code.

    I agree, the naming convention sucks though.

    Cheers

    Tim…

  3. Error is in 9iR2 as well.
    But your test script would have been a bit safer with
    ALTER Session SET CURSOR_SHARING=SIMILAR;
    rather than
    ALTER SYSTEM SET CURSOR_SHARING=SIMILAR;

  4. Gary — am I glad I came back to read any followups 🙂

    I would have been banging my head soon trying to figure out “what the heck is up” on my test box 😉

    Doh – alter system, gotta read those scripts a little closer.

    Now you know why I never test anything in asktom (the database).

Comments are closed.