asked on

Getting multiple output values back from SqlPlus call

I am using Korn Shell scriptiing. Can somebody show me the syntax to return multiple parameters from a call to SqlPlus? Let's say I execute a function in SqlPlus and would like to return more than 1 parameters. How do I do it? Here is an example syntax to return 1 output parameter.

vcSqlResults=`sqlplus @MyProc.sql'

but vcSqlResults would have only 1 parameter and I want more than 1.

Thank you very much.

shivsa

example, only this time we will read multiple values from SQL*Plus into shell variables.
      sqlplus -s /nolog |& # Open a pipe to SQL*Plus

      print -p -- 'connect user/password@instance'
      print -p -- 'set feed off pause off pages 0 head off veri off line 500'
      print -p -- 'set term off time off'
      print -p -- "set sqlprompt ''"

      print -p -- "select sysdate from dual;"
      read -p SYSDATE

      print -p -- "select user from dual;"
      read -p USER

      print -p -- "select global_name from global_name;"
      read -p GLOBAL_NAME

      print -p -- exit

      echo SYSDATE: $SYSDATE
      echo USER: $USER
      echo GLOBAL_NAME: $GLOBAL_NAME

Note: In all these examples we use the -s or -silent option to suppress SQL*Plus banners. The /nolog option indicates that the script will login to the database. This prevents Unix from displaying your userid and password in the Unix process list (ps -ef).

http://www.orafaq.com/faqunix.htm

mfarid2

ASKER

Thank you shivsa for your response. Unfortunately, it doesn't answer my question. My Sql Plus call to a function will multiple parameters. So your method simply will not work. You can have arrays in Korn Shell. We need some way of retrieving these parameters in either in array or some other way.

ahoffmann

> vcSqlResults=`sqlplus @MyProc.sql'
is wrong syntax, should be:
vcSqlResults=`sqlplus @MyProc.sql`

Also, could please give an example what you receive in $vcSqlResults
and what you expect

HamdyHassan

I am also interested to see the source of this MyProc.sql or at least the part that returns more than one value

One idea,
if your final select will be something like
select f1, f2 , f3 from table

you could do
select f1 + "|" + f2 + "|" + f3 from table ( Not sure about syntax to concatenate in oracle )

this select will return one value, then at Unix you could split it as you want

cut -f1 -d"|"
cut -f2 -d"|"
etc...

ASKER CERTIFIED SOLUTION

glassd

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

liddler

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

Answered by glassd

Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

liddler
EE Cleanup Volunteer