Pipelined table functions can be used inside a package?

From: <lio.spam_at_libero.it>
Date: 12 Mar 2004 07:30:29 -0800
Message-ID: <44143ea1.0403120730.bd7cdd1@posting.google.com>

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 - 09:30:29 CST