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 ??
"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 ]
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
0
