Oracle DBMS_SYS_ERROR
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
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 249 objects
Documented No
First Available Not known
Security Model Owned by SYS: Execute is granted to SYSTEM, EXFSYS, SYSBACKUP, and GSMADMIN_INTERNAL
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
Packages

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-2014 Daniel A. Morgan All Rights Reserved