Win a copy of Learning OpenStack Networking: Build a solid foundation in virtual networking technologies for OpenStack-based clouds this week in the Cloud/Virtualization forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Liutauras Vilda
  • Campbell Ritchie
  • Tim Cooke
  • Bear Bibeault
  • Devaka Cooray
Sheriffs:
  • Jeanne Boyarsky
  • Knute Snortum
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Ganesh Patekar
  • Stephan van Hulst
  • Pete Letkeman
  • Carey Brown
Bartenders:
  • Tim Holloway
  • Ron McLeod
  • Vijitha Kumara

PL/SQL PLS-00905 Object is invalid error  RSS feed

 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ??
 
ranger
Sheriff
Posts: 17314
11
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"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 ]
 
Ranch Hand
Posts: 254
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!