Oracle DBMS_SYS_ERROR
Version 11.2.0.3

General Information
Purpose An officially unsupported package it is 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';
Security Model Owned by SYS: Execute is granted to SYSTEM and EXFSYS
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

Related Topics
Exception Handling

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2013 Daniel A. Morgan All Rights Reserved