in reply to Arrays and Stored Procedures

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.

Replies are listed 'Best First'.
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]

      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]

In Section Seekers of Perl Wisdom