debugMinor
halt on error in a sqlplus script
Viewed 0 times
scripthaltsqlpluserror
Problem
I am deploying pl/sql code using several sql files that are called with
If a package got a compilation error the script continues to the end.
Is there a way to stop on every compilation error?
I tried
@@file.If a package got a compilation error the script continues to the end.
Is there a way to stop on every compilation error?
I tried
WHENEVER SQLERROR EXIT SQL.SQLCODE but the script still continues.Solution
This can't be done without some kind of workaround, so here's one for you.
You can recompile the PL/SQL after creation & raise an exception if the recompilation fails. This will cause SQL*Plus to exit on failure.
For example:
test.sql:
Example:
You can recompile the PL/SQL after creation & raise an exception if the recompilation fails. This will cause SQL*Plus to exit on failure.
For example:
test.sql:
create or replace procedure foo
as
begin
this is an error;
end;
/
exec execute immediate 'alter procedure foo compile'; exception when others then raise_application_error(-20000,'compilation error')Example:
[oracle@node1 ~]$ sqlplus phil/phil
SQL*Plus: Release 11.2.0.2.0 Production on Fri Dec 14 20:45:47 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE
SQL> @test.sql
Warning: Procedure created with compilation errors.
BEGIN execute immediate 'alter procedure foo compile'; exception when others then raise_application_error(-20000,'compilation error'); END;
*
ERROR at line 1:
ORA-20000: compilation error
ORA-06512: at line 1
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@node1 ~]$Code Snippets
create or replace procedure foo
as
begin
this is an error;
end;
/
exec execute immediate 'alter procedure foo compile'; exception when others then raise_application_error(-20000,'compilation error')[oracle@node1 ~]$ sqlplus phil/phil
SQL*Plus: Release 11.2.0.2.0 Production on Fri Dec 14 20:45:47 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE
SQL> @test.sql
Warning: Procedure created with compilation errors.
BEGIN execute immediate 'alter procedure foo compile'; exception when others then raise_application_error(-20000,'compilation error'); END;
*
ERROR at line 1:
ORA-20000: compilation error
ORA-06512: at line 1
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@node1 ~]$Context
StackExchange Database Administrators Q#29877, answer score: 5
Revisions (0)
No revisions yet.