Shell Programming and Scripting

Hi all,

I have below code

Code:

sqlplus -s ext/exo@TIS  << EOF
whenever sqlerror exit failure rollback;
set echo off
set head off
set serveroutput on
set termout on
set trimspool on
SPOOL $SPOOL_FILE
select 'ROWS '|| ' '||decode((count(Part_no)),0,'With greater values not found',count(part_no)) from log
/
spool off
exit
EOF
ROWS=`cat ${SPOOL_FILE} | grep ROWS | awk '{print $2}' `
INFORMATION=`tr -cs 'A-Za-z' '\n' < /a/abc.lst | grep -c "INFORMATION"`
echo $INFORMATION

if [ "${ROWS}" -ne 0 ] ||  [ "${INFORMATION}" -ne 0 ]
    then
//rest of my code//

where I need to query the table if the output is 0 then I need to write the text to a spool file if not I need to add the number of rows to the spool file.once I completed the spool file I used to search the number in the spool inorder to run my rest of my query

I am getting error as With: 0403-009 The specified number is not valid for this command.

but my program is working .can somehelp me to avoid the above error

Thanks in advance

What are you expecting in the variable ROWS?
If you are expecting a number signifying the count of your selected records, then that is not being assigned to the variable.

Hi

Thanks for the response.

I would like to Count the number of rows in the log table.if the count is greater than zero then I need to run certain Part of the code if not another part of the code apart from that again If I found the count = 0 then I need to write the text " ROWS With greater values not found" in the spool file because I am using this spool file output in a mail. SO what I did is I have added the output of the query to the variable rows. the query is working if I found any number after the variable row. getting any error is the query is returning zero bcoz i hope at that time in the spool file the text will be ROWS With greater values not found which the function

Code:

| awk '{print $2}

might throwing error

Thanks in advance

To me, the err msg seems to be SQL originated. Pls post result of above query submitted interactively.

This is a bit dubious. Could work if spool file contains literal string "ROWS" in all uppercase. Try using

Code:

ROWS=$(awk '/Rows|ROWS/  {print $2}' "$SPOOL_FILE")