Using a pipelined table function.
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE OracleError AS OBJECT (
2 ErrNumber INTEGER,
3 Message VARCHAR2(4000));
4 /
Type created.
SQL>
SQL> CREATE OR REPLACE TYPE OracleErrors AS TABLE OF OracleError;
2 /
Type created.
SQL>
SQL> CREATE OR REPLACE FUNCTION OracleErrorTable
2 RETURN OracleErrors DETERMINISTIC PIPELINED
3 AS
4 v_Low PLS_INTEGER := -65535;
5 v_High PLS_INTEGER := 100;
6 v_Message VARCHAR2(4000);
7 BEGIN
8 FOR i IN v_Low..v_High LOOP
9 v_Message := SQLERRM(i);
10
11 IF v_Message != ' -' || TO_CHAR(i) || ': non-ORACLE exception '
12 AND v_Message != 'ORA' || TO_CHAR(i, '00000') || ': Message ' ||
13 TO_CHAR(-i) || ' not found; product=RDBMS; facility=ORA'
14 THEN
15 PIPE ROW(OracleError(i, v_Message));
16 END IF;
17 END LOOP;
18 RETURN;
19 END;
20 /
Function created.
SQL>
SQL> CREATE OR REPLACE VIEW all_oracle_errors
2 AS SELECT * FROM TABLE(OracleErrorTable());
View created.
SQL>
SQL> desc all_oracle_errors
Name Null? Type
----------------------------------------- -------- ----------------------------
ERRNUMBER NUMBER
MESSAGE VARCHAR2(4000)
SQL> SELECT MIN(errnumber), MAX(errnumber), COUNT(*) FROM all_oracle_errors;
MIN(ERRNUMBER) MAX(ERRNUMBER) COUNT(*)
-------------- -------------- ----------
-44412 100 15430
1 row selected.
SQL>
SQL>
SQL> COLUMN message FORMAT a60
SQL>
SQL> SELECT *
2 FROM all_oracle_errors
3 WHERE errnumber BETWEEN -115 AND 100;
ERRNUMBER MESSAGE
---------- ------------------------------------------------------------
-115 ORA-00115: connection refused; dispatcher connection table i
s full
-114 ORA-00114: missing value for system parameter SERVICE_NAMES
-113 ORA-00113: protocol name is too long
-112 ORA-00112: value of is null
-111 ORA-00111: invalid attribute
-110 ORA-00110: invalid value for attribute , must be between a
nd
-109 ORA-00109: invalid value for attribute :
-108 ORA-00108: failed to set up dispatcher to accept connection
asynchronously
-107 ORA-00107: failed to connect to ORACLE listener process
-106 ORA-00106: cannot startup/shutdown database when connected t
o a dispatcher
-105 ORA-00105: too many dispatcher configurations
-104 ORA-00104: deadlock detected; all public servers blocked wai
ting for resources
-103 ORA-00103: invalid network protocol; reserved for use by dis
patchers
-102 ORA-00102: network protocol cannot be used by dispatchers
-101 ORA-00101: invalid specification for system parameter DISPAT
CHERS
-100 ORA-00100: no data found
-97 ORA-00097: use of Oracle SQL feature not in SQL92 Level
-96 ORA-00096: invalid value for parameter , must be from among
-94 ORA-00094: requires an integer value
-93 ORA-00093: must be between and
-92 ORA-00092: LARGE_POOL_SIZE must be greater than LARGE_POOL_M
IN_ALLOC
-91 ORA-00091: LARGE_POOL_SIZE must be at least
-90 ORA-00090: failed to allocate memory for cluster database OR
ADEBUG command
-89 ORA-00089: invalid instance number in ORADEBUG command
-88 ORA-00088: command cannot be executed by shared server
-87 ORA-00087: command cannot be executed on remote instance
-86 ORA-00086: user call does not exist
-85 ORA-00085: current call does not exist
-84 ORA-00084: global area must be PGA, SGA, or UGA
-83 ORA-00083: warning: possibly corrupt SGA mapped
-82 ORA-00082: memory size of is not in valid set of [1], [2],
[4]
-81 ORA-00081: address range [, ) is not readable
-80 ORA-00080: invalid global area specified by level
-79 ORA-00079: variable not found
-78 ORA-00078: cannot dump variables by name
-77 ORA-00077: dump is not valid
-76 ORA-00076: dump not found
-75 ORA-00075: process "" not found in this instance
-74 ORA-00074: no process has been specified
-73 ORA-00073: command takes between and argument(s)
-72 ORA-00072: process "" is not active
-71 ORA-00071: process number must be between 1 and
-70 ORA-00070: command is not valid
-69 ORA-00069: cannot acquire lock -- table locks disabled for
-68 ORA-00068: invalid value for parameter , must be between a
nd
-67 ORA-00067: invalid value for parameter ; must be at least
-65 ORA-00065: initialization of FIXED_DATE failed
-64 ORA-00064: object is too large to allocate on this O/S (,)
-63 ORA-00063: maximum number of log files exceeded
-62 ORA-00062: DML full-table lock cannot be acquired; DML_LOCKS
is 0
-61 ORA-00061: another instance has a different DML_LOCKS settin
g
-60 ORA-00060: deadlock detected while waiting for resource
-59 ORA-00059: maximum number of DB_FILES exceeded
-58 ORA-00058: DB_BLOCK_SIZE must be to mount this database (no
t )
-57 ORA-00057: maximum number of temporary table locks exceeded
-56 ORA-00056: DDL lock on object '.' is already held in an inco
mpatible mode
-55 ORA-00055: maximum number of DML locks exceeded
-54 ORA-00054: resource busy and acquire with NOWAIT specified
-53 ORA-00053: maximum number of enqueues exceeded
-52 ORA-00052: maximum number of enqueue resources () exceeded
-51 ORA-00051: timeout occurred while waiting for a resource
-50 ORA-00050: operating system error occurred while obtaining a
n enqueue
-42 ORA-00042: Unknown Service name
-41 ORA-00041: active time limit exceeded - session terminated
-40 ORA-00040: active time limit exceeded - call aborted
-38 ORA-00038: Cannot create session: server group belongs to an
other user
-37 ORA-00037: cannot switch to a session belonging to a differe
nt server group
-36 ORA-00036: maximum number of recursive SQL levels () exceede
d
-35 ORA-00035: LICENSE_MAX_USERS cannot be less than current num
ber of users
-34 ORA-00034: cannot in current PL/SQL session
-33 ORA-00033: current session has empty migration password
-32 ORA-00032: invalid session migration password
-31 ORA-00031: session marked for kill
-30 ORA-00030: User session ID does not exist.
-29 ORA-00029: session is not a user session
-28 ORA-00028: your session has been killed
-27 ORA-00027: cannot kill current session
-26 ORA-00026: missing or invalid session ID
-25 ORA-00025: failed to allocate
-24 ORA-00024: logins from more than one process not allowed in
single-process mode
-23 ORA-00023: session references process private memory; cannot
detach session
-22 ORA-00022: invalid session ID; access denied
-21 ORA-00021: session attached to some other process; cannot sw
itch session
-20 ORA-00020: maximum number of processes () exceeded
-19 ORA-00019: maximum number of session licenses exceeded
-18 ORA-00018: maximum number of sessions exceeded
-17 ORA-00017: session requested to set trace event
-1 ORA-00001: unique constraint (.) violated
0 ORA-0000: normal, successful completion
1 User-Defined Exception
100 ORA-01403: no data found
91 rows selected.
SQL>
SQL>
SQL> DROP TYPE OracleErrors;
Type dropped.
SQL>
Related examples in the same category