Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Pipelined table functions can be used inside a package?

227530Mar 12 2004 — edited Mar 15 2004
Hi, 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;
/
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 12 2004
Added on Mar 12 2004
6 comments
1,810 views