ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
Purpose
An officially unsupported package utilized by more than 200 of Oracle's built-ins as part of their exception handling. I do not recommend using it directly but it is a good model for how to think about exception handling.
AUTHID
DEFINER
Dependencies
SELECT name FROM dba_dependencies WHERE referenced_name = 'DBMS_SYS_ERROR'
UNION
SELECT referenced_name FROM dba_dependencies WHERE name = 'DBMS_SYS_ERROR'
ORDER BY 1;
-- this query returns 243 objects
Documented
No
First Available
Not known
Security Model
Owned by SYS: Execute is granted to GGSYS, GSMADMIN_INTERNAL, SYSBACKUP, and SYSTEM
Source
{ORACLE_HOME}/rdbms/admin/prvthsye.plb
RAISE_SYSTEM_ERROR
Undocumented
Overload 1
dbms_sys_error.raise_system_error(
num IN BINARY_INTEGER,
keeperrorstack IN BOOLEAN DEFAULT FALSE);
CREATE OR REPLACE TRIGGER system.def$_propagator_trig
BEFORE INSERT ON system.def$_propagator
DECLARE
prop_count NUMBER;
BEGIN
SELECT COUNT(*)
INTO prop_count
FROM system.def$_propagator;
IF (prop_count > 0) THEN
-- raise duplicate propagator error
sys.dbms_sys_error.raise_system_error(-23394);
END IF;
END;
/
Overload 2
dbms_sys_error.raise_system_error(
num IN BINARY_INTEGER,
arg1 IN VARCHAR2,
keeperrorstack IN BOOLEAN BOOLEAN DEFAULT FALSE);
TBD
Overload 3
dbms_sys_error.raise_system_error(
num IN BINARY_INTEGER,
arg1 IN VARCHAR2,
arg2 IN VARCHAR2,
keeperrorstack IN BOOLEAN BOOLEAN DEFAULT FALSE);
-- this demo is fabricated from production code in $ORACLE_HOME/rdbms/admin/catdwgrd.sql
col cname format a30
col version format a12
SELECT cname, version, org_version, prv_version
FROM registry$;
DECLARE
p_prv_version VARCHAR2(30);
p_compatible VARCHAR2(30);
BEGIN
-- Get the previous version of the CATPROC component
SELECT prv_version
INTO p_prv_version
FROM registry$
WHERE cid = 'CATPROC';
-- return the current compatible value
SELECT value
INTO p_compatible
FROM v$parameter
WHERE name = 'compatible';
IF p_compatible > p_prv_version THEN
dbms_sys_error.raise_system_error(-39707, p_compatible, p_prv_version);
END IF;
END;
/
Overload 4
dbms_sys_error.raise_system_error(
num IN BINARY_INTEGER,
arg1 IN VARCHAR2,
arg2 IN VARCHAR2,
arg3 IN VARCHAR2,
keeperrorstack IN BOOLEAN BOOLEAN DEFAULT FALSE);
TBD
Overload 5
dbms_sys_error.raise_system_error(
num IN BINARY_INTEGER,
arg1 IN VARCHAR2,
arg2 IN VARCHAR2,
arg3 IN VARCHAR2,
arg4 IN VARCHAR2,
keeperrorstack IN BOOLEAN BOOLEAN DEFAULT FALSE);
TBD
Overload 6
dbms_sys_error.raise_system_error(
num IN BINARY_INTEGER,
arg1 IN VARCHAR2,
arg2 IN VARCHAR2,
arg3 IN VARCHAR2,
arg4 IN VARCHAR2,
arg5 IN VARCHAR2,
keeperrorstack IN BOOLEAN BOOLEAN DEFAULT FALSE);
TBD
Overload 7
dbms_sys_error.raise_system_error(
num IN BINARY_INTEGER,
arg1 IN VARCHAR2,
arg2 IN VARCHAR2,
arg3 IN VARCHAR2,
arg4 IN VARCHAR2,
arg5 IN VARCHAR2,
arg6 IN VARCHAR2,
keeperrorstack IN BOOLEAN BOOLEAN DEFAULT FALSE);
TBD
Overload 8
dbms_sys_error.raise_system_error(
num IN BINARY_INTEGER,
arg1 IN VARCHAR2,
arg2 IN VARCHAR2,
arg3 IN VARCHAR2,
arg4 IN VARCHAR2,
arg5 IN VARCHAR2,
arg6 IN VARCHAR2,
arg7 IN VARCHAR2,
keeperrorstack IN BOOLEAN);
TBD
Overload 9
dbms_sys_error.raise_system_error(
num IN BINARY_INTEGER,
arg1 IN VARCHAR2,
arg2 IN VARCHAR2,
arg3 IN VARCHAR2,
arg4 IN VARCHAR2,
arg5 IN VARCHAR2,
arg6 IN VARCHAR2,
arg7 IN VARCHAR2,
arg8 IN VARCHAR2,
keeperrorstack IN BOOLEAN BOOLEAN DEFAULT FALSE);
TBD
Testing
Examples of testing to understand the encapsulated functionality
SQL> exec dbms_sys_error.raise_system_error(-20000);
BEGIN dbms_sys_error.raise_system_error(-20000); END;
*
ERROR at line 1:
ORA-21001: error number argument to raise_system_error of -20000 is out of range
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at line 1
SQL> exec dbms_sys_error.raise_system_error(-39707);
BEGIN dbms_sys_error.raise_system_error(-39707); END;
*
ERROR at line 1:
ORA-39707: compatibile parameter too high for downgrade to
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at line 1
SQL> exec dbms_sys_error.raise_system_error(-39708);
BEGIN dbms_sys_error.raise_system_error(-39708); END;
*
ERROR at line 1:
ORA-39708: component '' not a component
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at line 1
