This week's book giveaway is in the OCPJP forum.
We're giving away four copies of OCA/OCP Java SE 7 Programmer I & II Study Guide and have Kathy Sierra & Bert Bates on-line!
See this thread for details.
The moose likes Oracle/OAS and the fly likes PL/SQL PLS-00905 Object is invalid error Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "PL/SQL PLS-00905 Object is invalid error" Watch "PL/SQL PLS-00905 Object is invalid error" New topic
Author

PL/SQL PLS-00905 Object is invalid error

G. Z.
Greenhorn

Joined: Jun 14, 2002
Posts: 1
Hello !
I've created the following PL/SQL stored function:
CREATE OR REPLACE FUNCTION EREFILL (PARM_RXN CHAR) RETURN VARCHAR2 IS
PARM_MSG VARCHAR2 (460);
BEGIN
SELECT DECODE (MSG, NULL, 'N/A', MSG)
INTO PARM_MSG
FROM ER_REFILL_MESSAGES
WHERE RXN = PARM_RXN;
RETURN PARM_MSG;
EXCEPTION
WHEN NO_DATA_FOUND THEN RETURN 'RX_NOT_FOUND';
WHEN OTHERS THEN RETURN 'DATABASE UNAVAILABLE';
END EREFILL;
*** The problem is: The procedure compiles and works, but when the table (ER_REFILL_MESSAGES) is dropped by accident or when for some reason the server fails and it is not created properly in the nightly process - When called it returns the following error message:
SQL> DECLARE MSG VARCHAR2(460);
2 BEGIN
3 MSG := EREFILL ('CN','831','000882811');
4 DBMS_OUTPUT.PUT_LINE (MSG);
5 END;
6 /
DECLARE MSG VARCHAR2(460);
*
ERROR at line 1:
ORA-06550: line 3, column 11:
PLS-00905: object MS00.EREFILL is invalid
ORA-06550: line 3, column 4:
PL/SQL: Statement ignored
*** I would expect the 'WHEN OTHERS' exception to take over and return 'DATABASE UNAVAILABLE' from the function.
I know in a regular case you have to fix the problem with the table object and recompile/rerun the function, but I'm looking for a way to return 'DATABASE UNAVAILABLE' from the function instead of getting the error message from SQL-PLUS...
Can you help ??
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17258
    
    6

"G. Z"-
Welcome to the JavaRanch! Please adjust your displayed name to meet the
JavaRanch Naming Policy.
You can change it
here.
Thanks! and welcome to the JavaRanch!
As far as your question. I think (I double checked and this is the case) that once the table was lost, it automatically invalidated the function, and it needs to be recompiled again.
Mark
[ June 14, 2002: Message edited by: Mark Spritzler ]

Perfect World Programming, LLC - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
Beksy Kurian
Ranch Hand

Joined: Jul 11, 2001
Posts: 254
It would be better to check if the table exists or not before you call the function. Create it if it does not(ORA-00942 table or view does
not exist).If the you create the table and then call the function oracle will automatically compile the function.
Regards
Beksy
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: PL/SQL PLS-00905 Object is invalid error