Can I make a SQL * Plus output with an error if the function compilation fails?

Question

I was working on an Oracle function that will be deployed automatically using SQL * Plus. Sometimes I get it wrong, and Oracle says:

A warning. Function created with compilation errors.

Then I can use SHOW ERR to see the error, but I wonder if there is some kind of configuration that I can set this way with such a compilation error:

• Function will not be created
• The original error will be emitted.
• SQL * PLUS will exit with a non-0 exit value

Something like WHENEVER SQLERROR would be awesome.

Answer 1

This is a bit confusing, but you can.

A function or procedure will be created in the original CREATE FUNCTION or CREATE PROCEDURE expression. You will need to detect in your script that there were errors, and explicitly discard the function and / or procedure if there were errors. But you will need to fix the errors before dumping the object. This 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 this script is executed, the following output will be issued, which includes errors and discards 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