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 Go to next message
ThomasG
Messages: 3189
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 Go to previous messageGo to next message
ThomasG
Messages: 3189
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8620
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>

icon14.gif  Re: Calling pipelined function from pipelined function. [message #316343 is a reply to message #316338] Thu, 24 April 2008 12:47 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Thanks Barbara, the first option one will work great for this case, and the other two will surely come in handy in the future. Very Happy
Previous Topic: Query to produce list of minutes in a day
Next Topic: Query Problem
Goto Forum:
  


Current Time: Fri Dec 02 14:12:22 CST 2016

Total time taken to generate the page: 0.23214 seconds