how to capture oracle function returning 2 values in unix

Shell Programming and Scripting
1

i have an oracle function which returns two values, one is the error message if the function encounters anything and another one which returns a number

i need to capture both
and pass it on to unix shell script

how to do it

2

Do you mean an Oracle procedure ?
An Oracle function can return only a single value to the caller. The return value is strongly typed via the RETURN keyword, which means you can only return a number.
The error message you are talking about is thrown in case of an exception. It is not a return value.

If you leave out the exception section from the function, then maybe something like this -

$
$ cat -n get_tvalue.sh
     1  #!/usr/bin/bash
     2  x=$(sqlplus -s /nolog <<EOF
     3  connect <login_credentials>
     4  set pages 0 feedback off
     5  var n varchar2(10)
     6  exec :n := <your_function>;
     7  print n
     8  exit
     9  EOF)
    10  echo "x => $x"
    11
$
$

should assign the returned number or the error message to x.
Here <login_credentials> are your Oracle login credentials in the form username/password@SID and
<your_function> is the invocation of your function that returns a number.

HTH,
tyler_durden

3

As Durden indicates functions typically return a value. Even in languages such as Perl which can return arrays and lists they still return these values as a single entity.

With that said I'd like to ask a question. Do you know if the error value comes back from STDERR because the Oracle command fails?

Rather than waiting for an answer I'll assume the above to be the case where an error message is written to STDERR and the command returns a non-zero value as status. Here's some test code. The first script, 'test9a.sh', calls a second script, 'test9b.sh', which can return an error. The error is returned if you specify 'error' as the first parameter to the first script.

#!/bin/bash
# script test9a.sh

sfile=$(basename $0)

#echo "$sfile: start"
#echo "$sfile: param: $1"

err=""
rval=$(./test9b.sh $1 2>&1 )
rcode=$?
if [ $rcode -ne 0 ]; then 
    err="$rval"
    rval=""
fi
echo "$sfile: rcode=$rcode, rval=\"$rval\", err=\"$err\""

#!/bin/bash
#script test9b.sh

sfile=$(basename $0)

#echo "$sfile: start"
#echo "$sfile: param: $1"
if [ "$1" == "error" ]; then
    echo "$sfile: Error message" >&2
    exit 1
fi
echo "$sfile: Return Value $1"
exit 0

Try it out to see if these scripts emulate your situation.