Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Create sql log file using JDBC

 
Maverick Smith
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
e.g.
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
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?

regards,
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Consider Paul's rocket mass heater.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic