Putting SQL into an array insted of spooling to a file

[Larshg]

Hi

Does anyone know how I can put the out put from an SQL sentence into an array insted og spooling it into a file?

ex.
spool /home/ttsowner/LSG/tmp/sysdata1
select to_char(to_date(sysdate -1), 'YYYYMMDD')
from dual;

insteed of spooling it to sysdata1 I'd like it to go directly into an array.

/Larshg

 

[danielhozac]

What kind of array? What database are you using? //Daniel

 

[Larshg]

Well Im using oracle and I would like to but it into an ksh array.

normaly I do this
#!/bin/ksh


sqlplus test/test@test << !
spool /home/ttsowner/LSG/tmp/sysdata1
select to_char(to_date(sysdate -1), 'YYYYMMDD')
from dual;
spool off
exit
!

set -A array `cat /home/ttsowner/LSG/tmp/sysdata1`

I like to but my SQL directly into an array

 

[dickiebird]

If all you want to do is grab the date, then try this (untested)

#!/bin/ksh

DATEFOUND=`sqlplus test/test@test << !
select to_char(to_date(sysdate -1), 'YYYYMMDD')
from dual;
!`
echo $DATEFOUND

HTH

Dickie Bird (-)))

 

[dickiebird]

If all you want to do is grab the date, then try this (untested)

#!/bin/ksh

DATEFOUND=`sqlplus test/test@test << !
select to_char(to_date(sysdate -1), 'YYYYMMDD')
from dual;
!`
echo $DATEFOUND

(You may have to declare a variable in the sql and 'print' it in the sql too)

HTH

Dickie Bird (-)))

 

[marbaise]

Hi Larshg,

You should have a look on the following paper from Oracle Magazine. It discuss numerous way to interact between SQL plus and the UNIX shell.

It does the trick for me. There is an interesting section about retrieving output using a for..loop at shell level.

Look at:

http://www.oracle.com/oramag/webcolumns/2001/op_unixway.html

I hope it will help you.

Philippe.

 

[MikeLacey]

Interesting, the article you mention is written by the same person who wrote the faq759-2221. I came across the article quite a while ago, in text form and made a TT FAQ of it after asking him. Mike

Want to get great answers to your Tek-Tips questions? Have a look at faq219-2884

It's like this; even samurai have teddy bears, and even teddy bears get drunk.