Oracle GET_ERROR$
Version 21c

General Information
Library Note Morgan's Library Page Header
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 Locates error messages.
AUTHID DEFINER
Dependencies
DBMS_JAVA USER_ERRORS  
Documented No
First Available Not known
Package Header

Reformatted for readability
CREATE OR REPLACE PACKAGE get_error$ AS
 TYPE myrec IS RECORD (errormsg VARCHAR(4000));
 TYPE myrctype IS REF CURSOR RETURN myrec;

 FUNCTION error_lines(classname IN VARCHAR2) RETURN myrctype;
END get_error$;
/
Package Body

Reformatted for readability
CREATE OR REPLACE PACKAGE BODY get_error$ AS
 FUNCTION error_lines(classname IN VARCHAR2) RETURN myrctype IS
  rc         myrctype;
  short_name VARCHAR2(50);
  n          NUMBER;
 BEGIN
   OPEN rc FOR
   SELECT text
   FROM user_errors
   WHERE name = dbms_java.shortname(classname);

   RETURN rc;
 END;
END get_error$;
/
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/initjvm4.sql
 
ERROR_LINES
To get an output this demo requires rec_array have a size > 0

See comment under "purpose" at the top of the page

Based on the compiler warnings at right, unreachable code, unnecessary cursors, we recommend a remedial class of "How to write a simple program" be considered. Consideration of this recommendation should take into account that TKHCS_LOG_LISTAGGCLOBTYPE is new in 21c.
get_error$.error_lines(classname IN VARCHAR2) RETURN myrctype;
SELECT name, COUNT(*)
FROM user_errors
GROUP BY name
ORDER BY 1;

NAME                           COUNT(*)
------------------------------ --------
TKHCS_LOG_LISTAGGCLOB                 1
TKHCS_LOG_LISTAGGCLOBTYPE             9
TKHCS_LOG_PKG                         1
UTL_MAIL                             10
UTL_MAIL_INTERNAL                     4
UTL_PG                               27
UTL_SYS_COMPRESS                      1


set serveroutput on

DECLARE
 RetVal get_error$.myrcType;
 RetRec get_error$.myRec;
 x      INTEGER;

 TYPE array_t IS TABLE OF VARCHAR2(4000)INDEX BY BINARY_INTEGER;
 rec_array array_t;
BEGIN
  RetVal := get_error$.error_lines('UTL_MAIL');

  FETCH RetVal BULK COLLECT INTO rec_array;
  FOR i IN rec_array.FIRST .. rec_array.LAST LOOP
    dbms_output.put_line(rec_array(i));
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(TO_CHAR(rec_array.COUNT));
END;
/
PLW-07203: parameter 'ADDR_LIST' may benefit from use of the NOCOPY compiler hint
PLW-07203: parameter 'SMTP_SERVERS' may benefit from use of the NOCOPY compiler hint
PLW-07203: parameter 'SMTP_PORTS' may benefit from use of the NOCOPY compiler hint
PLW-06010: keyword "DATA" used as a defined name
PLW-06010: keyword "DATA" used as a defined name
PLW-06010: keyword "DATA" used as a defined name
PLW-06009: procedure "SEND_I" OTHERS handler does not end in RAISE
PLW-06002: Unreachable code
PLW-06002: Unreachable code
PLW-06002: Unreachable code

DECLARE
 RetVal get_error$.myrcType;
 RetRec get_error$.myRec;
 x      INTEGER;

 TYPE array_t IS TABLE OF VARCHAR2(4000)INDEX BY BINARY_INTEGER;
 rec_array array_t;
BEGIN
  RetVal := get_error$.error_lines('TKHCS_LOG_LISTAGGCLOBTYPE');

  FETCH RetVal BULK COLLECT INTO rec_array;
  FOR i IN rec_array.FIRST .. rec_array.LAST LOOP
    dbms_output.put_line(rec_array(i));
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(TO_CHAR(rec_array.COUNT));
END;
/
PLW-07203: parameter 'ACTX' may benefit from use of the NOCOPY compiler hint
PLW-07203: parameter 'SELF' may benefit from use of the NOCOPY compiler hint
PLW-07203: parameter 'SELF' may benefit from use of the NOCOPY compiler hint
PLW-07203: parameter 'RETURNVALUE' may benefit from use of the NOCOPY compiler hint
PLW-05018: unit TKHCS_LOG_LISTAGGCLOBTYPE omitted optional AUTHID clause; default value DEFINER used
PLW-07203: parameter 'ACTX' may benefit from use of the NOCOPY compiler hint
PLW-07203: parameter 'SELF' may benefit from use of the NOCOPY compiler hint
PLW-07203: parameter 'SELF' may benefit from use of the NOCOPY compiler hint
PLW-07203: parameter 'RETURNVALUE' may benefit from use of the NOCOPY compiler hint

Related Topics
Built-in Functions
Built-in Packages
DBMS_ERRLOG
Exception Handling
What's New In 19c
What's New In 20c-21c

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