I need help in the following script. I want to grep the sql errors insert into the error table and exit the shell script if there is any error, otherwise keep running the scripts.
Here is my script
#!/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
Thanks!
I am wary of writing shell scripts in csh, so I shall show a simple error handling mechanism in bash.
$
$ # 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
$
$
And here's the testcase for a successful run.
$
$
$ # 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
$
$
HTH,
tyler_durden
Thanks durden_tyler
It did worked I just can't use any other shell. I am restrick to C-shell.
After little syntex code it grep the error.
99