thrullcom

dbms_java.export_resource - oracle & java resources on server

dbms_java.export_resource - oracle & java resources on server

In an old Java/Oracle application I needed to display java resource body to find out where this old applications put its logs. It uses widely known Log4J library which is configured in log4j.properties.

The log4j.properties file is deployed as a JAVA RESOURCE object. To find out what what JAVA and JAVA RESOURCE we have available can be checked using this query:

SELECT OBJECT_NAME,OBJECT_TYPE FROM USER_OBJECTS t WHERE t.object_type LIKE 'JAVA%'

Displaying the body of a JAVA RESOURCE is a bit tricky:

DECLARE b CLOB; c varchar2(2000); i integer:= 255; begin DBMS_LOB.createtemporary(b, false); DBMS_JAVA.export_resource('log4j.properties', 'YOUR_SCHEMA_NAME', b); DBMS_OUTPUT.PUT_LINE('java_resource:'); DBMS_LOB.read(b, i, 1, c); DBMS_OUTPUT.PUT_LINE(c); end;

If java resource content longer that 255 characters (limitation of DBMS_OUTPUT.PUT_LINE You can use following code:

DECLARE b CLOB; c varchar2(2000); i integer; PROCEDURE pl ( str IN VARCHAR2 , len IN INTEGER := 80 , expand_in IN BOOLEAN := TRUE ) IS v_len PLS_INTEGER := LEAST (len, 255); v_len2 PLS_INTEGER; v_chr10 PLS_INTEGER; v_str VARCHAR2 (2000); BEGIN IF LENGTH (str) > v_len THEN v_chr10 := INSTR (str, CHR (10)); IF v_chr10 > 0 AND v_len >= v_chr10 THEN v_len := v_chr10 - 1; v_len2 := v_chr10 + 1; ELSE v_len2 := v_len + 1; END IF; v_str := SUBSTR (str, 1, v_len); DBMS_OUTPUT.put_line (v_str); pl (SUBSTR (str, v_len2), len, expand_in); ELSE v_str := str; DBMS_OUTPUT.put_line (str); END IF; EXCEPTION WHEN OTHERS THEN IF expand_in THEN DBMS_OUTPUT.ENABLE (1000000); DBMS_OUTPUT.put_line (v_str); ELSE RAISE; END IF; END; begin dbms_lob.createtemporary(b, false); dbms_java.export_resource('log4j.properties', 'SMSSYSTEM', b); DBMS_OUTPUT.PUT_LINE('java_resource:'); i := 2000; dbms_lob.read(b, i, 1, c); pl(c); DBMS_OUTPUT.ENABLE (1000000); DBMS_OUTPUT.PUT_LINE(c); end;