How to grep sql error in shell script and exit the script?


Login or Register for Dates, Times and to Reply

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How to grep sql error in shell script and exit the script?
# 1  
How to grep sql error in shell script and exit the script?

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
Code:
#!/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!

Last edited by Scott; 05-19-2010 at 08:28 PM.. Reason: Please use code tags
# 2  
Quote:
Originally Posted by allinshell99
...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..
# 3  
It worked

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
Login or Register for Dates, Times and to Reply

Previous Thread | Next Thread
Thread Tools Search this Thread
Search this Thread:
Advanced Search

Test Your Knowledge in Computers #108
Difficulty: Easy
The outer layers of the Linux architecture has the shell, shell commands and application programs.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Shell script with sql script error

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)
Discussion started by: srilinux09
2 Replies

2. UNIX for Advanced & Expert Users

Exit when sql script give error

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)
Discussion started by: rohit_shinez
2 Replies

3. Shell Programming and Scripting

How to pass Oracle sql script as argument to UNIX shell script?

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)
Discussion started by: a1_win
7 Replies

4. Shell Programming and Scripting

How to capture the exit code of a shell script in a perl script.?

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)
Discussion started by: Little
3 Replies

5. Shell Programming and Scripting

pass null value to sql script from korn shell script

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)
Discussion started by: megha2525
11 Replies

6. UNIX for Dummies Questions & Answers

trying to grep the first few lines of a continuos script, and exit the script anyidea

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)
Discussion started by: k00061804
5 Replies

7. Shell Programming and Scripting

(Urgent):Creating flat file using sql script and sqlplus from UNIX Shell Script

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)
Discussion started by: praka
6 Replies

8. Shell Programming and Scripting

error connecting sql through a shell script

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)
Discussion started by: nehak
1 Replies

9. Shell Programming and Scripting

Calling SQL LDR and SQL plus scripts in a shell script

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)
Discussion started by: rajagavini
5 Replies

10. Shell Programming and Scripting

Have a shell script call another shell script and exit

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)
Discussion started by: heprox
2 Replies

Featured Tech Videos