Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine

Passing arrays to an oracle stored procedure

by Anonymous Monk
on Aug 09, 2007 at 08:49 UTC ( #631489=perlquestion: print w/replies, xml ) Need Help??
Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hi, I want to pass an array of parameters to an Oracle stored procedure and get a result set. Is this possible using perl DBI or any other perl modules? I saw that bind_param_array can be used to pass an array as arguments. But this can only be used with queries like INSERT, UPDATE but not SELECT. Any suggestions?
  • Comment on Passing arrays to an oracle stored procedure

Replies are listed 'Best First'.
Re: Passing arrays to an oracle stored procedure
by Moron (Curate) on Aug 09, 2007 at 10:26 UTC
    No you can't bind an array because ORACLE doesn't have arrays. So you just bind some variables and fetch each row into those variables and THEN push them into an array. For example you have $column1 and $column2 which you bind to the oracle variables or columns, depending on how the procedure returns them. You DBI->execute the stored procedure then repeatedly DBI->fetchrow which loads the bound variables from the next row of the resultset and if it succeeded you 'manually' push $column1 into @column1 and $column2 into @column2 before looping back for the next row of the result set.

    ^M Free your mind!

Re: Passing arrays to an oracle stored procedure
by bart (Canon) on Aug 09, 2007 at 11:44 UTC
    I'm not sure what you want to pass an array for... but note that Oracle can use "tables" of literals in SQL. That might be a way to do what you want.

    Here's the code in SQL that you can most likely also use in DBI:

    create type StringTable as table of varchar2(4000); select A.dummy, B.column_value from dual A, table(StringTable('one', 'two', 'three')) B;
    This example makes a chartesian join of data from system table dual (with 1 row of one column dummy having the value 'X') with my emulated table of literals, producing 1*3 = 3 rows:
    DUMMY COLUMN_VALUE ----- ------------------ X one X two X three

    Likewise, if your stored procedure accepts a parameter of type StringArray, then you can just pass the data along this way:

    StringTable('one', 'two', 'three')

    Note that this will only work with table types where you don't specify index by, because the StringTable() here works as a constructor and that only works works with tables without index by.

    The way to pass the data through the statement with placeholders, is by using as many placeholders as there are values:

    begin myproc(?, StringArray(?,?,?)); end;
    and pass a flat list of 4 values.
      Thanks for the replies. But my requirement is something like this.

      Input to my stored procedure is an associative array.
      TYPE msg_comp IS TABLE OF VARCHAR2(40) INDEX BY VARCHAR2(100); TYPE messages_table IS TABLE OF VARCHAR2(200); TYPE message_versions_tt IS TABLE OF SOME_TABLE%ROWTYPE; PROCEDURE get_message_versions( msg_comp_names IN msg_comp, message_versions OUT message_versions_tt );
      So this procedure takes in an associative array as an input and returns a set of rows as a table.

      How do I call this procedure from perl using DBI?
      That is how to I bind a perl hash to an oracle associative array?
        I am new to 'perl to DB' communication and I have same question. How to bind perl hash to associative array (or nested table) and pass it to procedure from a perl program?
Re: Passing arrays to an oracle stored procedure
by andreas1234567 (Vicar) on Aug 09, 2007 at 10:48 UTC
    As Moron points out, you can't pass an array as argument to an Oracle stored procedure. However, assuming you would want to pass the array as an input parameter, you can convert your array to XML as described in Re: Objects in PL/SQL in DBD::Oracle.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://631489]
Approved by GrandFather
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others avoiding work at the Monastery: (3)
As of 2017-03-25 20:16 GMT
Find Nodes?
    Voting Booth?
    Should Pluto Get Its Planethood Back?

    Results (313 votes). Check out past polls.