I'm the database developer working with a front-end developer on making some needed improvements to an existing application. In one part of the application the front end had a list of id's to insert and it was looping and for each id would call a database procedure to insert one row. That was horribly slow so we decided that the front-end would put all the id's in an array and pass that to the database to insert all of them in a single call. So on the database side I created a type
create or replace type vartab as table of varchar2(4000);
/
Then in the procedure I changed the type of the input parameter from varchar2 to vartab. The insert statement was:
insert into this_table (col1, col2. col3)
select col1a, col2a, col3a
from that_table
where id = i_param;
which I changed to
insert into this_table (col1, col2. col3)
select col1a, col2a, col3a
from that_table
where id in (select column_value from table(i_param));
Everything is OK so far. The database proc compiles. I can write a PL/SQL block to test it and that works fine. The problem is that when the front-end tries to call the proc it gets the error "wrong number or types of arguments in call to myproc".
The front-end is .NET 4.0 and it seems that in order to be compatible with that my table has to be an associative array. OK. Since I'm already using the vartab type elsewhere I add a new type
create or replace type stringarray as table of varchar2(4000) index by binary_integer;
/
I change my input parameter to the procedure from vartab to stringarray but now I get the error PLS-00382: expression is of wrong type in my select. Oh I know I need to cast it. I'm not sure of the syntax so I google and from what I can find it appears the correct syntax is
where id in (select column_value from table(cast(i_param as vartab)));
but that still gets the "expression is of wrong type" error.
Can someone give me the correct syntax to cast an associative array as a collection.