How to pass array as input parameter in Oracle Function

Question

I have a function to BULK insert data using FORALL.

create or replace type l_array_tab as table of number;

create or replace FUNCTION fn_insert_using_array(
    L_TAB         VARCHAR2,
    L_COL_NAME    VARCHAR2,
    L_ARRAY L_ARRAY_TAB)
    RETURN NUMBER
AS
    SQL_STMT  VARCHAR2(32767);
    sql_count NUMBER;
BEGIN
    FORALL i IN L_ARRAY.first .. L_ARRAY.LAST

        EXECUTE immediate 'INSERT  INTO my_table 
               Select * from '||L_TAB
               ||' where '||L_COL_NAME||' := :1' using L_ARRAY(i);
        sql_count:= SQL%ROWCOUNT;

  RETURN SQL_COUNT;
end;

I need to call this function from another stored procedure or plsql block in this example. While calling this function, I am getting error as wrong number or type of inputs.

This is how I am calling the function:

create or replace type l_array_orig_tab as table of number;

Declare
    l_array_orig l_array_orig_tab :=l_array_orig_tab();
    l_tab varchar2(30): ='my_tab_orig';
    l_col_name varchar2(30) :='insert_id';
    V_COUNT NUMBER;
    cursor c1 is select * from my_tab_orig;
begin
    open c1;
    LOOP
        FETCH c1 BULK COLLECT INTO l_array_orig limit 1000;
        EXIT WHEN L_ARRAY_orig.COUNT =0;
        V_COUNT:= fn_insert_using_array(L_TAB, L_COL_NAME,l_array_orig); 
    END LOOP;
END ;

Please suggest how to call the function.

Answer 1

I am getting error as wrong number or type of inputs

You are getting the error because l_array_orig_tab is a different type from l_array_tab. It doesn't matter that they have the same structure, as far as Oracle knows they are different types. Oracle is a database engine and it strongly enforces type safety. There is no duck typing here.

So the simplest solution is to use the correct type when calling the function:

Declare
    l_array_orig l_array_tab :=l_array_tab(); -- change this declaration
    l_tab varchar2(30): ='my_tab_orig';
    l_col_name varchar2(30) :='insert_id';
    V_COUNT NUMBER;
    cursor c1 is select * from my_tab_orig;
begin
    open c1;
    LOOP
        FETCH c1 BULK COLLECT INTO l_array_orig limit 1000;
        EXIT WHEN L_ARRAY_orig.COUNT =0;
        V_COUNT:= fn_insert_using_array(L_TAB, L_COL_NAME,l_array_orig); 
    END LOOP;
END ;

"The function fn_insert_using_array is in a different schema and also the Type."

So the schema which owns the function has granted you EXECUTE privilege on the function. But they also need to grant you EXECUTE on the type. This is their responsibility: they defined the function with a UDT in its signature so they have to give you all the privileges necessary to call it.

I don't don't whether this is a toy example just for posting on SO, but if it isn't there is no need to create a type like this. Instead use the documented Oracle built-in table of numbers, sys.odcinumberlist.