Shell Programming and Scripting

#!/bin/csh -f
source .orapass
set user = $USER
set pass = $PASS

cd /opt/data/scripts
echo "Creating Type A File ..."
./filename.pl

$ORACLE_HOME/bin/sqlplus $user/$pass@cmd <<LINE;
truncate table DUP_CONTACTS_SUM
/
truncate table DUP_CONTACTS_TMP
/
exit
LINE

$ 
$ # check the structure of the error table in test schema
$ echo "desc error_table" | sqlplus -s test/test
 Name                       Null?    Type
 ----------------------------------------- -------- ----------------------------
 CODE                            NUMBER
 MESSAGE                        VARCHAR2(100)

$ 
$ # check the structure of table T, which we'll populate for this testcase
$ echo "desc t" | sqlplus -s test/test
 Name                       Null?    Type
 ----------------------------------------- -------- ----------------------------
 X                            NUMBER

$ 
$ # we'll try to insert a character in that NUMBER column T.X
$ # display the content of the shell script; hopefully the inline comments are clear enough
$ 
$ cat -n errtest.sh
     1    #!/bin/bash
     2    
     3    sqlplus -s /nolog <<EOF
     4    connect test/test
     5    -- the WHENEVER statement forces an exit from sqlplus when an exception is thrown
     6    whenever sqlerror exit 9
     7    -- the anonymous PL/SQL block that houses all SQL statements to be run
     8    declare
     9      -- local variables to hold error code and message
    10      n_code  number;
    11      v_mesg  varchar2(100);
    12    begin
    13      -- put all your SQL statements in this BEGIN section
    14      insert into t (x) values (1);
    15      -- let's try to insert a character in a NUMBER column
    16      insert into t (x) values ('A');
    17      insert into t (x) values (3);
    18      commit;
    19    exception
    20      when others then
    21        -- something went wrong; insert into ERROR_TABLE
    22        -- you can't put sqlcode and sqlerrm in the INSERT statement directly
    23        n_code := SQLCODE;
    24        v_mesg := SQLERRM;
    25        insert into error_table (code, message) values (n_code, substr(v_mesg,1,100));
    26        -- if this is a concern then try *anonymous transactions*
    27        commit;
    28        -- VERY, VERY IMPORTANT to raise the exception
    29        raise;
    30    end;
    31    /
    32    exit;
    33    EOF
    34    
    35    # capture the return value from the sqlplus session
    36    retval=$?
    37    if [ $retval != 0 ]; then
    38      echo "Oracle script threw an exception and returned exit status => $retval."
    39      echo "Check ERROR_TABLE for the sqlcode and sqlerrm. Abnormal exit from shell script."
    40      return 1
    41    fi
    42    
    43    # if we are here, then all went well with Oracle... carry on... 
    44    echo "The Oracle SQL statements executed successfully. Continuing with the rest of shell script..."
    45    
$ 
$ 
$ # now source the shell script
$ 
$ . errtest.sh
declare
*
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at line 22


Oracle script threw an exception and returned exit status => 9.
Check ERROR_TABLE for the sqlcode and sqlerrm. Abnormal exit from shell script.
$ 
$ # check the exit status of the shell script
$ echo $?
1
$ 
$ # check the error message in the table ERROR_TABLE of test schema
$ 
$ echo "select * from error_table;" | sqlplus -s test/test

      CODE
----------
MESSAGE
--------------------------------------------------------------------------------
     -1722
ORA-01722: invalid number

$ 
$ 

$ 
$ 
$ # display the content of the shell script
$ # all Oracle SQL statements are expected to succeed now
$ 
$ cat -n errtest.sh
     1    #!/bin/bash
     2    
     3    sqlplus -s /nolog <<EOF
     4    connect test/test
     5    -- the WHENEVER statement forces an exit from sqlplus when an exception is thrown
     6    whenever sqlerror exit 9
     7    -- the anonymous PL/SQL block that houses all SQL statements to be run
     8    declare
     9      -- local variables to hold error code and message
    10      n_code  number;
    11      v_mesg  varchar2(100);
    12    begin
    13      -- put all your SQL statements in this BEGIN section
    14      -- all the following 3 statements should succeed
    15      insert into t (x) values (1);
    16      insert into t (x) values (2);
    17      insert into t (x) values (3);
    18      commit;
    19    exception
    20      when others then
    21        -- something went wrong; insert into ERROR_TABLE
    22        -- you can't put sqlcode and sqlerrm in the INSERT statement directly
    23        n_code := SQLCODE;
    24        v_mesg := SQLERRM;
    25        insert into error_table (code, message) values (n_code, substr(v_mesg,1,100));
    26        -- if this is a concern then try *anonymous transactions*
    27        commit;
    28        -- VERY, VERY IMPORTANT to raise the exception
    29        raise;
    30    end;
    31    /
    32    exit;
    33    EOF
    34    
    35    # capture the return value from the sqlplus session
    36    retval=$?
    37    if [ $retval != 0 ]; then
    38      echo "Oracle script threw an exception and returned exit status => $retval."
    39      echo "Check ERROR_TABLE for the sqlcode and sqlerrm. Abnormal exit from shell script."
    40      return 1
    41    fi
    42    
    43    # if we are here, then all went well with Oracle... carry on... 
    44    echo "The Oracle SQL statements executed successfully. Continuing with the rest of shell script..."
    45    
$ 
$ # now source the shell script
$ 
$ . errtest.sh

PL/SQL procedure successfully completed.

The Oracle SQL statements executed successfully. Continuing with the rest of shell script...
$ 
$ # check the exit status of the shell script
$ echo $?
0
$ 
$