Oracle PL/SQL array input into parameter of pipelined function

Question

I am new to PL/SQL. I have created a pipelined function inside a package which takes as its parameter input an array of numbers (nested table).

But I am having trouble trying to run it via an sql query. Please see below

my input array

 CREATE OR REPLACE TYPE num_array is TABLE of number;

my function declaration

CREATE OR REPLACE PACKAGE "my_pack" as
    TYPE myRecord is RECORD(column_a NUMBER);
    TYPE myTable IS TABLE of myRecord;

    FUNCTION My_Function(inp_param num_array) return myTable PIPELINED;

end my_pack;

my function definition

CREATE OR REPLACE PACKAGE BODY "my_pack" as

FUNCTION My_Function(inp_param num_array) return myTable PIPELINED as
        rec myRecord;
    BEGIN

        FOR i in 1..inp_param.count LOOP
            FOR e IN 
                (
                   SELECT column_a FROM  table_a where id=inp_param(i)

                )
                LOOP
                rec.column_a := e.column_a;

                PIPE ROW (rec); 

            END LOOP;
        END LOOP;

    RETURN;
END;

end my_pack;

Here is the latest code I've tried running from toad. But it doesn't work

declare
    myarray num_array;
    qrySQL varchar2(4000);
begin
    myarray := num_array(6341,6468);
    qrySQL := 'select * from TABLE(my_pack.My_Function(:myarray))';
    execute immediate qrySQL;
end;

So my question is how can I feed an array into this pipelined function from either TOAD or SQL Developer. An example would be really handy.

Thanks

"It doesn't work" is not a diagnostically-useful error message. What happens? Apart from maybe saying MY_PACK isn't recognised? — Alex Poole, Feb 03 '15 at 15:33
Hi Alex, the error is : not all variables bound at line 9 . But the problem is I have been getting a whole array of errors before tweeking, so i was hoping for an established method of running a pipelined function that takes an array of numbers. I haven't found any thing that works from looking around the internet. — , Feb 03 '15 at 15:38

Alex Poole · Accepted Answer · 2015-02-03T18:21:38.983

The error is fairly clear, you have a bind variable that you haven't assigned anything to. You need to pass your actual array with:

qrySQL := 'select * from TABLE(my_pack.My_Function(:myarray))';
execute immediate qrySQL using myarray;

It's maybe more useful, if you want to call it from PL/SQL, to use static SQL as a cursor:

set serveroutput on
declare
    myarray num_array;
begin
    myarray := num_array(6341,6468);
    for r in (select * from TABLE(my_pack.My_Function(myarray))) loop
      dbms_output.put_line(r.column_a);
    end loop;
end;
/

Or just query it statically as a test, for fixed values:

select * from TABLE(my_pack.My_Function(num_array(6341,6468)));

SQL Fiddle with some minor tweaks to the function to remove errors I think came from editing to post.

Alex your a life saver, I would Up vote you but I dont have enought points to do that. Once I get 15 points I'll make sure your upvoted — , Feb 03 '15 at 17:26

Oracle PL/SQL array input into parameter of pipelined function

1 Answers1