Troubleshooting ORA-XXX: Message XXX Not Found in ODAC 12c with Custom Exceptions
The Problem: Lost in Translation
Have you encountered the dreaded "ORA-XXX: Message XXX not found; product=RDBMS; facility=ORA" error while working with Oracle Database and its client library, ODAC 12c? This cryptic message usually arises when you've tried to raise a custom exception in your application, only to have it swallowed by the ODAC client, leaving you with a generic error.
Scenario and Code Example
Imagine you're developing an application using ODAC 12c and you've implemented custom exceptions for specific scenarios.
-- Example using PL/SQL stored procedure
CREATE OR REPLACE PROCEDURE my_procedure
AS
invalid_input EXCEPTION;
PRAGMA EXCEPTION_INIT(invalid_input, -20001);
BEGIN
-- some logic...
IF some_condition THEN
RAISE invalid_input;
END IF;
END;
/
You expect your application to catch the custom exception invalid_input (with error code -20001) and handle it accordingly. However, instead of the expected custom error message, you end up with the generic "ORA-XXX: Message XXX not found" error.
Understanding the Root Cause
The root cause of this issue lies in the way ODAC 12c handles custom exceptions. It attempts to retrieve the error message associated with the custom exception code from the database. However, if the error message is not found in the database, it defaults to displaying the generic "ORA-XXX: Message XXX not found" message.
Solution: Mapping Custom Error Messages
To ensure that your application catches and displays custom exceptions correctly, you need to map the custom error messages to the corresponding exception codes in the database.
There are two primary ways to achieve this:
- Using
DBMS_ERROR_TEXT.FORMAT_ERROR_STACK:- This function allows you to format the error stack and include the custom exception message.
- The
DBMS_ERROR_TEXT.FORMAT_ERROR_STACKfunction takes the exception code as a parameter and returns a formatted error string that includes the custom exception message. - You can then use the returned error string to display the custom exception message in your application.
-- Example of using DBMS_ERROR_TEXT.FORMAT_ERROR_STACK
BEGIN
-- some logic...
IF some_condition THEN
RAISE invalid_input;
END IF;
EXCEPTION
WHEN invalid_input THEN
DBMS_OUTPUT.PUT_LINE(DBMS_ERROR_TEXT.FORMAT_ERROR_STACK);
END;
/
- Using
DBMS_ERROR_TEXT.GET_ERROR_TEXT:- This function directly retrieves the error message associated with the exception code.
- If the error message is found in the database, the function returns the message.
- If the error message is not found, it returns the generic "ORA-XXX: Message XXX not found" message.
-- Example of using DBMS_ERROR_TEXT.GET_ERROR_TEXT
BEGIN
-- some logic...
IF some_condition THEN
RAISE invalid_input;
END IF;
EXCEPTION
WHEN invalid_input THEN
DBMS_OUTPUT.PUT_LINE(DBMS_ERROR_TEXT.GET_ERROR_TEXT(-20001));
END;
/
Additional Insights
- Make sure that the custom error messages are properly defined in your database.
- Verify that the exception code and the error message are correctly associated in the database.
- Remember that the error message will be displayed in the language of the user's session.
Conclusion
By understanding the interaction between ODAC 12c and custom exceptions, you can troubleshoot the "ORA-XXX: Message XXX not found" error and display your custom error messages effectively. Ensure that you correctly map your custom error messages in the database to ensure a seamless experience for your users.
References:
