Which has the higher priority in your organization: Deploying a new database or securing the ones you already have?
Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
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
