4 Replies Latest reply: May 2, 2014 6:27 AM by _Karthick_ RSS

    pipeline

    1043556


      hi all

      i created a pipeline function "abc_pipe_fun" in my pak  i want to call this pipeline fun in another procedure of same pak

      i called like this

      abc:=abc_pipe_fun('as');

      here abc is a var of datatype is collection ,but it is not working .

        • 1. Re: pipeline
          John Stegeman

          It is not working... most descriptive.

           

          I don't see any situation where it would make sense to call a pipelined function from a procedure like.

          • 2. Re: pipeline
            BluShadow

            Pipelined functions are designed to provide a pipeline of data to the table function of an SQL statement.  They do not provide a structured result set that can be simply assigned to a collection type variable.

            • 3. Re: pipeline
              Frank Kulash

              Hi,

              f3665bb6-1b11-4d8f-ba83-befd00bfce5e wrote:

               


              hi all

              i created a pipeline function "abc_pipe_fun" in my pak  i want to call this pipeline fun in another procedure of same pak

              i called like this

              abc:=abc_pipe_fun('as');

              here abc is a var of datatype is collection ,but it is not working .

              Review the PL/SQL manual  ( PL/SQL Optimization and Tuning ) or some other source for the correct way to call a pipelined function.

               

              Whenever you post a question here, post a complete test script (including full CREATE PACKAGE statements) so the people who want to help you can re-create the problem and test their ideas.

               

              A lot of people are willing to share what they know about PL/SQL.  Are you willing to share what you know about the problem?  For example, how do you know it's not working?  Share the error message, or details about the wrong results.

              • 4. Re: pipeline
                _Karthick_

                You cant use a pipelined function on the right side of an assignment operator. You need to process it like table using TABLE function. Check below example.

                 

                SQL> create or replace type tbl as table of number
                  2  /

                 

                Type created.

                 

                SQL> create or replace function fn (no integer) return tbl
                  2  pipelined
                  3  as
                  4  begin
                  5    for i in 1..no
                  6    loop
                  7       pipe row(i);
                  8    end loop;
                  9  end;
                10  /

                 

                Function created.

                 

                SQL> declare
                  2    l_tbl tbl := tbl();
                  3  begin
                  4    l_tbl := fn(10);
                  5  end;
                  6  /
                  l_tbl tbl := tbl();
                *
                ERROR at line 2:
                ORA-06550: line 1, column 10:
                PLS-00653: aggregate/table functions are not allowed in PL/SQL scope


                SQL> declare
                  2    l_tbl tbl := tbl();
                  3  begin
                4    select * bulk collect into l_tbl
                  5      from table(fn(10));
                  6  end;
                  7  /

                 

                PL/SQL procedure successfully completed.

                 

                If you want to use it with an assignment operator then you should not define it as a pipelined function You need to have a regular function that returns a collection.

                 

                SQL> drop function fn;

                 

                Function dropped.

                 

                SQL> create or replace function fn (no integer) return tbl
                  2  as
                  3    l_tbl tbl := tbl();
                  4  begin
                  5    for i in 1..no
                  6    loop
                  7       l_tbl.extend;
                  8       l_tbl(i) := i;
                  9    end loop;
                10
                11    return l_tbl;
                12  end;
                13  /

                 

                Function created.

                 

                SQL> declare
                  2    l_tbl tbl := tbl();
                  3  begin
                  4    l_tbl := fn(10);
                  5  end;
                  6  /

                 

                PL/SQL procedure successfully completed.

                 

                SQL>