As well as handling unexpected exceptions in user PL/SQL, the BMC_ERROR.ERROR_HANDLE procedure can handle exceptions which were specifically raised in user code when error conditions were detected.
There are a number of ways in which you can manually raise an exception in your code. For example, you can :-
bmc_error.raise_error('Something went wrong');
Which will raise an exception and terminate the running procedure. You can also pass a specific error number which is the user exceptions range (-20000 to -20999), for example :-
bmc_error.raise_error(-20001,'Something else went wrong');
When these exceptions are raised, they will be handled by the WHEN OTHERS handler and BMC_ERROR.ERROR_HANDLE, but the way in which they are handled is defined by ranges which are defined in the BMC_EXCEPTIONS package.
The BMC_EXCEPTIONS package
The third package which is part of the BMC_DEBUG suite is called BMC_EXCEPTIONS. This package just contains a specification, so it has no callable procedures, only public constants and exceptions which can be referred to in your code.
At the start of the package, there are three constants which define how user error numbers are handled by BMC_ERROR :-
-- The range of error numbers which are considered to be user exceptions.
-- When processing these bmc_error.error_handle will not log or stacktrace by default
min_user_error_no CONSTANT PLS_INTEGER := -20900;
max_user_error_no CONSTANT PLS_INTEGER := -20000;
-- The range of error numbers which are user-raisable but are treated as system errors.
-- Errors between this and min_user_error_no will be processed by bmc_error.error_handle in the same way
-- as general Oracle errors e.g. with a full log and stacktrace
min_bmcx_error_no CONSTANT PLS_INTEGER := -20999; -- Range is -20999 to -20901
So by default, the error numbers between -20000 and -20900 are considered to be user exceptions, so when ERROR_HANDLE processes one of these it does not write the exception details, call stack or message store to the BMC_DEBUG_LOG table. It does run a ROLLBACK TO SAVEPOINT to undo any changes DML made in the procedure, and calls ENDCALL to keep the call stack accurate, but that is all. The reason for this is that these exceptions are not considered to be the result of bugs, but are conditions which are purposely detected
There is a range of user error numbers which are processed in the same way as system exceptions, and these are defined by the MIN_BMCX_ERROR_NO constant, so by default these are the error numbers between -20901 and -20999. If ERROR_HANDLE processes one of these, it will treat it the same as one of the regular Oracle error codes, and will dump the call stack and the message store to the log table, along with the error details.
Checking runtime errors
Something which is often overlooked when writing PL/SQL is checking the outcome of DML statements. For example, an application might contain a DML statement such as this :-
UPDATE customers SET cust_name = in_cust_name WHERE cust_id = in_cust_id;
So here we are setting the customer name on an existing row in the CUSTOMERS table for a given CUST_ID.
If we assume that there is a primary key on the CUST_ID column, the general assumption would be that when this statement runs it will only update one row. However, if it updated no rows, or more than one row, the statement would not raise an exception. Unlike a SELECT .. INTO statement, which will raise exceptions if it retrieves no rows or more than 1 row, DML statements (INSERT, UPDATE and DELETE) do not raise exceptions based on the number of rows they process.
It is therefore good practice to check the outcome of DML statements, and raise an exception if they do not process the expected number of rows. BMC_ERROR contains procedures which make this checking easier to implement. We could modify the above example to :-
UPDATE customers SET cust_name = in_cust_name WHERE cust_id = in_cust_id;
bmc_error.raise_error_if(sql%rowcount <> 1,'Did not update 1 row in CUSTOMERS');
So the first argument to BMC_ERROR.RAISE_ERROR_IF is a boolean, into which you can pass an expression. Here we are checking SQL%ROWCOUNT, which contains the number of rows processed by the last SQL statement (namely the UPDATE). So if the passed boolean is TRUE, which will be the case if the UPDATE does not process 1 row, RAISE_ERROR_IF will raise an exception with the given error message.
Another way to write this check would be :-
UPDATE customers SET cust_name = in_cust_name WHERE cust_id = in_cust_id;
bmc_error.assert(sql%rowcount = 1,'Did not update 1 row in CUSTOMERS');
BMC_ERROR.ASSERT also accepts a boolean as the first argument, but raises an exception if the boolean is FALSE. The assert is used to specify conditions which should be TRUE, and so if they are FALSE an exception results.