show errors shows nothing?

You learn something new every day - you really do. Today i finally learnt how to get errors displayed when compiling code in sqlplus that didn't seem to have worked properly for years. I guess i just thought there was a bug somewhere that was never fixed.

Previously to find out why certain package bodies wouldn't compile i'd have to go into sqldeveloper (other tools are available of course) and get that to show me the problem or query some dictionary views.

But today i discovered something new (and this is probably one of those things that everyone else knew but me....)

So take a classic example of what i mean

I compile a package

SQL>  alter package ate2.AP_PKG compile;

Warning: Package altered with compilation errors.

Something has gone wrong

SQL> show errors
No errors.

That's helpful..... 

Now at this point in sqlplus you can go and select from USER/ALL/DBA_ERRORS and you can find whats wrong but that's a pain to do.

So what's the trick to getting to the error easily?

Well it seems there is extended syntax for show errors - who knew? (well not me clearly)

If you look at the syntax for it you see this

Usage: SHOW ERRORS [{ FUNCTION | PROCEDURE | PACKAGE |

   PACKAGE BODY | TRIGGER | VIEW

   | TYPE | TYPE BODY | DIMENSION

   | JAVA SOURCE | JAVA CLASS } [schema.]name]

So if i now try

SQL> sho errors package ate2.ap_pkg;

No errors.

Still shows nothing - but lets check the body separately

SQL> show errors package body ate2.ap_pkg;

Errors for PACKAGE BODY ATE2.AP_PKG:

822/11   PL/SQL: SQL Statement ignored

873/23   PL/SQL: ORA-00918: column ambiguously defined

And there we go - we get direct access to what we want - this has probably been around since the 90s and i didn't know .

I'm not sure what the issue is with just generic show errors, i'm sure i've had this problem on objects other than packages - but anyway seems i now have a simple fix.