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. I’ve recently posted about Java Stored Procedures in general here.

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 itself1. 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:

   bText CLOB; 
   offset PLS_INTEGER;
   text VARCHAR2(2000);
   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); 

This printed the following:

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.

Comments, please?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s