Arrays and Stored Procedures

joec_ has asked for the wisdom of the Perl Monks concerning the following question:

Hi, I have a function in PL/SQL which should take in arrays for some of its parameters for example:

begin pkg.set(:array_of_ids, :array_of_param_names, :array_of_param_types, :array_of_values, 1); end;

When i pass in arrays using

$s->bind_param_array(":array_of_ids",\@id); for all the arrays and execute it with

$s->execute_array({ ArrayTupleStatus => \@tuple_status });

and run the script, i get a 'wrong number or type of arguments passed to set' error.

Please can anyone offer any advice on what i am doing wrong? Is it even possible to bind arrays in this way?

Thanks

Joe

Comment on Arrays and Stored Procedures Select or Download Code

Replies are listed 'Best First'.
Re: Arrays and Stored Procedures by mje (Curate) on Nov 20, 2008 at 17:13 UTC
I'm not completely sure but I don't think you can pass arrays to a pl/sql procedure with DBD::Oracle - I certainly can't find it in the DBD::Oracle pod. When I have needed to something like this I have used temporary tables. As far as I am aware execute_array is for performing the same operation once on each set of values e.g., an insert into a table where each array index holds one set of columns for the row.</p.	[reply]
Re^2: Arrays and Stored Procedures by joec_ (Scribe) on Nov 21, 2008 at 00:35 UTC
Hi, could you explain a little further about temporary table approach. I do not know if this will be possible as i do not have access to the schema containing the data, only the procedures (security and confidentiality etc). If you could give a little detail, i will see what the DBA can implement. Thanks.	[reply]
Re^3: Arrays and Stored Procedures by mje (Curate) on Nov 21, 2008 at 08:43 UTC
I'm assuming that since you have 4 parameters all arrays that each index represents a group of values i.e., array_of_ids[0] goes with array_of_param_names[0] etc. create a global temporary table: `CREATE GLOBAL TEMPORARY TABLE mygtbl ( id type, param_name type, param_type type, value type ) on commit preserve rows;` [download] Your perl code changes to "insert into mygtbl values(?,?,?,?)", and you can use execute_array to insert the values. You then either change the procedure to read the values from mygtbl or you write a wrapper procedure which reads the values from mygtbl into arrays and pass them to your original procedure.	[reply] [d/l]
Re: Arrays and Stored Procedures by Bloodnok (Vicar) on Nov 20, 2008 at 17:09 UTC
Could it be that `$s->bind_param_array(":array_of_ids",\@id)` is binding `:array_of_ids` to an array ref, not an array. Does $s->bind_param_array(":array_of_ids", @id)</c>, or similar, work ? A user level that continues to overstate my experience :-))	[reply] [d/l] [select]