ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
Purpose
Locates error messages caused by invalid classes.
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;
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
