Connecting sqlplus from UNIX with multiple select statement

Shell Programming and Scripting
1

hi,
i have a requirement where i need to connect sqlplus from unix and i am able to do so by following command:

cust_count=`sqlplus -s $ORACLE_USER/$ORACLE_PASS@$ORACLE_SID   << EOF
                set pagesize 0
                set feedback off
                set verify off
                set heading off
                select count(*) from BCHISTEVENT b, bcuser u
                where b.type != 'com.avolent.apps.event.LogoutEvent'
                and b.actinguserid = u.identifier
                and u.type = 'CustomerView'
                /*and (to_char(b.createdt, 'dd/mm/yy hh24:mi:ss') between
                to_char((SYSTIMESTAMP - INTERVAL '30' MINUTE),'dd/mm/yy hh24:mi:ss') and to_char(SYSTIMESTAMP,'dd/mm/yy hh24:
mi:ss'))*/
                AND b.bucket = to_char(sysdate, 'YYYYMM')
                group by u.type;
EOF`

but i have one more select statement here and if i am using the same above command then i am getting no output while echoing the variabe value:

biller_count=`sqlplus -s $ORACLE_USER/$ORACLE_PASS@$ORACLE_SID   << EOF
                set pagesize 0
                set feedback off
                set verify off
                set heading off
                select count(*) from BCHISTEVENT b, bcuser u
                where b.type != 'com.avolent.apps.event.LogoutEvent'
                and b.actinguserid = u.identifier
                and u.type = ' BillerAdminView'
                /*and (to_char(b.createdt, 'dd/mm/yy hh24:mi:ss') between
                to_char((SYSTIMESTAMP - INTERVAL '30' MINUTE),'dd/mm/yy hh24:mi:ss') and to_char(SYSTIMESTAMP,'dd/mm/yy hh24:
mi:ss'))*/
                AND b.bucket = to_char(sysdate, 'YYYYMM')
                group by u.type;
EOF`


echo "Total # of users Logged in @ last 30 minutes . Customer View    :$cust_count">>vmstat.txt;
echo "Total # of users Logged in @ last 30 minutes . Biller View      :$biller_count">>vmstat.txt;

plss suggest how to acheive this....thanks in advance

2

When you put a multi-line comment in sql always make sure that /* is followed by a space or newline else you will have unexpected results.

So it should be:

/* and (to_char(b.createdt, 'dd/mm/yy hh24:mi:ss') between

OR

/*
and (to_char(b.createdt, 'dd/mm/yy hh24:mi:ss') between