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

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

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).
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.
> 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
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

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"|"

One way:

sqlplus @MyProc.sql | read var1 var2 var3

echo $var1
echo $var2
echo $var3


set `sqlplus @MyProc.sql`

while [[ $# -gt 0 ]]
   echo $1

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


EE Cleanup Volunteer
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.