« Wirthless Expelled Review | Stein's "Just An Opinion" On Science » |
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.