Thursday, October 28, 2010

Displaying compile-time warnings for triggers in Oracle

I'm keeping this hear mostly for my future reference.

The following code snippet is for displaying compile time warnings when a trigger compiles, but shows warnings.  For instance, if I compile a trigger in SQL Developer, sometimes it will compile, but then the trigger is invalidated when I try to run it.  I go to check for any errors, but SQL Developer only shows 'Compiled with warnings'.

select line, position, text from dba_errors where owner='SCHEMANAME' and
name='TRIGGERNAME' and type='TRIGGER'
order by sequence, line, position;

This query can also be used for package and package body compilation errors/warnings. Instead of type='TRIGGER' use (type='PACKAGE' or type='PACKAGE BODY')

Note, the user you're running the query as, needs to have select priveleges to dba_errors, such as the SYS user.

No comments: