Only a few dozen Oracle errors have predefined names such as NO_DATA_FOUND. To handle any other error by name, such as ORA-02292 for a parent row that still has children, or your own application errors, declare an exception and bind it to the error number with PRAGMA EXCEPTION_INIT.
Code for This Guide
The main examples are in the examples/exceptions folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
name exception; pragma exception_init(name, -error_number); raise_application_error(-20000 .. -20999, 'message' [, keep_errors]);
The pragma follows the exception's declaration, in the same declaration section, and the number is negative, as SQLCODE returns it.
Handle ORA-02292 by Name
Example:
declare
e_child_rows exception;
pragma exception_init(e_child_rows, -2292);
begin
delete from routes where route_id = 1;
exception
when e_child_rows then
dbms_output.put_line('Route 1 still has flights: ' || sqlerrm);
end;
/Output:
Route 1 still has flights: ORA-02292: integrity constraint (NIMBUS.FLIGHTS_ROUTE_FK) violated - child record found PL/SQL procedure successfully completed.
Deleting route 1 fails because flights reference it. The handler for e_child_rows reads more clearly than WHEN OTHERS with a test on SQLCODE.
Raise Application Errors
RAISE_APPLICATION_ERROR raises an error numbered from -20000 to -20999 with your own message, which reaches the caller like any Oracle error.
Example:
declare
v_status flights.status%type;
begin
select status into v_status from flights where flight_id = 30;
if v_status <> 'SCHEDULED' then
raise_application_error(-20001, 'Flight 30 can''t be rebooked: it is ' || v_status);
end if;
end;
/Output:
declare * ERROR at line 1: ORA-20001: Flight 30 can't be rebooked: it is ARRIVED ORA-06512: at line 6
Name Your Own Application Errors
Combining the two lets callers handle your application errors by name.
Example:
-- name an application error so callers can handle it by name
declare
e_not_rebookable exception;
pragma exception_init(e_not_rebookable, -20001);
procedure rebook (p_flight_id number) is
v_status flights.status%type;
begin
select status into v_status from flights where flight_id = p_flight_id;
if v_status <> 'SCHEDULED' then
raise_application_error(-20001, 'Flight ' || p_flight_id || ' is ' || v_status);
end if;
end;
begin
rebook(30);
exception
when e_not_rebookable then
dbms_output.put_line('Handled by name: ' || sqlerrm);
end;
/Output:
Handled by name: ORA-20001: Flight 30 is ARRIVED PL/SQL procedure successfully completed.
The procedure raises -20001 for a flight that cannot be rebooked, and the caller catches it as e_not_rebookable. Declare such exceptions in a package specification so every caller shares the names.
Things to Know
- Keep a list of your application error numbers and their meanings, ideally as named exceptions in one package.
- A third argument TRUE to RAISE_APPLICATION_ERROR keeps the previous errors on the error stack.
- Do not bind names to NO_DATA_FOUND's number; use the predefined name.
Related Guides
Conclusion
PRAGMA EXCEPTION_INIT gives a name to any Oracle error number, so handlers can catch it by name. Combine it with RAISE_APPLICATION_ERROR to define named application errors that callers can handle as clearly as built-in ones.
See my articles more often when you search Google.
