By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,421 Members | 1,244 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,421 IT Pros & Developers. It's quick & easy.

sql output in UNIX shell

P: 55
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_return_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_return_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.
Sep 20 '07 #1
Share this Question
Share on Google+
3 Replies

radoulov
P: 34
Expand|Select|Wrap|Line Numbers
  1. ERRFLAG="$(sqlplus -s <<!
  2. "$LOGIN"/"$DB_PWD"
  3. set pages 0 feed off serveroutput on
  4. begin
  5. procedure_return_error(ERRFLAG);
  6. dbms_output.put_line(ERRFLAG); // here you get the value
  7. end ;
  8. /
  9. !
  10. )"
  11.  
  12. echo "$ERRFLAG"
Sep 21 '07 #2

P: 55
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_OUT_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.
Sep 21 '07 #3

radoulov
P: 34
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_OUT_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 ....
Sep 22 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.