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.
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.
Joined: Nov 01, 2012
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?
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.