Click here to Skip to main content
15,868,016 members
Articles / Web Development
Tip/Trick

Quick Tip: Java Stored Procedures in Oracle – Reading Content

Rate me:
Please Sign up or sign in to vote.
4.00/5 (1 vote)
30 Dec 2013CPOL 12K   4
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

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:

SQL
SET SERVEROUTPUT ON
EXEC DBMS_JAVA.SET_OUTPUT (1000000);
DECLARE
   bText <a title="CLOB" href="href="http://www.orafaq.com/wiki/CLOB">CLOB</a>;
   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:

<span style="color: rgb(0, 0, 0);">logLevel=ERROR
dbLogLevel=ERROR</span>
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior) City of Los Angeles
United States United States
Originally a Physics major, fell in love with Microprocessors and switched to Computer Science 20+ years ago. Since then, dabbled in various languages including, PowerBuilder, Oracle, Java, C, C++, Perl, Python etc. Constantly striving for quality and performance too.

I try to help fellow developers with technology as a way of "giving back to the community". Blogging became a natural extension of that effort. Still learning to perfect that art. If one new programmer out there benefits from this blog, my time and effort are fully worth it.

The underlying theme in my blogs is power and beauty of programming (and technology in general). A well written program gives me the sense of awe you get when you look at a man made wonder like Angkor Wat. You experience poetry, art, mystique, power all at once. A program and the troubleshooting that ensues also gives you a feeling you get while reading a mystery novel!

Comments and Discussions

 
QuestionCan you please delete newer revisions Pin
Sam Varadarajan29-Dec-13 15:40
professionalSam Varadarajan29-Dec-13 15:40 
AnswerRe: Can you please delete newer revisions Pin
Wendelius29-Dec-13 20:07
mentorWendelius29-Dec-13 20:07 
GeneralRe: Can you please delete newer revisions Pin
Sam Varadarajan30-Dec-13 5:24
professionalSam Varadarajan30-Dec-13 5:24 
GeneralRe: Can you please delete newer revisions Pin
Wendelius30-Dec-13 11:05
mentorWendelius30-Dec-13 11:05 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.