Oracle DBMS_SYS_ERROR
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
Coming to OpenWorld 2018? Be sure to visit the TidalScale booth in Moscone South and learn how to solve performance problems and lower costs with Software Defined Servers. Before you visit the booth, or if you can't make it this year, check out TidalScale at www.tidalscale.com. Be sure to click on the Solutions link and look through the Oracle resources.
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 215 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

Related Topics
Built-in Functions
Built-in Packages
DBMS_SYS_ERROR
Exception Handling
What's New In 12cR1
What's New In 12cR2

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