...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. ...
I am wary of writing shell scripts in csh, so I shall show a simple error handling mechanism in bash.
Code:
$
$ # 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.
Code:
$
$
$ # 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
Last edited by durden_tyler; 05-20-2010 at 12:01 AM..
I have a shell script that calls another shell script "str_process_main" that runs in a loop until a given time. I want the first script to just call the second one and then exit. The first script is:
#!/bin/ksh
DATE=$(date +%m%d%y)
DPID=$(ps -ef|grep str_process_main|grep -v grep)
if ;... (2 Replies)
Hi-
I am trying to achieve the following in a script so I can schedule it on a cron job. I am fairly new to the unix environment...
I have written a shell script that reads a flat file and loads the data into an Oracle table (Table1) via SQLLDR. This Works fine. Then, I run a nested insert... (5 Replies)
Hi I am getting this error while connecting to sql through a shell script, whereas i am able to connect to sql directly.
It was working properly earlier, no clue why i am getting this.
Please find the log below:
FTP to <IP> completed Wed Apr 30 11:42:01 BST 2008
Program ended. Wed Apr 30... (1 Reply)
Hi,
I need help urgently for following issue. Pls help me to resolve this issue.
I am calling sql script file(file1.sql) from UNIX Shell Script(script1.ksh) using sql plus and trying to create flat file that contains all records returned from SQL query in SQL script(file1.sql)
I given... (6 Replies)
Hi.
I am trying to extract the output of the first few lines of a continuos sh script.
The when i run it, i wont to grep the the first 20 lines of it for an entry and basically do a crtl z out of it or to that effect, and output the results to a text file.
I basically want to script... (5 Replies)
There are 4 parameters that I have to pass from korn shell to sql script.
1) I have to check if $1 , $2 , $3 and $4 are null values or not .
How can I do that ?
2) Once its determined that these values are null (in the sense they are empty) how can I pass null values to sql script... (11 Replies)
hi,
i want to pop up an alert box using perl script. my requirement is.
i am using a html page which calls a perl script. this perl script calls a shell script.. after the shell script ends its execution, i am using exit 0 to terminate the shell script successfully and exit 1 to terminate the... (3 Replies)
Hi all,
$ echo $SHELL
/bin/bash
Requirement - How to pass oracle sql script as argument to unix shell script?
$ ./output.sh users.sql
Below are the shell scripts and the oracle sql file in the same folder.
Shell Script
$ cat output.sh
#!/bin/bash
.... (7 Replies)
Hi Guys,
I am calling a SQL script which runs under while loop, i need to exit the while loop if i get error in sql script which is called
while
do
sqlplus -s user/pass@db @test.sql id$i
done
test.sql
whenever sqlerror exit;
alter table t1 add &1 number;
I need to come out of... (2 Replies)
Hi All
when I execute from psql prompt, I get the result, when I try to automate
using a shell script, the query is not working
# `/usr/bin/psql -U postgres -d coba1 -c "select name from users where
"Date" > current_date - 30;"`
ERROR: column "Date" does not exist
LINE 1: select... (2 Replies)