How to return varchar from a an oracle function to Korn Shell

[Suman_7]

All,

I need to return some varchar values from Oracle to Shell and then pass that as a parameter to shell script. I can do it by spooling the output in a file,Is there any other better way of doing it. I also need to do some inserts into oracle taking the shell parameters. How can I do it. Can someone send me a sample code for it.

Thanks in advance.


Suman

[Hein van den Heuvel]

Just make sqlplus report as little as possible using glogin.sql or explicit instructions in the sql script to execute.
Then run is silently ( -s ) and act on teh output for example with a simple foreach or perhaps a read.

foreach name (`sqlplus -s "/ as sysdba" < test.sql`)
? echo test $name test
? end
test SYS test
test SYSTEM test
:

and feeding them back into an other sql task:

foreach name (`sqlplus -s "/ as sysdba" < test.sql`)
? echo "\ntest\n" $name
? echo "select * from dba_users where username = '$name'" | sqlplus -s "/ as sysdba"
? end
:

The script used for this was:


set heading off
set feedback off
select username from dba_users;


hth,
Hein.

[SteveKirby]

I think you already have a good answer above, but thought I'd add another one.

Just turn 'spool' on in sql.
Run your query
and sed/awk/grep/cut what you need from the spooled output.

It is dirty, but it works in any situation.

Steve