Oracle Sqlplus Script Error Handling
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta sqlplus whenever Discuss the workings and policies of this site About Us Learn sqlplus error handling more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us sqlplus exit code Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like whenever sqlerror exit 1 shell script you, helping each other. Join them; it only takes a minute: Sign up Managing error handling while running sqlplus from shell scripts up vote 9 down vote favorite 4 #!/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
Sqlplus Whenever Oserror
/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 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,941113573 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 getti
class="underline">COMMIT | ROLLBACK] | CONTINUE [COMMIT | ROLLBACK | NONE]} Performs the specified action (exits SQL*Plus by default) if a SQL command or PL/SQL block generates an error. In iSQL*Plus, performs the specified action (stops the current script by
Whenever Sqlerror Exit Sql.sqlcode Shell Script
default) and returns focus to the Workspace if a SQL command or sqlplus error handling in unix PL/SQL block generates an error. Terms [SUCCESS | FAILURE | WARNING | n | variable | :BindVariable] Directs SQL*Plus capture sqlplus error in shell script to perform the specified action as soon as it detects a SQL command or PL/SQL block error (but after printing the error message). SQL*Plus will not exit on a SQL*Plus error. http://stackoverflow.com/questions/14734131/managing-error-handling-while-running-sqlplus-from-shell-scripts EXIT [SUCCESS | FAILURE | WARNING | n | variable | :BindVariable] Directs SQL*Plus to exit as soon as it detects a SQL command or PL/SQL block error (but after printing the error message). SQL*Plus will not exit on a SQL*Plus error. The EXIT clause of WHENEVER SQLERROR follows the same syntax as the EXIT command. See EXIT for more information. CONTINUE https://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12052.htm Turns off the EXIT option. COMMIT Directs SQL*Plus to execute a COMMIT before exiting or continuing and save pending changes to the database. ROLLBACK Directs SQL*Plus to execute a ROLLBACK before exiting or continuing and abandon pending changes to the database. NONE Directs SQL*Plus to take no action before continuing. Usage The WHENEVER SQLERROR command is triggered by SQL command or PL/SQL block errors, and not by SQL*Plus command errors. Examples The commands in the following script cause iSQL*Plus to stop processing the current script and return focus to the Input area on the Workspace if the SQL UPDATE command fails: The commands in the following script cause SQL*Plus to exit and return the SQL error code if the SQL UPDATE command fails: WHENEVER SQLERROR EXIT SQL.SQLCODE UPDATE EMP_DETAILS_VIEW SET SALARY = SALARY*1.1; The following SQL command error causes iSQL*Plus to stop processing the current script and return focus to the Input area on the Workspace if the SELECT command fails: WHENEVER SQLERROR EXIT SQL.SQLCODE select column_does_not_exiSt from dual; select column_does_not_exist from dual * ERROR at line 1: ORA-00904: invalid column name Disconnected from Oracle.
CommunityOracle User Group CommunityTopliners CommunityOTN Speaker BureauJava CommunityError: You don't have JavaScript enabled. This tool uses JavaScript and much of it will not work correctly without it enabled. Please turn JavaScript back on and reload this page. Please enter a title. You can not post a blank message. https://community.oracle.com/thread/2436180 Please type your message and try again. More discussions in PL/SQL and SQL All PlacesDatabaseDatabase Application DevelopmentPL/SQL and SQL This discussion is archived 3 Replies Latest reply on Sep 5, 2012 8:29 AM by user5647282 Oracle Error Handling in Shell Scripts user5647282 Aug 31, 2012 8:04 AM I need to manage 2 diferente class of errors : Oracle Errors(produced in compilation time) and Operating Syste Error(e.g. No Datbase conection error handling ORA-1017,etc) my shell its KSH. Please can you help me how can I manage then? this my alternative but is not correct ; #creating conexion with sql exit | sqlplus -s $USERPV_DB/$PWDPV_DB @$VORDSQLPATH/ord.extractor_porven.sql $VFDESDE $VFHASTA > $VORDDATOS_PATH/ord.extractor_porven$VDATE.dat 2>> $VLOG #Evaluating last sentence (sqlplus . . . . ) VERROR=$? #Si VERROR=0 should stop process execution and alert with echo ".."Oracle error handling before compilation time if [ sqlplus error handling $VERROR != 0 ] then echo "value of VERROR are:$VERROR" echo "`date +"$V_FORMATDATE"` DATA EXTRACTION WAS NOT SUCESSFUL ERROR BEFORE COMPILATION TIME" >> $VLOG 2> /dev/null " Here show VERROR else #Oracle error handling in compilation time echo "`date +"$V_FORMATDATE"` DATA EXTRACTION WAS NOT SUCESSFUL ERROR BEFORE COMPILATION TIME " >> $VLOG " Here show VERROR else if [ $VERROR = 0 ] " DATA EXTRACTION WAS SUCESSFUL" fi ----------------- Would apreciate your help its very urgent. Best Regards Antonio I have the same question Show 0 Likes(0) 386Views Tags: none (add) errorContent tagged with error, handlingContent tagged with handling, inContent tagged with in, oracleContent tagged with oracle, scriptsContent tagged with scripts, shellContent tagged with shell This content has been marked as final. Show 3 replies 1. Re: Oracle Error Handling in Shell Scripts EdStevens Aug 31, 2012 1:15 PM (in response to user5647282) user5647282 wrote: I need to manage 2 diferente class of errors : Oracle Errors(produced in compilation time) and Operating Syste Error(e.g. No Datbase conection ORA-1017,etc) my shell its KSH. Please can you help me how can I manage then? this my alternative but is not correct ; #creating conexion with sql exit | sqlplus -s $USERPV_DB/$PWDPV_DB @$VORDSQLPATH/ord.extractor_porven.sql $VFDESDE $VFHASTA > $VORDDATOS_PATH/ord.ext