Is it possible to pass unix shell arrays in Oracle stored procedure?
Is yes, how?
Thanks

The answer is: that depends.

Not taking into account command line restrictions, which may prevent large arrays from being expanded to the command line without error;

further not taking into account if an interface for such a huge amount of passed variables is possible to realize in sqlplus or whatever you use to trigger the stored procedure;

you could use the variable expansion of the ksh to expand the content of an array:

bakunin

Hi bakunin
Thanks for the response but I am looking for the syntax on how to pass unix shell arrays in Oracle stored procedure

How are you currently executing your stored procedures via shell? Are you using a Here document ( << EOF .... EOF). If so, you might be able to use the syntax noted above

Bottom line is that you are not going to be able to pass the array by making a simple assignment to an Oracle stored procedure parameter or variable.

You can do one of the following:

1. Pass the array as stated previous, which is essentially a space delimited string. The strored procedure or an anonymous PL/SQL block can then parse sting into an Index-by table (PL/SQL table).
   
   Code:

   # myarr[1]="foo"
   # myarr[2]="bar"
   # sqlplus user/pwd@db <<EOF
   BEGIN
       MyVar VARCHAR2(100) := '${myarr[ * ]}'; -- The star is separated by spaces because the code block was getting horked up
       -- The previous line is expanded as follows:
       -- MyVar VARCHAR2(100) :='foo bar';
       YourStoredProcedure (MyVar);
   END;
   /
   EOF

2. Insert the array elements into a table and the stored procedure can then retrieve the values from the table. This can be a global temporary table since the data would be temporary in nature.

Thomas

Another way to do this:
1. write the contents of the array to something like a pipe-delimited file
2. Write a PL/SQL wrapper for your SP that calls UTL_FILE to read in the file, and create an array out of it, then pass the array (table actually) to the SP.

There is no shortcut.

Oh yeah, there is this way too. I avoid using UTL_FILE. I usually want to minimize load time and UTL_FILE is notroiously slow, involves DBAs who resist helping developers (I am a DBA by the way ), and is counter intuitive when reading the input lines.

Thomas