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 ??
