Accessing Unix Shell arrays from pl/sql!

[jmiturbe]

Hello,

I have a unix shell script that generates unix shell arrays and I want to load those arrays's values into an Oracle 8i database from the same script, but cannot correctly access them from sqlplus.

I am looking for something like (this is a simplified script that has the same problem):

#!/usr/bin/ksh
...

array1[0]="text1"
array1[1]="text1"
array1[2]="text2"
array1[3]="text3"

...

SQL="/oracle817/bin/sqlplus user/password"
$SQL <<finish
set pagesize 0
set linesize 800
set colsep ','
set feedback off
set serveroutput on

variable var1 varchar2( 100 );

begin
:num1 := 0;
while :num1 < 3 loop
:var1 := ${array1[:num1]};
:num1 := :num1 + 1;
end loop;

end;
/
print var1;

finish
...
...

I can access the unix array if the array_element_number is specified "manually". But I can't get it working when this array_element_number is dinamically set on the sql loop.

Any ideas. Thanks in advance,

jmiturbe

 

[hoinz]

Hello,

I don't think it can be done at all.
However I would like to be proven wrong.

Just consider the way how your script will be executed.
First let's assume we have something like this:
array1[0]="text1"
sqlplus ...
:var1 := ${array1[0]};
...

Here, in a first step, the Unix shell will expand variables, and send this to sqlplus:
:var1 := text1;

You wrote:
I can access the unix array if the array_element_number is specified "manually".
There is some kind of vagueness or misunderstanding, imho.
sqlplus will not take notice of the unix array, nor access it. It will get the actual value instead.

If however we have this:
sqlplus ...
:var1 := ${array1[:num1]};
Here the Unix shell will not be able to expand ${array1[:num1]};
We will get an error like ': unexpected', and leaving away the ':' won't help either.

And the only workaround that comes to my mind:
Do the looping in Unix, not in sqlplus.
Then you may use:
:var1 := ${array1[$num1]};

But I suppose this idea will not help much, if your real problem is a bit more difficult than the example you showed here.

regards

 

[jmiturbe]

Hello,

You are right on all of your appreciations.

The bigest problem with looping in Unix is that my script is more complex tahn the example. It has several loops, each loop with multiple iterations, so I think that it's not a good idea to connect and disconnect to the DB so many times.

I could use temporary files to store arrays's values and then access those values from sql, but I don't like this approach and I would prefer to access those arrays directly from sql.

Any other ideas?

Yours,

jmiturbe

 

[SantaMufasa]

jmiturbe,

What about generating all of the Unix-shell-script output to a flat file, then accessing all of those data via an Oracle EXTERNAL table?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[jmiturbe]

Hello again,

A different approach could be using another unix variable as the loop counter and access the unix array this way:

${array1[${counter}]}

But it's mandatory to be able to modify ${counter} value from the sql loop!

Does anybody know how to set a unix variable value from sql?

Thanks in advance,

jmiturbe

 

[Dagon]

SQL has no ability to modify UNIX variables. It can pass information back through flat files or something like variable=`sqlplus ....`, but it can't directly change anything in the UNIX environment.

You'll have to alter your approach so that it's driven by the UNIX procedure rather than trying to do it the other way round. An obvious approach would be to move the loop into UNIX and call sql*plus for each iteration, passing in a different array variable each time.

 

[hoinz]

jmiturbe,
hello again,

as Dagon pointed out, your different approach will not work, because sqlplus cannot modify Unix variables.
Let me add that it wouldn't work, even if sqlplus could.

What I tried to explain in my previous post is a not just a problem of syntax, but a problem of process flow. It seems I was not very clear.
The main obstacle is that Unix variables are expanded only once, before sqlplus may use them.
So sqlplus would see only one value for ${array1[${counter}]}, the value it had at the time when sqlplus was called. Modifying ${counter} later on would not change the value that had been provided to sqlplus by the Unix shell.
Just imagine all the ${array1[${counter}]} in your script have been replaced by text1. And this is all sqlplus can see.

It's definitely time to look for another approach, I think.