Home » SQL & PL/SQL » SQL & PL/SQL » pipeline function call in a stored proc and loop
| pipeline function call in a stored proc and loop [message #684236] |
Mon, 26 April 2021 11:21  |
desmond30
Messages: 41
Registered: November 2009
|
Member |
|
|
Already posted this in Oracle forums but looks like the captcha there is giving me issues so trying to post the same question here,
I have a pipeline function that reads data from a table, given the date
CREATE TABLE PTEST
(
X NUMBER,
MYDATE DATE
);
SET DEFINE OFF;
Insert into PTEST
(X, MYDATE)
Values
(100, TO_DATE('4/4/2021', 'MM/DD/YYYY'));
Insert into PTEST
(X, MYDATE)
Values
(100, TO_DATE('4/5/2021', 'MM/DD/YYYY'));
Insert into PTEST
(X, MYDATE)
Values
(100, TO_DATE('4/2/2021', 'MM/DD/YYYY'));
Insert into PTEST
(X, MYDATE)
Values
(200, TO_DATE('4/3/2021', 'MM/DD/YYYY'));
COMMIT;
CREATE OR REPLACE PACKAGE my_PKG
IS
CURSOR ctest (pdate date)
is
select x, mydate
from ptest where mydate = pdate;
TYPE c_type IS TABLE OF ctest%ROWTYPE;
FUNCTION getdata (
pdate IN DATE DEFAULT NULL
)
RETURN c_type
PIPELINED;
END my_PKG;
/
CREATE OR REPLACE PACKAGE BODY my_PKG
AS
FUNCTION getdata (
pdate IN DATE DEFAULT NULL
)
RETURN c_type
PIPELINED
AS
v_date DATE;
v_c_type c_type;
v_count PLS_INTEGER := 0;
BEGIN
v_date := pdate;
FOR r IN ctest (v_date
)
LOOP
v_c_type := c_type (r);
PIPE ROW (r);
v_count := v_count + 1;
END LOOP;
RETURN;
EXCEPTION
when others then null;
END getdata;
END my_PKG;
CREATE OR REPLACE PROCEDURE P_REPORT
IS
CURSOR C1 IS
SELECT * from PTEST WHERE MYDATE IN ('04-APR-2021','05-APR-2021');
BEGIN
FOR R1 IN C1 LOOP
select * from table (my_PKG.getdata(R1.MYDATE));
END LOOP;
END;
How can I call the pipeline query inside a stored proc ? basically I get the data for one date by using below query, i am trying a PLSQL stored proc that when called, gives data for more than one date
SELECT * FROM TABLE (my_PKG.getdata('02-APR-2021'));
I will be using the stored procedure call in a microsoft power bi report direct query (live), will a ref cursor solve my problem or is there any other way ?
[Edit MC: removed tons of useless empty lines]
[Updated on: Mon, 26 April 2021 12:24] by Moderator Report message to a moderator |
|
|
|
| Re: pipeline function call in a stored proc and loop [message #684237 is a reply to message #684236] |
Mon, 26 April 2021 11:24  |
desmond30
Messages: 41
Registered: November 2009
|
Member |
|
|
I tried the sys_refcursor ,reading it from sql statement, at the end I still have a sql statement that is select * from table(populate_p('02-APR-2021')); Question is how to put it in a cursor for loop ?
taking a cue from https://community.oracle.com/tech/developers/discussion/888365/pl-sql-101-understanding-ref-cursors
create or replace type ptype as object(x number,
mydate date);
create or replace type t_ptype as table of ptype;
create or replace function get_pdata(p_mydate in date) return sys_refcursor is
v_rc sys_refcursor;
begin
open v_rc for 'select x, mydate from ptest where mydate = :mydate' using p_mydate;
return v_rc;
end;
create or replace function populate_p(mydate date := null)
return t_ptype is
v_ptype t_ptype := t_ptype(); -- Declare a local table structure and initialize it
v_cnt number := 0;
v_rc sys_refcursor;
v_x number;
v_mydate date;
begin
v_rc := get_pdata(mydate);
loop
fetch v_rc into v_x, v_mydate;
exit when v_rc%NOTFOUND;
v_ptype.extend;
v_cnt := v_cnt + 1;
v_ptype(v_cnt) := ptype(v_x,v_MYDATE);
end loop;
close v_rc;
return v_ptype;
end;
select * from table(populate_p('02-APR-2021'));
[Edit MC: removed tons of useless empty lines]
[Updated on: Mon, 26 April 2021 12:25] by Moderator Report message to a moderator |
|
|
|
| Re: pipeline function call in a stored proc and loop [message #684238 is a reply to message #684237] |
Mon, 26 April 2021 12:44 |
 |
Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
REF CURSOR is indeed the correct way for this kind of problem:
SQL> create or replace function get_pdata(p_mydate in date) return sys_refcursor is
2 v_rc sys_refcursor;
3 begin
4 open v_rc for 'select x, mydate from ptest where mydate = :mydate' using p_mydate;
5 return v_rc;
6 end;
7 /
Function created.
SQL> var c refcursor;
SQL> exec :c := get_pdata(to_date('02/04/2021','DD/MM/YYYY'))
PL/SQL procedure successfully completed.
SQL> print c
X MYDATE
---------- -------------------
100 02/04/2021 00:00:00
1 row selected.
Quote:How can I call the pipeline query inside a stored proc ?
There (just fixing your code without any analysis of the balance of it and your actual end needs):
SQL> CREATE OR REPLACE PROCEDURE P_REPORT
2 IS
3 CURSOR C1 IS
4 SELECT * from PTEST WHERE MYDATE IN (to_date('04/04/2021','DD/MM/YYYY'),to_date('05/04/2021','DD/MM/YYYY'));
5 v ptest%ROWTYPE;
6 BEGIN
7 FOR R1 IN C1 LOOP
8 select * into v from table (my_PKG.getdata(R1.MYDATE));
9 dbms_output.put_line(v.x||' '||v.mydate);
10 END LOOP;
11 END;
12 /
Procedure created.
SQL> exec p_report;
100 04/04/2021 00:00:00
100 05/04/2021 00:00:00
PL/SQL procedure successfully completed.
Note:
Quote:EXCEPTION
when others then null;
This is the BIGGEST error you can make in PL/SQL, read WHEN OTHERS.
|
|
|
|
| Re: pipeline function call in a stored proc and loop [message #684239 is a reply to message #684236] |
Mon, 26 April 2021 13:35 |
desmond30
Messages: 41
Registered: November 2009
|
Member |
|
|
Thank you, the procedure p_report works, now problem is in power bi i cannot execute a stored procedure, can this be a function that I can use in a select statement which will return exactly what the p_report procedure is returning ?
maybe a pipeline function ? this is because if its a pipeline function power bi accepts it (as its like saying select * from table)...
trying to write a simple package that has a pipeline function that has p_report logic..will keep posted in this thread if it works.
|
|
|
|
| Re: pipeline function call in a stored proc and loop [message #684240 is a reply to message #684239] |
Mon, 26 April 2021 14:01 |
|
Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
You can make p_report a pipelined function as you did it for getdata:
SQL> CREATE OR REPLACE PACKAGE my_PKG
2 IS
3 CURSOR ctest (pdate date)
4 is
5 select x, mydate
6 from ptest where mydate = pdate;
7 TYPE c_type IS TABLE OF ptest%ROWTYPE;
8 FUNCTION getdata (
9 pdate IN DATE DEFAULT NULL
10 )
11 RETURN c_type
12 PIPELINED;
13 FUNCTION P_REPORT RETURN c_type PIPELINED;
14 END my_PKG;
15 /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY my_PKG
2 AS
3 FUNCTION getdata (
4 pdate IN DATE DEFAULT NULL
5 )
6 RETURN c_type
7 PIPELINED
8 AS
9 v_date DATE;
10 v_c_type c_type;
11 v_count PLS_INTEGER := 0;
12 BEGIN
13 v_date := pdate;
14 FOR r IN ctest (v_date
15 )
16 LOOP
17 v_c_type := c_type (r);
18 PIPE ROW (r);
19 v_count := v_count + 1;
20 END LOOP;
21 RETURN;
22 END getdata;
23 FUNCTION P_REPORT RETURN c_type PIPELINED
24 is
25 CURSOR C1 IS
26 SELECT * from PTEST
27 WHERE MYDATE IN (to_date('04/04/2021','DD/MM/YYYY'),to_date('05/04/2021','DD/MM/YYYY'));
28 v ptest%ROWTYPE;
29 BEGIN
30 FOR R1 IN C1 LOOP
31 select * into v from table (my_PKG.getdata(R1.MYDATE));
32 pipe row (v);
33 END LOOP;
34 END;
35 END my_PKG;
36 /
Package body created.
SQL> select * from table(my_PKG.p_report);
X MYDATE
---------- -------------------
100 04/04/2021 00:00:00
100 05/04/2021 00:00:00
2 rows selected.
SQL> CREATE OR REPLACE PACKAGE my_PKG
2 IS
3 CURSOR ctest (pdate date)
4 is
5 select x, mydate
6 from ptest where mydate = pdate;
7 TYPE c_type IS TABLE OF ptest%ROWTYPE;
8 FUNCTION getdata (
9 pdate IN DATE DEFAULT NULL
10 )
11 RETURN c_type
12 PIPELINED;
13 FUNCTION P_REPORT RETURN c_type PIPELINED;
14 FUNCTION P_REPORT (dtlist sys.odcidatelist) RETURN c_type PIPELINED;
15 END my_PKG;
16 /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY my_PKG
2 AS
3 FUNCTION getdata (
4 pdate IN DATE DEFAULT NULL
5 )
6 RETURN c_type
7 PIPELINED
8 AS
9 v_date DATE;
10 v_c_type c_type;
11 v_count PLS_INTEGER := 0;
12 BEGIN
13 v_date := pdate;
14 FOR r IN ctest (v_date
15 )
16 LOOP
17 v_c_type := c_type (r);
18 PIPE ROW (r);
19 v_count := v_count + 1;
20 END LOOP;
21 RETURN;
22 END getdata;
23 FUNCTION P_REPORT RETURN c_type PIPELINED
24 is
25 CURSOR C1 IS
26 SELECT * from PTEST
27 WHERE MYDATE IN (to_date('04/04/2021','DD/MM/YYYY'),to_date('05/04/2021','DD/MM/YYYY'));
28 v ptest%ROWTYPE;
29 BEGIN
30 FOR R1 IN C1 LOOP
31 select * into v from table (my_PKG.getdata(R1.MYDATE));
32 pipe row (v);
33 END LOOP;
34 END;
35 FUNCTION P_REPORT (dtlist sys.odcidatelist) RETURN c_type PIPELINED
36 is
37 begin
38 for rec in (
39 select * from ptest where mydate in (select * from table(dtlist))
40 ) loop
41 pipe row (rec);
42 end loop;
43 end;
44 END my_PKG;
45 /
Package body created.
SQL> select *
2 from table(my_PKG.p_report(sys.odcidatelist(to_date('04/04/2021','DD/MM/YYYY'),
3 to_date('05/04/2021','DD/MM/YYYY'))))
4 /
X MYDATE
---------- -------------------
100 04/04/2021 00:00:00
100 05/04/2021 00:00:00
2 rows selected. |
|
|
|
|
|
| Re: pipeline function call in a stored proc and loop [message #684244 is a reply to message #684240] |
Tue, 27 April 2021 08:55 |
desmond30
Messages: 41
Registered: November 2009
|
Member |
|
|
HI Micheal,
There is one problem when I am using example 1, when i enter a mydate value which already exists, I get exact fetch returns ORA-01422: exact fetch returns more than requested number of rows
we are using cursors so I am confused why should it give this error. There are two records of April 3, hence this error
CREATE TABLE PTEST
(
X NUMBER,
MYDATE DATE
);
SET DEFINE OFF;
Insert into PTEST
(X, MYDATE)
Values
(100, TO_DATE('4/4/2021', 'MM/DD/YYYY'));
Insert into PTEST
(X, MYDATE)
Values
(100, TO_DATE('4/5/2021', 'MM/DD/YYYY'));
Insert into PTEST
(X, MYDATE)
Values
(100, TO_DATE('4/3/2021', 'MM/DD/YYYY'));
Insert into PTEST
(X, MYDATE)
Values
(100, TO_DATE('4/2/2021', 'MM/DD/YYYY'));
Insert into PTEST
(X, MYDATE)
Values
(200, TO_DATE('4/3/2021', 'MM/DD/YYYY'));
COMMIT;
CREATE OR REPLACE PACKAGE my_PKG
IS
CURSOR ctest (pdate date)
is
select x, mydate
from ptest where mydate = pdate;
TYPE c_type IS TABLE OF ptest%ROWTYPE;
FUNCTION getdata (
pdate IN DATE DEFAULT NULL
)
RETURN c_type
PIPELINED;
FUNCTION P_REPORT RETURN c_type PIPELINED;
END my_PKG;
/
CREATE OR REPLACE PACKAGE BODY my_PKG
AS
FUNCTION getdata (
pdate IN DATE DEFAULT NULL
)
RETURN c_type
PIPELINED
AS
v_date DATE;
v_c_type c_type;
v_count PLS_INTEGER := 0;
BEGIN
v_date := pdate;
FOR r IN ctest (v_date
)
LOOP
v_c_type := c_type (r);
PIPE ROW (r);
v_count := v_count + 1;
END LOOP;
RETURN;
END getdata;
FUNCTION P_REPORT RETURN c_type PIPELINED
is
CURSOR C1 IS
SELECT * from PTEST
WHERE MYDATE IN (to_date('02/04/2021','DD/MM/YYYY'),to_date('03/04/2021','DD/MM/YYYY'),to_date('05/04/2021','DD/MM/YYYY'));
v ctest%ROWTYPE;
BEGIN
FOR R1 IN C1 LOOP
select * into v from table (my_PKG.getdata(R1.MYDATE));
pipe row (v);
END LOOP;
END;
END my_PKG;
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Mar 28 04:39:01 CDT 2023
|
] 
Search
Help
Members
Register
Login
Home
