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;
[download]

DUMMY COLUMN_VALUE
----- ------------------
X     one
X     two
X     three
[download]

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')
[download]

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;
[download]

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  );
[download]

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?