10

I am connecting to oracle database and firing a query and assigning the output to variable But when I echo the value of the variable it doesn't get printed correctly.

count=`sqlplus -s $configuser/$configpass@$ORACLE_SID <<END
           set pagesize 0 feedback off verify off heading off echo off;
           select max(cust_id) from cutomers
           exit;
           END`
           echo $count

The query return correct result when fired on database. But "count" variable has incorrect value.

  • 1
    please check whether the indenting matches your original code, now after I fixed the formatting. (If matches, then it's wrong. You can not indent the here-documents' closing delimiter like that.) – manatwork Aug 1 '13 at 13:12
  • What is shown in normal mode, and what's the value of count when assigning? – ott-- Aug 1 '13 at 13:49
12

The terminating here-doc word must be the only characters on the line: no indenting allowed. Also, use $() instead of backticks -- they are nestable.

count=$(sqlplus -s $configuser/$configpass@$ORACLE_SID <<END
       set pagesize 0 feedback off verify off heading off echo off;
       select max(cust_id) from cutomers
       exit;
END
)
echo $count

http://www.gnu.org/software/bash/manual/bashref.html#Here-Documents

  • 2
    You can also use tabs with the <<-END notation. Not spaces, TABS! – slm Aug 1 '13 at 13:55
1
OUTPUT=$( ${OracleClientHome}/sqlplus -S user/pass@database <<EOF
set pagesize 0 linesize 32767 feedback off verify off heading off echo off
select a1.id  || '|' ||
       a1.stmt_begin_date  || '|' ||
       a1.stmt_end_date  || '|' ||
       a1.status  || '|' ||
       a1.total_recs 
from acct_stmt_file a1
 where a1.actp_cd = 'HSA'
   and a1.stmt_begin_date =trunc(sysdate);
exit;
EOF
)

echo -e "\n${OUTPUT}"
  • 1
    Why? I think it would be better if you shared your ideas with the community, too, not only this script snippet. – peterh May 26 '15 at 6:18

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.