More Oracle and Java Woes

Today I continued the trek toward completing the project described in my last entry. Though I don’t think I ran into as many issues today as I did in the past week or so of working on the project, today certainly had it’s fair share.

First up was a rather interesting exception being thrown by a JDBC operation, namely “java.sql.SQLException: SQL string is not Query.” This is apparently intended to be JDBC’s way of explaining that PreparedStatement.executeQuery() doesn’t work for DML operations. To execute one of those, you have to use either execute() or executeUpdate(). Thankfully, a forum thread was able to point me in the right direction on that one.

Next on the list, if Oracle JDeveloper tells you “The WAR file is already up to date,” don’t believe it! I don’t know what logic it’s using to decide whether or not the class files constituting a WAR file are out-of-date, but there are definitely some cases where it’s flawed. I spent a better part of the morning trying to figure out why everything from undeploying and redeploying the EAR file to bouncing the OAS installation was still giving me illogical output. Come to find out, I didn’t know the WAR file not being updated was relevant to the problem at the time, but it certainly proved to be in the end! Tried searching for a bug report on this, but came up empty, so maybe it’s just me.

Last but not least, I take issue with the language used in the mod_plsql User’s Guide to describe its process of file upload handling. Though it never explicitly states this, it seems to imply that the internal handling of performing an INSERT operation to place data for an uploaded file into the document table takes place in a separate transaction from that of the action procedure that gets executed afterward.

You have to go to the PL/SQL User’s Guide to read why this is not the case. To sum it up, a transaction can span multiple procedures. A procedure being executed as a data cartridge operates within a transaction that is implicitly committed when that procedure terminates so long as no uncaught exceptions are raised. However, until that point, the effects of any DML operations executed are only visible to the procedure. This includes the INSERT procedure performed by mod_plsql on the document table. What this effectively means is that the only way something other than the procedure can see that the inserted record exists unless the procedure does an explicit COMMIT.

If you read my last post, you know that I was calling a servlet from the data cartridge. You can probably imagine the amount of aggravation this caused me when I ran my servlet locally without issue, had to backtrack to figure out where the servlet was failing when it was deployed, and then found out that a single COMMIT statement at the beginning of my data cartridge procedure made things work as expected. So, yay for lacking Oracle documentation.

I did get the servlet working, though. It can now pull data from the database, convert it from Excel binary to CSV format, and put the converted data back into the database. So, the Clean Content API, while not specifically designed for the purpose for which I’m using it, is at least a somewhat capable solution. That basically sums up my day, folks. I’ll be back on Monday to do it again.

Comments are closed.