sqlplus.exe - how to know if an error occured during file execution script

by Dinu
(Iasi, Romania)

Hi everybody!

I have a program that run automatically some script files using sqlplus.exe. For some reasons the customer want to run all statements from the files even if errors are encountered.
For each script file I need to store if execution was done without errors.
I know that the use of WHENEVER SQLERROR EXIT FAILURE would help me but will also stop executing the rest of statements from files so is not a solution.

I can only insert some commands at the beggining or to the end of the scripts. Any ideas?

Thanks a lot!

Comments for sqlplus.exe - how to know if an error occured during file execution script

Average Rating starstarstarstarstar

Click here to add your own comments

Oct 13, 2015
click here
by: click here

I should say only that its awesome! The blog is informational and always produce amazing things.

Oct 10, 2015
by: sudoapp

As part of an assignment for research I have to find an article with relevant information on this topic and give the teacher our opinion and the article. Your article helped me a lot.

Jun 12, 2015
not enought
by: Dinu

Hi Anonymous!

and thanks for the solution.
Unfortunatelly I need a better solution from code and not looking myself in the log. You can suggest me to parse the log and find the errors but I don't want this because I have to check for all errors and code errors and I have a big chance to miss somethig. (so my application will not work fine in all possible cases.)
If I remember well in other products like sqlserver.exe (sql server), psql (postgree) you have the possibility to stop execution (or not) if an error occured and also all the time you know if the query was executed sucesfully by looking at application result (result less than zero meaning error)

Jun 12, 2015
Good post
by: Chesley Stracke Sr.

Awesome blog!

May 04, 2015
Detecting errors in SQL*Plus script
by: Anonymous

There is one easy way to dtermine if ant errors occured when running a SQL script in SQL^Plus and that is to spool the output from the scripts to a log file and then check the log file after the script has finished. If you include the command SET ECHO ON at the beginning of the script then every SQL statement executed will be echoed and written to the log file so you would be able to see which command any error related to.

If you're running on UNIX or Linux you could pipe the output through "grep" to look for any "ORA-" strings and "tee" them to another file if required. grep, like all UNIX commands, returns a status which you could use as a quick check pn whether or not there were any Oracle errors in the script executed.

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.