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 Go to next message
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;
Below is the pipeline function that reads from above table
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;
I am trying to write a procedure where , there will be a cursor that has data for 04-APR and 05-APR,, and i want to call the above pipeline query inside the procedure and the result should be data from mytable for 04-APR and 05-APR (whatever dates we use in the curosr)
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;
I get the error that PLS-00428: an INTO clause is expected in this SELECT statement

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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67888
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.
Now this assumes your client program is able to handle an Oracle ref cursor.

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.
You have to tell Oracle where (in which variable(s)) you want to put the result of the query, this is the meaning of the message.

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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67888
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.
But I don't see why get_data could not take 2 parameters and directly return the final result (unless this is an existing function you can't change). If so, why not:
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 #684241 is a reply to message #684240] Mon, 26 April 2021 14:44 Go to previous messageGo to next message
desmond30
Messages: 41
Registered: November 2009
Member
Thank you !
Re: pipeline function call in a stored proc and loop [message #684244 is a reply to message #684240] Tue, 27 April 2021 08:55 Go to previous messageGo to next message
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;

Re: pipeline function call in a stored proc and loop [message #684245 is a reply to message #684244] Tue, 27 April 2021 09:30 Go to previous messageGo to next message
Michel Cadot
Messages: 67888
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

In this case, you have to loop on get_data result and only retrieve the distinct dates from C1 as this is the only thing you use:
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 distinct mydate from PTEST
 27       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'));
 28      BEGIN
 29        FOR R1 IN C1 LOOP
 30         for r2 in (
 31           select * from table (my_PKG.getdata(R1.MYDATE))
 32         ) loop
 33           pipe row (r2);
 34         END LOOP;
 35       END LOOP;
 36       END;
 37    END my_PKG;
 38  /

Package body created.

SQL> select * from table(my_PKG.P_REPORT);
         X MYDATE
---------- -------------------
       100 03/04/2021 00:00:00
       200 03/04/2021 00:00:00
       100 05/04/2021 00:00:00
       100 02/04/2021 00:00:00

4 rows selected.
Re: pipeline function call in a stored proc and loop [message #684246 is a reply to message #684245] Tue, 27 April 2021 10:14 Go to previous message
desmond30
Messages: 41
Registered: November 2009
Member
Thank you !
Previous Topic: grouping logic
Next Topic: ORA-00979
Goto Forum:
  


Current Time: Thu Jun 24 22:54:35 CDT 2021