Unable to retrieve data from shell when connected to Oracle

Shell Programming and Scripting
1

I am trying to get the data from oracle table to a variable I have tried the below code

#/usr/bin/sh
        echo " I am in correct loop"
        SPOOL_FILE=/app/scripts/alt.lst
      sqlplus /nolog  <<END_SQL
        connect bindu/bindu@gis
        whenever sqlerror exit failure rollback;
        set serveroutput on
        set termout on
        set trimspool on
        SPOOL $SPOOL_FILE
        select 'SQLCODE' ||'count(*) from altuomlog
        /
      spool off
     EXIT
     END_SQL
     echo " I am in correct loop"
      SQLCODE='cat ${SPOOL_FILE} | grep SQLCODE | awk '{print $2}''
   echo $SQLCODE
      unset EXIT_STATUS
       unset SQL_CODE
     echo "END.. I am in correct loop"

when I ran this code the it got exit from the oracle after completion of the select statement the rest of the shell script is not executed.if I removed the exit statement the in that case the shell is throwing error.

Please advice..

Thanks in advance..

2

dont give any spaces for the line where you used

END_SQL

it should be stick to the left side of the editor..

1 Like
3

I am able to run the shell.very much appreciated

Could you please let me know what was the error in the below line:

 SQLCODE='cat $SPOOL_FILE | grep SQLCODE | awk -F    '{print $2}' '

Thanks in advance..

4

You have it in single quotes, not backticks, therefore you're storing it as a literal string, and not actually running it.

Also, that's a useless use of cat

1 Like
5

Hi

I want to send the output of a spool file through mail.FYI..the spool file consists of output of a select comand with only 3 columns.The mail should be sent to a list of people .I didn't have a good knowledge on Shell what I can see is that the list of people where added to a variable list and called the mailx command.

Can some one please send me the code how to do this by taking above two consideration

Thanks in Advance