handling PL/SQL compile errors, 'show errors' as procedure

From: Volker Apelt <gq437x_at_yahoo.de>
Date: Thu, 02 Dec 2004 11:38:25 +0100
Message-ID: <lg1xe9asta.fsf@chi.biosolveit.loc>

Q:
Which table and timestamp column is used for 'show errors' definition of 'the previously defined object'?

Longer description:

Concider you have the schema definition for a larger schema with tables and PL/SQL objects in a text file. Everything works for the current version but you want some error control for new pices of code.

So, you want SQLPLUS to abort when the first defective object definition is found and report an exist status unequal to zero in case of problems.

I tried to catch compilation errors with WHENEVER SQLERROR EXIT FAILURE and noticed sqlplus exits for errors in SQL code, but errors in PL/SQL code will pass unnoticed. Sqlplus will report the errors for the last compiled object from USER_ERRORS with 'show errors', but it will not abort the script. WHENEVER SQLERROR does not catch this type of errors.

Google found an article from T.Kyte with a PLS/SQL procedure which addresses this problem. The procedure raises an exception if it finds an error message in USER_ERRORS. But the original procedure required me to enter and update the object name as a parameter of the procedure for each use of the procedure.

Would't it be nice to let the method it self find the 'most recently compiled object' and raise only if that object had an error?

I tried to modify the procedure, but failed to find the proper timestamp column.

Do you know how which view and column finds the 'most recently compiled object'?

Thanks,

Volker

############################################# CODE 

create or replace procedure test_error

• derived from a procedure of T.Kyte from the news groups as n number; begin for rec in (select OBJECT_NAME, OBJECT_TYPE from ( select OBJECT_NAME, OBJECT_TYPE from USER_OBJECTS order by TIMESTAMP DESC
  • what is the proper view and time column for 'show errors'
  • for selection of the most recently compiled object? ) where ROWNUM = 1) loop
    • this 'loops' exactly once select count(*) into n from USER_ERRORS where name = rec.OBJECT_NAME and TYPE = rec.OBJECT_TYPE; if ( n > 0 ) then raise_application_error( -20000, n || ' Errors on ' ||rec.OBJECT_TYPE ||' '|| rec.OBJECT_NAME ); end if; end loop; end; / show errors

############################################ USAGE example

WHENEVER SQLERROR EXIT FAILURE BEGIN

• valid code null; END; / show errors exec test_error

BEGIN
   N := 1; -- invalid code
END;
/
show errors
exec test_error
-- above raises and produces an
EXIT 0

############################################

btw:
Oracle 92, Linux

-- 
Volker Apelt                   

Received on Thu Dec 02 2004 - 04:38:25 CST