Re: Pipelined table functions can be used inside a package?

lio.spam_at_libero.it (lio.spam_at_libero.it) wrote in message news:<44143ea1.0403120730.bd7cdd1_at_posting.google.com>...

Search for this on asktom...I didn't totally understand his explanation, but I think I remember that you have to create the TYPES outside of the package definition. So perhaps that is your issue.

Dave

> Hi, I tried to use pipelined table functions inside a test package
> (the package code is at the end of the messagge).
>
> The package is created without errors and calling the table functions
> from a SQL statement like:
> SELECT * FROM TABLE(test_pkg.get_my_data2(1,'Numero: '))
> everything works fine.
>
> But if I try to recompile the package with:
> ALTER PACKAGE test_pkg COMPILE PACKAGE
> the session hangs up and I need to kill the session.
>
> Why this happens? There are some mistakes in the code?
>
> P.S. I noticed that the documentation of Oracle 9.2 describes the
> PIPELINED Clause at page 13-54 of the 'SQL Reference' for standalone
> funcions but not for functions inside packages ('PL/SQL User's Guide
> and Reference' page 13-93).
> But if pipelined table functions are not supported inside packages why
> my test_pkg works fine but ALTER PACKAGE not?
>
> Thanks.
>
> Here is the package:
>
> CREATE OR REPLACE PACKAGE test_pkg IS
>
> TYPE my_data_r_t IS RECORD
> (
> field1 NUMBER
> , field2 VARCHAR2(50)
> );
> TYPE my_data_t_t IS TABLE OF my_data_r_t;
>
> -- TEST with: select * from table(Test_Pkg.get_my_data1(1,'Numero:
> '))
> FUNCTION get_my_data1
> ( val NUMBER
> , descr VARCHAR2
> ) RETURN my_data_t_t
> PIPELINED
> ;
>
> -- TEST with: select * from table(Test_Pkg.get_my_data2(1,'Numero:
> '))
> FUNCTION get_my_data2
> ( val NUMBER
> , descr VARCHAR2
> ) RETURN my_data_t_t
> PIPELINED
> ;
>
> END test_pkg;
> /
>
> CREATE OR REPLACE PACKAGE BODY test_pkg AS
>
>
> -- TEST with: select * from table(Test_Pkg.get_my_data1(1,'Numero:
> '))
> FUNCTION get_my_data1
> ( val NUMBER
> , descr VARCHAR2
> ) RETURN my_data_t_t
> PIPELINED
> IS
> CURSOR get_my_data_cr
> ( val IN NUMBER
> , descr IN VARCHAR2
> )
> IS
> SELECT y.v
> , descr || y.v
> FROM ( SELECT val v FROM dual UNION ALL SELECT -val v FROM
> dual) y
> ;
>
> my_data_r get_my_data_cr%ROWTYPE;
>
> BEGIN
>
> -- Loop on all rows in the cursor
> FOR my_data_r IN get_my_data_cr(val, descr)
> LOOP
> pipe ROW(my_data_r);
> END LOOP;
>
> RETURN;
> END;
>
> -- TEST with: select * from table(Test_Pkg.get_my_data2(1,'Numero:
> '))
> FUNCTION get_my_data2
> ( val NUMBER
> , descr VARCHAR2
> ) RETURN my_data_t_t
> PIPELINED
> IS
> CURSOR get_my_data_cr
> ( val IN NUMBER
> , descr IN VARCHAR2
> )
> IS
> SELECT z.field1
> , z.field2
> FROM TABLE( test_pkg.get_my_data1(val,descr) ) z
> ;
>
> my_data_r get_my_data_cr%ROWTYPE;
>
> BEGIN
>
> -- Loop on all rows in the cursor
> FOR my_data_r IN get_my_data_cr(val, descr)
> LOOP
> pipe ROW(my_data_r);
> END LOOP;
>
> RETURN;
> END;
>
> END test_pkg;
> /
Received on Fri Mar 12 2004 - 12:38:29 CST