The moose likes JDBC and the fly likes Create sql log file using JDBC Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Create sql log file using JDBC" Watch "Create sql log file using JDBC" New topic

Create sql log file using JDBC

Maverick Smith

Joined: Nov 01, 2012
Posts: 3
Hi All,

I need to log SQL query output in a file using JAVA.

We can use tools like SQL*PLUS to generate the log file after .sql file execution using spooling. I need to generate similar file using JDBC.
I tried googling it but unable to find any concrete apart from java.util.logging package which did not fulfill my requirement.

DB : Oracle 11g
After executing above script using SQL*plus, we'll get mylogfile.lg file something like this

Now i need to generate similar log file using JAVA.

Please help me .

Thanks in advance !

Martin Vajsar

Joined: Aug 22, 2010
Posts: 3606

Welcome to the Ranch!

In what kind of application do you need to do this, and why? The approaches might differ depending on that.

There is no built-in support for this kind of logging in JDBC. You'll have to do it yourself. In principle, you need to do with ResultSets something very similar to what is described here for PreparedStatement - that is, you'll wrap it in your own class and log operations made on it. In my opinion, it will be bit harder than wrapping the PreparedStatement, though.
Maverick Smith

Joined: Nov 01, 2012
Posts: 3
Thanks Martin for your reply.

I need to execute a sql script (with different case numbers) script using SQL*plus daily. 200-300 times a day. Also need to capture the log file after each time execution.
Now, I want to automate this thing using java where I will provide the case numbers in a text file as input and the application should
1. pick the case number
2. prepare update script for case number
3. execute the update script
4. capture the log file
5. repeat the procedure for all the case numbers.

Let me know if any more details required.

Also, is there any other alternative way which i can try?

Martin Vajsar

Joined: Aug 22, 2010
Posts: 3606

In this case, I'd let Java prepare the SQL script and run SQL*plus to do the hard work. It will take care of all the logging, and you'll also have the script that was run for reference.

You might be interested in SQL*plus' WHENEVER SQLERROR command. You can use this to stop the script execution when an SQL error occurs, and return an exit code. The Java program might use the exit code to detect errors.

If you haven't used Java to run other processes yet, you'll need to read this article.
wood burning stoves
subject: Create sql log file using JDBC
Similar Threads
memory/cpu issue of reading/writing files
How to make a data source connection without writing a User name & Password and the -ds.xml file
JavaMail - why doesn't this work???
p6spy - spy.properties settings