Passing arrays to oracle from unix

Shell Programming and Scripting
1

Hi all
Iam trying to send an array to oracle procedure from unix. Iam writing a program in K Shell to pass this array to oracle. Is it possible. Please advice

thanks
Krishna

2

Need more information...what do you mean by passing an array to Oracle? Is this into a stored procedure, or directly into a table....please give more detail!

3

Yes passing an array to oracle procedure

Thanks in advance
Krishna sai

4

someone I work with got around this problem by passing a delimited string to the oracle procedure and then parsing the string in the proc, just an idea, hope it helps

-Ed

5

Thanks edog
Iam already concatenating he columns into a string. I beleive KShell supports only single dimensional array

Thanks
Krishna

6

yep sai , u r rite , shell script only allow one dimensional arrays .
is u want to fill a one dimension array from oracle , then this mite help

set -A `sqlplus userid/pwd @urcode.sql`

in
urcode.sql , u will have to have dbms_output.put_line so that whatever is passed out is captured into the array .

cheers,
ace

P.S. : 1) Awk mite give u more freedom in arrays
2) Do let me know on whether there is ne other mehod

7

Thanks Ace
Your tip helped me in passing array from oracle to Shell.
I tried from .sql file passing array to Oracle procedure and getting back the array This also worked fine.
Iam trying to use this array in .sql file in the shell script.
If this idea works I think I can send the array from Oracle to and shell and vice versa.
I will update this if I accomplish the task.

Please update if you have any other opinion about this

Thanks
Krishna

8

Hi Ace_cool
Can you tell me How Ican use tha awk command to get tha data into array

Thanks
Krishna Sai