Output Stream of Consciousness

If you go throw a function into Oracle, and it's wrong, e.g.

CREATE OR REPLACE FUNCTION Banana IS -- Oops, no return!
BEGIN
RETURN SUBSTUR('Banana',2,5); -- Oops, misspelled!
END;

You will get no warnings about this in most tools until it comes time to call it:

SELECT Banana FROM DUAL;

(If Banana were a procedure, we would use CALL Banana; instead)

You will get the error ORA-06575: Package or function BANANA is in an invalid state.

So how do you find out what's wrong? SHOW ERRORS; only works for SQL statements, not the contents of a stored procedure.

This is what you have to do:
SELECT * FROM USER_ERRORS WHERE NAME='BANANA' ORDER BY SEQUENCE, LINE;

It is important to put the name in uppercase.

You get:

BANANA, FUNCTION, 1, 1, 17, PLS-00103: Encountered the symbol "IS" when expecting one of the following: ( return compress compiled wrapped, ERROR, 103

If we add in the RETURN VARCHAR2 before the IS, and select the user errors again, we get:

BANANA, FUNCTION, 1, 3, 10, PLS-00201: PLS-00201: identifier 'SUBSTUR' must be declared, ERROR, 201
BANANA, FUNCTION, 2, 3, 3, PL/SQL: Statement ignored, ERROR, 0

Nice, rich error messages, but what a way to have to get them!

If you are using PL/SQL Developer, take a look under Functions or Procedures. Any stored procedures in error show up with a little X on their icon. The errors will show up if you right click->Edit. Creating the stored procedures from right-click->New on these items will save you a lot of hassle.

If you are stuck with a bit of a "manual" tool, however, the USER_ERRORS technique is your best bet.