Home » SQL & PL/SQL » SQL & PL/SQL » Error handing with batch sql scripts (Oracle, 10g, Windows/Linux)
Error handing with batch sql scripts [message #560649] Mon, 16 July 2012 03:39 Go to next message
anyoneokay
Messages: 8
Registered: September 2007
Junior Member
Hello all,

I want run servral scripts in batch, and I use autorun.bat to call main.sql, which including servral scripts. If there has any pl/sql error in script, then the script will stop to run, but not exit SQL*Plus. If the pl/sql must exit, can it output the error messages in a file?

Can anyone help me? any suggestions will appreciated.

Noteļ¼šPlease don't use "whenever sqlerror exit|continue...", because it will exit pl*sql tool or continue to run the other sql, it's not easy to know where the error happened

autoRun.sql
---------------------------------------------------------------------------
sqlplus "sys/manager@ORADB as sysdba" @main.sql


main.sql
---------------------------------------------------------------------------
--WHENEVER SQLERROR Exit SQL.SQLCODE
select sysdate from dual;
@@test1.sql
@@test2.sql


test1.sql
---------------------------------------------------------------------------
select 'This is test1.sql' from dual;
select a;           --error will happen


test2.sql
---------------------------------------------------------------------------
select 'This is test2.sql' from dual;
insert into scott.emp select * from scott.emp; --error will happen
Re: Error handing with batch sql scripts [message #560656 is a reply to message #560649] Mon, 16 July 2012 04:01 Go to previous messageGo to next message
Michel Cadot
Messages: 58847
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SPOOL command.

Regards
Michel
Re: Error handing with batch sql scripts [message #560814 is a reply to message #560656] Tue, 17 July 2012 04:52 Go to previous messageGo to next message
anyoneokay
Messages: 8
Registered: September 2007
Junior Member
Michel Cadot, thank you very much, it's a good suggestion.
I use spool command, add some codes as following in main.sql, and it will output error message to the spool file
main.sql
---------------------------------------------------------------------------
--WHENEVER SQLERROR Exit SQL.SQLCODE
set timing off
set heading off
set feedback off
spool D:\install.log
select sysdate from dual;
@@test1.sql
@@test2.sql
spool off

Re: Error handing with batch sql scripts [message #560821 is a reply to message #560814] Tue, 17 July 2012 05:17 Go to previous message
Michel Cadot
Messages: 58847
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks to let us know.

Regards
Michel
Previous Topic: drop user cascade issue
Next Topic: SQL Query two columns
Goto Forum:
  


Current Time: Wed Aug 20 22:03:03 CDT 2014

Total time taken to generate the page: 0.23428 seconds