Home » SQL & PL/SQL » SQL & PL/SQL » Calling pipelined function from pipelined function. (9.0.2.0.8)
| Calling pipelined function from pipelined function. [message #316314] |
Thu, 24 April 2008 10:41  |
ThomasG
Messages: 620
Registered: April 2005
Location: Heilbronn, Germany |
Senior Member |
|
|
Hi there,
I'm currently drawing a blank here and haven't found anything while searching.
I have an existing package with a pipelined function that takes a number of parameters. Now I want to create a second pipelined function that basically just calls the first with most of the parameters set to default values.
Consider this scaled down example :
CREATE OR REPLACE PACKAGE pipetest
AS
TYPE test_rec_type IS RECORD (
col_1 NUMBER(8),
col_2 VARCHAR2(10)
);
TYPE test_tab_type IS TABLE OF test_rec_type;
-- Already Existing Pipelined function.
FUNCTION list_one (dat_from IN DATE , date_to IN DATE,
nr_from IN NUMBER, nr_to IN number)
RETURN test_tab_type PIPELINED;
-- NEW Function
FUNCTION list_two (dat IN DATE ) -- NEW
RETURN test_tab_type PIPELINED; -- NEW
END;
/
CREATE OR REPLACE PACKAGE BODY pipetest AS
-- Already Existing Pipelined function.
FUNCTION list_one (dat_from IN DATE , date_to IN DATE,
nr_from IN NUMBER, nr_to IN number)
RETURN test_tab_type PIPELINED IS
out_rec test_rec_type;
BEGIN
-----------------------------------------------
-- Quite some stuff going on here in reality --
-----------------------------------------------
FOR i IN 1..5 LOOP
out_rec.col_1 := i;
out_rec.col_2 := 'YO';
pipe ROW (out_rec);
END LOOP;
RETURN;
END;
-- NEW Function
FUNCTION list_two (dat IN DATE)
RETURN test_tab_type PIPELINED IS
BEGIN
-- ??????
-- ?? CALL list_one(dat,dat,1,9999); here somehow.
-- ??????
RETURN;
END;
END;
/
I have tried a
return list_one(dat,dat,1,9999);
but of course that doesn't work but generates a "PLS-00633: RETURN statement in a pipelined function cannot contain an expression".
What would be the best way to add the list_two function to the package?
|
|
|
| Re: Calling pipelined function from pipelined function. [message #316323 is a reply to message #316314 ] |
Thu, 24 April 2008 11:14  |
ThomasG
Messages: 620
Registered: April 2005
Location: Heilbronn, Germany |
Senior Member |
|
|
Hmm.. I have it working now by using this in the second function :
-- NEW Function
FUNCTION list_two (dat IN DATE)
RETURN test_tab_type pipelined IS
out_rec test_rec_type;
BEGIN
FOR l_line IN ( SELECT *
FROM TABLE(
pipetest.list_one(
sysdate,sysdate,1,9999)
) LOOP
out_rec.col_1 := l_line.col_1;
out_rec.col_2 := l_line.col_2;
pipe row (out_rec);
END LOOP;
RETURN;
END;
But when I change the "sysdates" to "dat" I get a "ORA-22905: cannot access rows from a non-nested table item" during execution.
And I have a hunch there MUST be a better way to do this.
|
|
|
| Re: Calling pipelined function from pipelined function. [message #316338 is a reply to message #316323 ] |
Thu, 24 April 2008 12:18 |
 |
Barbara Boehmer
Messages: 3940
Registered: November 2002
Location: California, USA |
Senior Member |
|
|
Why not just use default values for list_one or use overloading? Perhaps there is something that I am not seeing due to the scaled down version. I have provided three options below. The first uses default values, the second using overloading and the third is what you asked for with the second function calling the first.
-- one function with default values:
SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE pipetest
2 AS
3 TYPE test_rec_type IS RECORD (
4 col_1 NUMBER(8),
5 col_2 VARCHAR2(10)
6 );
7 TYPE test_tab_type IS TABLE OF test_rec_type;
8 FUNCTION list_one (dat_from IN DATE , date_to IN DATE DEFAULT SYSDATE,
9 nr_from IN NUMBER DEFAULT 1, nr_to IN number DEFAULT 9999)
10 RETURN test_tab_type PIPELINED;
11 END;
12 /
Package created.
SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE BODY pipetest AS
2 FUNCTION list_one (dat_from IN DATE , date_to IN DATE DEFAULT SYSDATE,
3 nr_from IN NUMBER, nr_to IN number)
4 RETURN test_tab_type PIPELINED IS
5 out_rec test_rec_type;
6 BEGIN
7 FOR i IN 1..5 LOOP
8 out_rec.col_1 := i;
9 out_rec.col_2 := 'YO';
10 pipe ROW (out_rec);
11 END LOOP;
12 RETURN;
13 END;
14 END;
15 /
Package body created.
SCOTT@orcl_11g> SELECT * FROM TABLE (pipetest.list_one (SYSDATE, SYSDATE, 2, 5))
2 /
COL_1 COL_2
---------- ----------
1 YO
2 YO
3 YO
4 YO
5 YO
SCOTT@orcl_11g> SELECT * FROM TABLE (pipetest.list_one (SYSDATE))
2 /
COL_1 COL_2
---------- ----------
1 YO
2 YO
3 YO
4 YO
5 YO
-- one overloaded function:
SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE pipetest
2 AS
3 TYPE test_rec_type IS RECORD (
4 col_1 NUMBER(8),
5 col_2 VARCHAR2(10)
6 );
7 TYPE test_tab_type IS TABLE OF test_rec_type;
8 FUNCTION list_one (dat_from IN DATE , date_to IN DATE,
9 nr_from IN NUMBER, nr_to IN number)
10 RETURN test_tab_type PIPELINED;
11 FUNCTION list_one (dat_from IN DATE)
12 RETURN test_tab_type PIPELINED;
13 END;
14 /
Package created.
SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE BODY pipetest AS
2 FUNCTION list_one (dat_from IN DATE , date_to IN DATE,
3 nr_from IN NUMBER, nr_to IN number)
4 RETURN test_tab_type PIPELINED IS
5 out_rec test_rec_type;
6 BEGIN
7 FOR i IN 1..5 LOOP
8 out_rec.col_1 := i;
9 out_rec.col_2 := 'YO';
10 pipe ROW (out_rec);
11 END LOOP;
12 RETURN;
13 END;
14 FUNCTION list_one (dat_from IN DATE)
15 RETURN test_tab_type PIPELINED IS
16 out_rec test_rec_type;
17 BEGIN
18 FOR i IN 1..5 LOOP
19 out_rec.col_1 := i;
20 out_rec.col_2 := 'YO';
21 pipe ROW (out_rec);
22 END LOOP;
23 RETURN;
24 END;
25 END;
26 /
Package body created.
SCOTT@orcl_11g> SELECT * FROM TABLE (pipetest.list_one (SYSDATE, SYSDATE, 2, 5))
2 /
COL_1 COL_2
---------- ----------
1 YO
2 YO
3 YO
4 YO
5 YO
SCOTT@orcl_11g> SELECT * FROM TABLE (pipetest.list_one (SYSDATE))
2 /
COL_1 COL_2
---------- ----------
1 YO
2 YO
3 YO
4 YO
5 YO
-- with two functions, the second calling the first:
SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE pipetest
2 AS
3 TYPE test_rec_type IS RECORD (
4 col_1 NUMBER(8),
5 col_2 VARCHAR2(10)
6 );
7 TYPE test_tab_type IS TABLE OF test_rec_type;
8 FUNCTION list_one (dat_from IN DATE , date_to IN DATE,
9 nr_from IN NUMBER, nr_to IN number)
10 RETURN test_tab_type PIPELINED;
11 FUNCTION list_two (dat IN DATE)
12 RETURN test_tab_type PIPELINED;
13 END;
14 /
Package created.
SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE BODY pipetest AS
2 FUNCTION list_one (dat_from IN DATE , date_to IN DATE,
3 nr_from IN NUMBER, nr_to IN number)
4 RETURN test_tab_type PIPELINED IS
5 out_rec test_rec_type;
6 BEGIN
7 FOR i IN 1..5 LOOP
8 out_rec.col_1 := i;
9 out_rec.col_2 := 'YO';
10 pipe ROW (out_rec);
11 END LOOP;
12 RETURN;
13 END;
14 FUNCTION list_two (dat IN DATE)
15 RETURN test_tab_type PIPELINED IS
16 out_tab test_tab_type;
17 BEGIN
18 SELECT t.* BULK COLLECT INTO out_tab
19 FROM TABLE (list_one (dat, dat, 1, 9999)) t;
20 FOR i IN 1 .. out_tab.COUNT LOOP
21 pipe ROW (out_tab(i));
22 END LOOP;
23 RETURN;
24 END;
25 END;
26 /
Package body created.
SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> SELECT * FROM TABLE (pipetest.list_one (SYSDATE, SYSDATE, 2, 5))
2 /
COL_1 COL_2
---------- ----------
1 YO
2 YO
3 YO
4 YO
5 YO
SCOTT@orcl_11g> SELECT * FROM TABLE (pipetest.list_two (SYSDATE))
2 /
COL_1 COL_2
---------- ----------
1 YO
2 YO
3 YO
4 YO
5 YO
SCOTT@orcl_11g>
|
|
| |
Goto Forum:
Current Time: Sat May 17 03:18:10 CDT 2008
Total time taken to generate the page: 0.03656 seconds |
]
Members
Search
Help
Register
Login
Home
Re: Calling pipelined function from pipelined function.