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 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
