Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am deploying pl/sql code using several sql files that are called with @@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.

share|improve this question

1 Answer 1

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:

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 ~]$
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.