checking if a variable value is null on bash script

Question

i have this script that retrieves a parameter (a number) from a SQL query and assigns it to a variable. there are two options - either the SQL query finds a value and then the script preforms echo "the billcycle number is $v_bc", or it doesnt find a value and it suppose to echo "no billcycle parameter found". im having a problem with the if condition.

this is what i came up with:

#!/bin/bash


v_bc=`sqlplus -s /@bscsprod <<EOF
set pagesize 0
select billcycle from bc_run
where billcycle not in (50,16)
and control_group_ind is null
and billseqno=6043;
EOF`


if [ -z "$v_bc" ]; then echo no billcycle parameter found                     
else echo "the billcycle parameter is $v_bc"
fi

when billseqno=6043, then it means that v_bc=25, and when i run the script, the result is: "the billcycle parameter is 25". which is what i ment it to do. when i set billseqno=6042, according to the above SQL query, v_bc will get no value, therefore what i want it to do is echo "no billcycle parameter found". instead i get

"the billcycle parameter is

no rows were selected".

any suggestions ?

thanks very much Assaf.

score 1 · Answer 1 · edited May 23 '17 at 11:58

Your code is correctly checking for an empty value -- v_bc is just not empty, even with -s.

You may either:

parse the output of sqplus when no rows are returned, so add this:

if [[ "$v_bc" == "no rows were selected" ]]; then v_bc=""; fi

This uses the bash [[ ]] command with "==" for pattern matching so we don't need to worry about leading/trailing whitespace. This is not as robust as dogbane's SET FEEDBACK OFF since it's entirely possible for "no rows were selected" to be valid data.
write a better query which always returns data, like this: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:594023455752#followup-221571500346463844

The trick being to reformulate your query and use select/union with a fallback query that conditionally provides output when your query is empty:

with data as
  (select billcycle from bc_run where [...])
select * from data 
union all
select 'NA', null from dual where not exists (select null from data);

(see also What is the dual table in Oracle? )

score 0 · Answer 2 · answered Jan 29 '13 at 14:04

0

Try something like

if [ "${v_bc:-SHOULDNTHAPPEN}" = "SHOULDNTHAPPEN" ]; then
  echo no billcycle parameter found
else......

answered Jan 29 '13 at 14:04

VB9-UANIC

330
1
5

Hello. thanks very much for your reply.the code you suggested does give the same result as if [ -z "$v_bc" ]; then echo no billcycle parameter found. would you mind explaining what it does ? didnt really get the SHOULDNTHAPPEN part (: – Shlomix Jan 31 '13 at 08:04

dogbane · Accepted Answer · 2013-01-29T15:08:58.563

0

Try turning feedback off in sqlplus so that you don't get any output if no rows are selected:

v_bc=`sqlplus -s /@bscsprod <<EOF
SET FEEDBACK OFF
set pagesize 0
select billcycle from bc_run
where billcycle not in (50,16)
and control_group_ind is null
and billseqno=6043;
EOF`

edited Jan 29 '13 at 15:08

answered Jan 29 '13 at 14:20

dogbane

266,786
75
396
414

score 0 · Answer 4 · answered Jan 29 '13 at 16:23

The idiomatic way to assign a value to a variable if that variable is empty is with an = in a parameter expansion. In other words, this:

: ${v_bc:=no billcycle paramter found}

is equivalent to:

test -z "$v_bc" && v_bc='no billcycle paramter found'

In your case, it would also be easy to do:

echo ${v_bc:-no billcycle parameter found}

but the question asked in the title is not really the problem in your case. (Since the problem is not that v_bc is the empty string, but rather that it is a value you do not expect.)

checking if a variable value is null on bash script

4 Answers4