Assigning the output of a SQL query to variable

Question

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.

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
– manatwork, Commented Aug 1, 2013 at 13:12
What is shown in normal mode, and what's the value of count when assigning? — ott--
– ott--, Commented Aug 1, 2013 at 13:49

glenn jackman · Accepted Answer · 2013-08-01 13:51:41Z

13

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

answered Aug 1, 2013 at 13:51

glenn jackman

88.5k16 gold badges124 silver badges179 bronze badges

2

You can also use tabs with the <<-END notation. Not spaces, TABS!

slm
– slm ♦

2013-08-01 13:55:06 +00:00
Commented Aug 1, 2013 at 13:55

Add a comment |

peterh · Accepted Answer · 2015-05-26 06:34:33Z

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}"

edited May 26, 2015 at 6:34

peterh

10.5k18 gold badges70 silver badges99 bronze badges

answered May 26, 2015 at 6:09

Tathagata Mukherjee

111 bronze badge

1

Why? I think it would be better if you shared your ideas with the community, too, not only this script snippet.

peterh
– peterh

2015-05-26 06:18:19 +00:00
Commented May 26, 2015 at 6:18

Add a comment |

AdminBee · Accepted Answer · 2020-01-02 15:41:52Z

-1

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

You have to use the semicolon in right place.

edited Jan 2, 2020 at 15:41

AdminBee

23.6k25 gold badges55 silver badges77 bronze badges

answered Jan 2, 2020 at 13:24

Emaya

1

Also, the END at the end needs to be flush to the left, with no whitespace in front of it.

Kusalananda
– Kusalananda ♦

2020-01-02 19:58:16 +00:00
Commented Jan 2, 2020 at 19:58

Add a comment |

Stack Exchange Network

Assigning the output of a SQL query to variable

3 Answers 3

You must log in to answer this question.

Hot Network Questions

Assigning the output of a SQL query to variable

3 Answers 3

You must log in to answer this question.

Related

Hot Network Questions