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.
mfarid2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

shivsaCommented:
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
0
mfarid2Author Commented:
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.
0
ahoffmannCommented:
> 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
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

HamdyHassanCommented:
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...



   
0
glassdCommented:
One way:

sqlplus @MyProc.sql | read var1 var2 var3

echo $var1
echo $var2
echo $var3


Or:

set `sqlplus @MyProc.sql`

while [[ $# -gt 0 ]]
do
   echo $1
   shift
done
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
liddlerCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
System Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.