« Wirthless Expelled ReviewStein's "Just An Opinion" On Science »

ORA-06575: Package or function X is in an invalid state

04/30/08

  03:08:17 pm, by Nimble   , 268 words  
Categories: Thoughts, Programming

ORA-06575: Package or function X is in an invalid state

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.

3 comments

Comment from: Patrick [Visitor]
Patrick

Thank you very much for this gem!

01/22/09 @ 06:05
Comment from: Prakash [Visitor]
Prakash

Thanks for this.

02/09/12 @ 12:39
Comment from: Bruno Ferreira [Visitor]
Bruno Ferreira

Thak you!

01/18/17 @ 06:06

An unexpected error has occurred!

If this error persists, please report it to the administrator.

Go back to home page

Additional information about this error:

MySQL error!

Table './nimblebr_b2evolution/evo_hitlog' is marked as crashed and should be repaired(Errno=145)

Your query: Record the hit

INSERT INTO evo_hitlog ( hit_datetime, hit_uri, hit_disp, hit_ctrl, hit_action, hit_type, hit_referer_type, hit_referer, hit_referer_dom_ID, hit_keyphrase_keyp_ID, hit_keyphrase, hit_serprank, hit_coll_ID, hit_remote_addr, hit_agent_type, hit_agent_ID, hit_response_code, hit_sess_ID )
VALUES ( FROM_UNIXTIME( 1679594847 ), '/index.php/ora_06575_package_or_function_x_is_in_an?blog=2', 'single', NULL, NULL, 'standard', 'direct', '', NULL, NULL, NULL, NULL, '2', '3.236.241.39', 'unknown', NULL, '200', '11007047' )