Problem inserting records with CLOB column with Oracle in EJB 2.0.

Gabriel Jufer

Additional Info
EJB2.0 with an Oracle 8i, with a table containing a CLOB column.
In the entity bean of that table the get and set methods are defined to return and accept java.lang.String resp. for that particular CLOB column.
That particular field is specified as OracleClob using the <dbms-column-type> tag of the vendor specific descriptor (weblogic-cmp-rdbms-jar.xml).

But when trying to insert a record using create method, Oracle returns an error saying: record containing LOB column is not locked.

LOB's in Oracle are different than in other databases!
LOB mean Locator. Follow these SQL Steps to insert a Row in Oracle:
  1. Insert a row with all your data. All lobs (BLOB, CLOB) must have the value empty_blob() or byte[]{1}
  2. Lock the row and get it (we need a result set!) Select * from your_table for update
  3. Parse the result set to get the BLOB or CLOB Object oracle.jdbs.CLOB myCLOB = (CLOB) myResultSet.getObject( /*column index*/ 1);>
  4. With this object you can fill data into the db! get the CharArrayOutputStream, fill your data, flash it and close it!!
  5. Unlock the row with a SQL update.

Note: to update the row, you must call trim from the CLOB object before you fill in the data! Make sure you have the right Oracle thin driver!

Further info can be found on Oracle TechNet!

[It seems that all these steps would require a BMP bean, rather than a CMP. - AAG]