Quick Tip: Java Stored Procedures in Oracle - Reading Content

This post is about how to get the contents of a resource file that is loaded into Oracle as part of Java Stored Procedures package.

While working on enhancing some Java procedures, I added a simple Java logger class, fashioned after Log4J. To be able to dynamically configure the logging properties (log levels, threshold), I added in a properties file, bundled into the Jar file itself¹. After loading the properties file into the Database (using LoadJava command; loaded as a Java Resource), I wanted a way to "see" what's inside the file. That's when I posted a question on StackOverflow, but eventually googled and found the answer. See my question and my own answer here. And here is the script, I came up with:

SET SERVEROUTPUT ON
EXEC DBMS_JAVA.SET_OUTPUT (1000000);
DECLARE
   bText CLOB; 
   len PLS_INTEGER;
   offset PLS_INTEGER;
   text VARCHAR2(2000);
BEGIN
   DBMS_LOB.CreateTemporary(bText, FALSE );
   DBMS_JAVA.Export_Resource('LAJavaSP.properties', bText);
   len := 1000; -- length of the lob to read.
   offset := 1;
   DBMS_LOB.Read(bText, len, offset, text); 
   DBMS_OUTPUT.Put_Line(text);
END;
/

This printed the following:

logLevel=ERROR
dbLogLevel=ERROR

For now, that's all I have in the properties file. Hoping to add more later.

---

1 Adding the properties file into the jar file makes it easy to read it using getSystemResourceAsStream. Otherwise, we will have to worry about Directory objects, Permissions etc.