sql output in UNIX shell

[eeriehunk]

Hi,

I wrote a shell script where I connect to an oracle db(the connection works because I able to see the DBMS output) and I execute a procedure, but I am not able to get the return value from that procedure into one of the shell variables (i understand that procedure do not return values but i am using IN OUT and i want this out value to go into a variable declared in the shell). I made sure the procedure works in sqlplus and the OUT value is comming out. Below is the code.

LOGIN=user1
DB_PWD=`some passwork`

sqlplus -s <<!
${LOGIN}/${DB_PWD}
set head off;
set feed off;
set pages 0;
set verify off;
set feedback off;
set serveroutput on;

declare
ERRFLAG number := 0;
begin
procedure_retur n_error(ERRFLAG );
dbms_output.put _line(ERRFLAG); /* the value gets displayed here which means the sql connection works and the OUT value is comming through */
end ;
/
!
echo errorflag is ${ERRFLAG} /* the value does not come here and dont mind the ${ERRFLAG} it does not work with just $ERRFLAG either */
echo ${APPS_LOGIN}
exit 0

I also tried declaring a global variable like below:

LOGIN=user1
DB_PWD=`some passwork`
ERRFLAG = 0 // like this but this does not work either

sqlplus -s <<!
${LOGIN}/${DB_PWD}
set head off;
set feed off;
set pages 0;
set verify off;
set feedback off;
set serveroutput on;

begin
procedure_retur n_error(ERRFLAG );
dbms_output.put _line(ERRFLAG); // the value gets displayed here
end ;
/
!
echo errorflag is ${ERRFLAG} // the value does not come here
echo ${APPS_LOGIN}
exit 0

Please reply,
thank you.

[radoulov]

ERRFLAG="$(sqlplus -s <<!
"$LOGIN"/"$DB_PWD"
set pages 0 feed off serveroutput on
begin
procedure_return_error(ERRFLAG);
dbms_output.put_line(ERRFLAG); // here you get the value
end ;
/
!
)"

echo "$ERRFLAG"

[eeriehunk]

Thank you for your reply.
As I said earlier, I am using a procedure and not a function. So technically there is no return value that comes out of the sql block to go into the UNIX variable. The value falls into the parameter of the procedure. (ie. EPALS_GL_NREG_O UT_INT(PARAM); the value falls into the parameter PARAM). so i am looking for a way to assign the value of PARAM to a UNIX variable outside the sql block.

[radoulov]

Thank you for your reply.
As I said earlier, I am using a procedure and not a function. So technically there is no return value that comes out of the sql block to go into the UNIX variable. The value falls into the parameter of the procedure. (ie. EPALS_GL_NREG_O UT_INT(PARAM); the value falls into the parameter PARAM). so i am looking for a way to assign the value of PARAM to a UNIX variable outside the sql block.

Yes,
I and as I said, you have to print it(modify your PL/SQL code or write a wrapper that prints it).
AFAIK there is no other way to pass it to the shell ....