adm_error

Centralized error handling.

Owns every error code ADM raises, the default message for each one, and a raise_error procedure that logs and raises in a single call. Packages must not call raise_application_error directly any more - the number would carry no meaning and nobody outside this schema could handle it.

Placeholders in the message templates use apex_string.format syntax: %0 .. %9.

adm_error.raise_error(
  p_error_code => adm_error.c_err_doc_not_found
, p_scope      => l_scope
, p0           => p_document_id
);
-- raises: ORA-20140: Document not found: 42

Override the default message when the situation needs its own wording:

adm_error.raise_error(
  p_error_code => adm_error.c_err_invalid_state
, p_scope      => l_scope
, p_message    => 'Only items at the top of the trash can be permanently deleted'
);

A literal percent sign in an overridden message has to be doubled - apex_string.format reads %0 .. %9 as placeholders, so ‘Disk is 90%% full’ is what produces “90% full”.

Rule: never put sqlerrm or a backtrace into the message. The message is shown to the end user; diagnostics go to p_extra, which is logged and never surfaced.

adm_error.raise_error(
  p_error_code => adm_error.c_err_storage_request
, p_scope      => l_scope
, p0           => 'delete object'
, p_extra      => sqlerrm || chr(10) || sys.dbms_utility.format_error_backtrace
);

Handling an ADM error as a consumer - three ways, in order of precision:

-- one specific error
exception
  when adm_error.e_doc_not_found then ...

-- a whole category
exception
  when others then
    if adm_error.is_no_permission(sqlcode) then ...
    elsif adm_error.is_not_found(sqlcode) then ...
    else raise;
    end if;

-- ours versus everything else
if adm_error.is_adm_error(sqlcode) then ...

Functions and Procedures

raise_error

Log an error and raise it in one call.

The default message for p_error_code is used unless p_message is given. Logging happens before the raise, because raise_application_error carries at most 2048 characters while p_extra usually holds the detail you need.

Signature:

procedure raise_error (
  p_error_code in number,
  p_scope in varchar2 default null,
  p0 in varchar2 default null,
  p1 in varchar2 default null,
  p2 in varchar2 default null,
  p3 in varchar2 default null,
  p4 in varchar2 default null,
  p5 in varchar2 default null,
  p6 in varchar2 default null,
  p7 in varchar2 default null,
  p8 in varchar2 default null,
  p9 in varchar2 default null,
  p_message in varchar2 default null,
  p_extra in clob default null,
  p_log in boolean default true
);

Parameters:

Name	Direction	Type	Description
p_error_code	in	number	One of the c_err_* constants
p_scope	in	varchar2 default null	Where it happened, e.g. ‘adm_document_api.create_document’
p0	in	varchar2 default null	Substitution value for %0
p1	in	varchar2 default null	Substitution value for %1
p2	in	varchar2 default null	Substitution value for %2
p3	in	varchar2 default null	Substitution value for %3
p4	in	varchar2 default null	Substitution value for %4
p5	in	varchar2 default null	Substitution value for %5
p6	in	varchar2 default null	Substitution value for %6
p7	in	varchar2 default null	Substitution value for %7
p8	in	varchar2 default null	Substitution value for %8
p9	in	varchar2 default null	Substitution value for %9
p_message	in	varchar2 default null	Message template overriding the default for p_error_code
p_extra	in	clob default null	Diagnostics to log and not show: sqlerrm, a backtrace, a response body
p_log	in	boolean default true	Whether to log before raising. Pass false only when the caller has

                already logged this exact error. |

---

log_error

Log the error currently being handled, so the caller can re-raise it.

Replaces the hand-written apex_debug.error(‘Error in x: ’ || sqlerrm || ’ - Backtrace: ’ || …); that every exception handler used to carry. Always followed by a bare raise:

exception when others then adm_error.log_error(p_scope => c_scope_prefix || ‘create_document’); raise;

PL/SQL only allows raise; lexically inside a handler, which is why this procedure logs but does not re-raise - the raise has to stay at the call site.

An ADM error was already logged where it was raised, so it is recorded here at info level only, so one failure is not logged three times as it travels up through nested handlers. Anything else is logged at error level with its backtrace.

Call this only from inside an exception handler.

Signature:

procedure log_error (
  p_scope in varchar2,
  p_extra in clob default null
);

Parameters:

Name	Direction	Type	Description
p_scope	in	varchar2	Where it was caught, e.g. ‘adm_document_api.create_document’
p_extra	in	clob default null	Additional context worth logging, e.g. the arguments in play

---

get_category

The category of an error code, or null when the code is not ADM’s.

Codes in the AI Pack range are classified from their position in that range, which is why adm_ai_error allocates by category sub-range - see the note there. It keeps this function correct without the base product depending on the optional pack.

Signature:

function get_category (
  p_sqlcode in number
) return varchar2;

Parameters:

Name	Direction	Type	Description
p_sqlcode	in	number	An Oracle error code, normally sqlcode

Returns: varchar2 - One of the c_cat_* constants, or null

---

is_adm_error

Whether an error code is one of ADM’s, base product or AI Pack.

False for the bundled UC plugin libraries, for uc_ai and for Oracle’s own errors, even though some of those also sit in the -20000 .. -20999 application range.

Signature:

function is_adm_error (
  p_sqlcode in number
) return boolean;

Parameters:

Name	Direction	Type	Description
p_sqlcode	in	number	An Oracle error code, normally sqlcode

Returns: boolean - True when ADM raised it

---

is_not_found

Whether the error says something did not exist.

Signature:

function is_not_found (
  p_sqlcode in number
) return boolean;

Parameters:

Name	Direction	Type	Description
p_sqlcode	in	number	An Oracle error code, normally sqlcode

Returns: boolean

---

is_no_permission

Whether the error says the current user was not allowed to do it.

Signature:

function is_no_permission (
  p_sqlcode in number
) return boolean;

Parameters:

Name	Direction	Type	Description
p_sqlcode	in	number	An Oracle error code, normally sqlcode

Returns: boolean

---

is_conflict

Whether the error says something already existed.

Signature:

function is_conflict (
  p_sqlcode in number
) return boolean;

Parameters:

Name	Direction	Type	Description
p_sqlcode	in	number	An Oracle error code, normally sqlcode

Returns: boolean

---

is_invalid_input

Whether the error blames the caller’s input.

Signature:

function is_invalid_input (
  p_sqlcode in number
) return boolean;

Parameters:

Name	Direction	Type	Description
p_sqlcode	in	number	An Oracle error code, normally sqlcode

Returns: boolean

---

is_invalid_state

Whether the error blames the state of the object rather than the request.

Signature:

function is_invalid_state (
  p_sqlcode in number
) return boolean;

Parameters:

Name	Direction	Type	Description
p_sqlcode	in	number	An Oracle error code, normally sqlcode

Returns: boolean

---

strip_ora_prefix

The message of an ADM error without the leading ORA-nnnnn: prefix.

Signature:

function strip_ora_prefix (
  p_sqlerrm in varchar2
) return varchar2;

Parameters:

Name	Direction	Type	Description
p_sqlerrm	in	varchar2	The full error text, normally sqlerrm

Returns: varchar2 - The message only, or p_sqlerrm unchanged when there is no prefix

---

apex_error_handler

APEX error handling function. Register it on the application under Edit Application Properties > Error Handling > Error Handling Function as adm_error.apex_error_handler.

ADM errors that were written for end users are shown as they are, without the ORA-nnnnn: prefix. Errors in the INTERNAL and EXTERNAL categories, constraint violations and anything not ours are replaced by a generic sentence, because their text names tables, columns and HTTP endpoints. The original is kept in the APEX debug log and in the APEX error log either way.

Signature:

function apex_error_handler (
  p_error in apex_error.t_error
) return apex_error.t_error_result;

Parameters:

Name	Direction	Type	Description
p_error	in	apex_error.t_error	The error APEX caught

Returns: apex_error.t_error_result - What APEX should display

---