Pipelined table functions can be used inside a package?
227530Mar 12 2004 — edited Mar 15 2004Hi, I tried to use pipelined table functions inside a test package (the package code is at the end of the messagge).
The package was 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;
/