Catch Sql Error In Shell Script
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about
Whenever Sqlerror Exit Sql Sqlcode Shell Script
Stack Overflow the company Business Learn more about hiring developers or posting ads with shell script sql query execute us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is shell script sql output to variable a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Managing error handling while running sqlplus from shell scripts up vote 8 down vote
Sql Shell Script Example
favorite 3 #!/bin/sh echo "Please enter evaluate database username" read eval_user echo "Please enter evaluate database password" read eval_pass echo "Please enter the database name" read db_name LOGFILE=shell_log.txt $ORACLE_HOME/bin/sqlplus -s /nolog <<-EOF>> ${LOGFILE} connect $eval_user/$eval_pass@$db_name WHENEVER OSERROR EXIT 9; WHENEVER SQLERROR EXIT SQL.SQLCODE; DBMS_OUTPUT.put_line('Connected to db'); EOF if [ $? != 0 ] then echo "The upgrade script failed. Please refer to the log results.txt for more information" echo "Error
Sql Query In Shell Script Unix
code $?" exit 0; fi I am entering garbage values trying to force this script to fail. But, annoyingly, it keeps moving ahead without any mention of any error code. What else needs to be done here? oracle shell sqlplus aix share|improve this question asked Feb 6 '13 at 16:35 roymustang86 1,926103573 Which OS user account are you running under? What database does the script login to? –APC Feb 11 '13 at 14:01 I tried your script, and the log file is capturing the error code. Here is the grab from the log file: ERROR: ORA-01017: invalid username/password; logon denied SP2-0734: unknown command beginning "DBMS_OUTPU..." - rest of line ignored. –Incognito Feb 12 '13 at 9:55 May I know, what're you getting in log file when you execute the script? –Incognito Feb 12 '13 at 11:18 add a comment| 5 Answers 5 active oldest votes up vote 7 down vote accepted +50 What Max says is correct. Try this modified script #!/bin/sh echo "Please enter evaluate database username" read eval_user echo "Please enter evaluate database password" read eval_pass echo "Please enter the database name" read db_name LOGFILE=shell_log.txt sqlplus -s /nolog <<-EOF>> ${LOGFILE} WHENEVER OSERROR EXIT 9; WHENEVER SQLERROR EXIT SQL.SQLCODE; connect $eval_user/$eval_pass@
Scripting Unix shell scripting - KSH, CSH, SH, BASH, PERL, PHP, SED, AWK and shell scripts and shell scripting languages here. Search Forums Show Threads Show Posts run sql from shell script Tag Search Advanced Search Unanswered Threads Find All Thanked Posts Go to
Shell Script Catch Exception
Page... unix and linux operating commands How to catch sql error in script? Shell Programming and Scripting shell script handle error Thread Tools Search this Thread Display Modes #1 03-14-2014 ken6503 Registered User Join Date: May 2013 Last Activity: 28 September 2016, 4:26 PM EDT http://stackoverflow.com/questions/14734131/managing-error-handling-while-running-sqlplus-from-shell-scripts Location: Toronto Posts: 181 Thanks: 152 Thanked 3 Times in 3 Posts How to catch sql error in script? Hi Gurus, I have a script which send sql query to oracle db and return value to my script. dummy code like below: Code: sqlplus -s user/${PASSWD}@${ORACLE_SID} @${DIR}/query.sql > outputfile using above code, when query has error, it send error to same out put file and http://www.unix.com/shell-programming-and-scripting/245224-how-catch-sql-error-script.html exit code is 0, is there any way to make the sqlplus exit code other than 0 if the query contains any error? thanks in advance Remove advertisements Sponsored Links ken6503 View Public Profile Find all posts by ken6503 #2 03-14-2014 shamrock Registered User Join Date: Oct 2007 Last Activity: 30 September 2016, 12:20 PM EDT Location: USA Posts: 1,594 Thanks: 34 Thanked 155 Times in 146 Posts Put this line whenever sqleror exit sql.sqlcode as the first line of your "query.sql" file... Remove advertisements Sponsored Links shamrock View Public Profile Find all posts by shamrock #3 03-14-2014 Ditto Registered User Join Date: Mar 2014 Last Activity: 30 September 2015, 11:58 AM EDT Location: Toronto, Canada Posts: 70 Thanks: 5 Thanked 10 Times in 10 Posts Quote: Originally Posted by shamrock Put this line whenever sqleror exit sql.sqlcode as the first line of your "query.sql" file... That won't quite work as expected: Code: > more gg.sql whenever sqlerror exit sql.sqlcode; select 1/0 from dual; exit > sqlplus -s myid@mydb @gg.sql Enter password: select 1/0 from dual * ERROR at line 1: ORA-0147
script [message #97631] Wed, 25 September 2002 06:00 Tara Messages: 4Registered: July 1999 Junior Member I have a shell script which calls a packaged procedure. In my shell script, how do I trap an error http://www.orafaq.com/forum/t/37022/ which has happened in the pl/sql program, so that I can take some actions in the shell script. At the moment, I have an exception handler in my pl/sql program which rolls back the changes. Thanks in advance, T. Report message to a moderator Re: how to trap pl/sql error in shell script [message #97632 is a reply to message #97631] Wed, 25 September 2002 21:13 K.K. Raj shell script kumar Messages: 33Registered: July 2002 Member Hello, You can use set serveroutput on size 50000 before calling the procedure or package and inside the procedure or package you can use dbms_output.put_line to trap the variables. Before calling the package you can spool the output so that the messages will come out. Report message to a moderator Re: how to trap pl/sql error in shell script [message #97634 is in shell script a reply to message #97631] Sun, 29 September 2002 07:54 Deborah Messages: 18Registered: June 2002 Junior Member It did not work out for me. But I use the following to catch the details of the error by redirecting into some file. sqlplus -s usename/password@connect << EOF >> Logfile.txt execute pl-sql_procedure_name exit EOF The above is in shell script and I get the message (success/ failure) in the Logfile.txt Debby Report message to a moderator Re: how to trap pl/sql error in shell script [message #97638 is a reply to message #97631] Mon, 30 September 2002 16:38 andrew again Messages: 2577Registered: March 2000 Senior Member See the solution at the following URL: http://www.oracle.com/oramag/oracle/01-may/index.html?o31asktom.html Report message to a moderator Re: how to trap pl/sql error in shell script [message #97640 is a reply to message #97638] Wed, 02 October 2002 14:18 andrew again Messages: 2577Registered: March 2000 Senior Member Note that some things don't generate errors - like connecting to Oracle. If user/pass is wrong (or maybe if Oracle is unavailable) then you don't get a pl/sql error. A good way of handling that is to spool to a dummy file as the last command in the script. When you return to the OS and your dumm