CODEWITHSUNDEEP
oracle-databaseerror-handlingsqlpluscompiler-warnings
theory
theory

Reputation: 9887

Can I make SQL*Plus exit with an error on function compilation failure?

I've been working on an Oracle function that will be deployed in an automated fashion via SQL*Plus. Sometimes I make a mistake, and Oracle says:

Warning: Function created with compilation errors.

I can then use SHOW ERR to see the error, but I'm wondering if there is some configuration I can set such that, on such a compilation error:

Something like WHENEVER SQLERROR would be awesome.

Upvotes: 4

Views: 3194

Answers (1)

Justin Cave
Justin Cave

Reputation: 231651

It's a bit convoluted, but you can.

The initial CREATE FUNCTION or CREATE PROCEDURE statement will create the function or procedure. You'd have to detect in your script that there were errors and explicitly drop the function and/or procedure if there were errors. But you'd have to capture the errors before dropping the object. That will require some code in your script after the CREATE statement.

whenever sqlerror exit failure;

create or replace procedure compile_error
as
begin
  select count(*)
    into no_such_variable
    from emp;
end;
/

show error;

declare
  l_num_errors integer;
begin
  select count(*)
    into l_num_errors
    from user_errors
   where name = 'COMPILE_ERROR';

 if( l_num_errors > 0 )
 then
   execute immediate 'DROP PROCEDURE compile_error';
   raise_application_error( -20001, 'Errors in COMPILE_ERROR' );
 end if;
end;
/

When executed, this script will produce the following output which includes the errors and will drop the procedure.

SQL> @c:\temp\compile_errors.sql

Warning: Procedure created with compilation errors.

Errors for PROCEDURE COMPILE_ERROR:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/3      PL/SQL: SQL Statement ignored
5/10     PLS-00201: identifier 'NO_SUCH_VARIABLE' must be declared
6/5      PL/SQL: ORA-00904: : invalid identifier
declare
*
ERROR at line 1:
ORA-20001: Errors in COMPILE_ERROR
ORA-06512: at line 12


Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Upvotes: 6

Related Questions