oracle can not persist BLOB in JMS_MESSAGES table

[schambers]

I have looked everywhere and have not found that anyone is having this same problem, although we have it quite a lot, namely that Oracle (9.2.0.4 with thin driver) can not persist a BLOB with creating an empty one first and then updating with the binary data. For this reason we can not use the line of SQL found in the oracle-jdbc2-service.xml where

INSERT_MESSAGE = INSERT INTO JMS_MESSAGES (MESSAGEID, DESTINATION, MESSAGEBLOB, TXID, TXOP) VALUES(?,?,?,?,?)


Is this something that I am just missing? I saw a couple of posts about switching to oci drivers but, as this is a very large application, I am catching all kinds of trouble for even suggesting it. I also heard that the new Oracle 10g drivers might be different, but I got the same results with it.

Thanks,
Steve

[genman]

Oracle seems to think that 4k is enough for you pure 100% Java people. You can either use MySQL or Postgres, or pay $$$ for a third-party driver.

[ckmod]

I am experiencing the same problem with oracle 8.0.6 and jdbc driver version 9.0.1.4: I cannot store blobs larger than 4KB...:-(

[m0nstermind]

This is because Oracle JDBC driver is not JDBC conformant. Or they want to teach us all how to live. They believe, that using statement.setInputStream(), like jdbc2.PersistenceManager does, is not efficient enough to use, so they limited it to 4kb (actually this is because they passing BLOB as a RAW datatype, because for smaller lengths it is more efficient to include it in row data, which is limited since beginning of time in Oracle to 4k). For larger lengths they force us to use their non standard and crappy oracle.sql.BLOB extension.

So i just written my own patched version of jdbc2.PersistenceManager to use this Oracle's BLOBs extension, which is available here:
http://core.swdfactory.com/~oleg/jbossmq-orapm.zip

Please keep in mind - it works only with oracle database using Oracle JDBC drivers, developed by Oracle.

You can use this freely, but on your own risk. 8-)

[luc.texier]

You should switch to OCI, this is the only way as far as I know.

The other option, recommend by the Hibernate community is to drop Oracle drivers altogether and use those from
DataDirect
http://www.datadirect.com/products/jdbc/matrix/jdbcpublic.htm
or InetSoftware
http://www.inetsoftware.de/English/produkte/JDBC_Overview/oracle.htm

[mgruener]

I had a similar problem to this and it was fixed by upgrading ojdbc14.jar to version 10.1.0.3.0. I did not have to switch to oci. Thin now works for me.