0

I am getting some issue when running sql from sqlplus and want to assign the o/p to a Unix variable.The Output is showing correct in log file but not assign to variable. code :

PASSWD variable conatins user/password/DB details.

STATUS=`sqlplus -s /nolog <<EOF>> /home/test_db.log
        CONNECT ${PASSWD}
WHENEVER OSERROR EXIT FAILURE
WHENEVER SQLERROR EXIT SQL.SQLCODE
set termout off
set showmode off
set heading off
set echo off
set timing off
set time off
set feedback 0
set pagesize 0
set embedded ON
set verify OFF
select status from int_control_tab where rec_no=877 and process_date=to_date('15/02/2017','dd/mm/yyyy');
exit;
EOF`

echo "STATUS :" ${STATUS}

Even i am getting o/p i.e 1 value in log file but not assigning value to STATUS variable. Please suggest.

  • You're redirecting the output to /home/test_db.log. If you want it in the variable as well, use |tee -a /home/test_db.log instead of >>/home/test_db.log. If you want the exit status. It's in $?. And remember to quote your variables! – Stéphane Chazelas Feb 24 '17 at 16:58
2

Since you are redirecting the output form the command into a file, there is no ouput to put into the variable.

You may use the tee utility to fix this:

STATUS=$( sqlplus ... <<EOF | tee -a logfile
...
EOF
)

tee will duplicate its input to all the named files, as well as to its own standard output (which will be inserted into you variable).

Also, please double quote the password as "$PASSWD" or you will have all sorts of issues if the password contains special characters. The same goes for "$STATUS" when you echo it.

  • Sure, I just did same and i got expected output now...Thank You very much 'Kusalananda' – user212332 Feb 24 '17 at 17:14
  • You are most welcome, but please don't quote my name, it's actually a given name :-) If this solves your issue, then please consider accepting the answer. – Kusalananda Feb 24 '17 at 17:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.