"show errors" For Oracle Scripts

by Brandon 2. June 2011 22:59

Everything seems to be turned of by default in Oracle.  That includes returning the details of an error in a sql script. 

After finishing a piece of code like a stored proc or trigger, most developers immediately run the script to make sure there are no errors.  Unfortunately this is all you'll see with Oracle:

Warning: Trigger created with compilation errors. 

There is, fortunately, a way to show the detail of those errors.  If you add a "/" to terminate the script and then "show errors;" to the end of you script.  You'll see the following instead:

Warning: Trigger created with compilation errors.

Errors for TRIGGER PROJECT_BIU:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/5  PL/SQL: SQL Statement ignored
3/12  PL/SQL: ORA-02289: sequence does not exist

Here's an example:


create or replace trigger PROJECT_BIU
before insert
on PROJECT
referencing old as old new as new
for each row

begin
    --create the id
    select ProjectNuber_seq.nextval
    into :new.Project_Number
    from dual;
end;
/
show errors;

About the authors

Three brothers (yes we're really brothers) all writing and architecting .net solutions.  Our original inspiration for this blog was simply to give the same useful advice we receive from other blogs on a very regular basis.  Lets face it, thanks to the Internet and its contributors, we're a long way away from the days of shifting through a 900 page reference book to find that one line of code.  But lately we've also noticed the shift in the view of application development.   Where once we were the heros of optimization and efficiency, we're now seen a bunch of budget blowing goof-offs that nobody trusts.  We've got some ideas to change that and we'll try to share those thoughts between the short snips of code.