ORA-20000
Programs that rely on PL/SQL can often be hit with run-time errors that occur due to faults in design, problems with coding, and a number of other issues. However, one of the great aspects of working with PL/SQL in Oracle is that the user can plan for the errors that frequently arise by creating warnings, or exceptions, to signal them.
The user can have exceptions for items in a database such as “insufficient_budget” that signal when more funding is allocated to a particular budget category than what is owned. When the error occurs, an exception is raised and users can write routines called ‘exception handlers’ that essentially skip over the procedure to allow continuous running. The ORA-20000 concerns these types of user-defined errors as well as other errors that are artificially tacked onto a program to facilitate a database manager’s needs.
The Problem
The ORA-20000 is a generic error that almost always accompanies another error or a stack of errors. It is part of the reserved section of PL/SQL user-defined errors. The error is caused when a stored procedure (‘raise_application_error’) is called upon. Oracle raises exceptions from the innermost to the outermost error, so when the ORA-20000 is seen in front of a stack of errors, the user knows that the innermost error, or bottom, is the block that can serve as the catalyst.
The amount of information available on the ORA-20000 is minimal due primarily to its open-ended ness. Essentially, when a user sees an ORA-20000, their goal is not necessarily to correct the ORA-20000. Instead, they need to resolve the error accompanying an ORA-20000, regardless of whether it is a user-created error or a reserved error. Because the error accompanies several other error messages, let us look at some of the more common combinations for the ORA-20000.
THE SOLUTION
One example of the ORA-20000 conjoined with another set of errors is shown below. Suppose the following stack of exceptions is thrown together:
ORA-20000: ORA-20000: ORA-0000: normal, successful completion
Update failed for the ch_clnt_mast
Line: 632 Execution of ap_old_ib_terms_xfer_dr failed Line: 1045
ORA-06512: at “AEPRDFCRH.ORA_RAISERROR”, line 16
ORA-06512: at “AEPRDFCRH.AP_OL_IB_TERMS_XFER_DR”, line 935
To review, the ORA-06512 is an error caused when the stack is unwound by unhandled exceptions in the code. As previously mentioned, the ORA-06512 error and ORA-20000 error are often triggered together. To fix these errors, the user would need to correct the condition causing the errors or write an exception handler.
To begin correcting the stack of errors, check the code in the lines indicated in the error message. In this particular case, the user-defined error likely occurred due to being place in a WHEN OTHERS exception. Check over the code in line 632 (update failed for the ch_clnt_mast) as well as line 1045 (ap_old_ib_terms_xfer_dr failed). The user will have to remove or work with the exception handlers that are masking the real error message so they can rerun the code to discover what is occurring in the system.
Another common error combination is the ORA-20000: ORU-10027: buffer overflow. DBMS_OUTPUT has various default buffer sizes that all depend on the user’s version of Oracle. In the system, the buffer size limit is 2000 bytes. The user can extend the buffer all the way to 1,000,000 bytes by issuing the statement below:
DBMS_OUTPUT.ENABLE(1000000);
The comparable SQL*Plus statement looks like this:
set serveroutput on size 1000000
If the user is working with Oracle’s 10g release or something more recent, unlimited buffer settings can be set with the following:
DBMS_OUTPUT.ENABLE (buffer_size => NULL);
And the SQL*Plus version:
set serveroutput on size unlimited
This should offset the ORA-20000: ORU-10027, but, if the user conducts this approach and is still triggering the error, it is recommended to look back through the code in full to see if any items are overriding the buffer settings.
