Cayenne uses "EMPTY_CLOB()" or "EMPTY_BLOB()" as appropriate. Are you
sure you have OracleAdapter selected for your project? Do you see any
errors?
Andrus
> I noticed that Cayenne does not properly handle LOBs in Oracle. When
> Cayenne adds a record with a LOB, it simply writes it in the
> PreparedStatement with setBytes(). However, as demonstrated by the
> Oracle Q&A article below, this only satisfies BLOBs <4k. To have a blob
> more than 4k, the row must be inserted, followed by an EMPTY_BLOB() call
> (or EMPTY_CLOB()), followed by the blob write. An example pulled (and
> stripped) from Metalink is as follows:
>
>
> ps = conn.prepareStatement("INSERT INTO table (fields...,blobField)
> VALUES (..., ?)");
> ...
> ps.setBinaryStream(idx, null, 0);
> ps.executeUpdate();
> ps.close();
>
> ps = conn.prepareStatement("UPDATE table SET blobField=EMPTY_BLOB()
> WHERE ...");
> ...
> ps.executeUpdate();
> ps.close();
>
> ps = conn.prepareStatement("SELECT blobField FROM table WHERE ..."); ...
> rs = ps.executeQuery();
>
> Blob dbBlob = null;
> if (rs.next()) {
> dbBlob = writeToBlob(rs, data);
> }
>
> rs.close();
> ps.close();
>
> ps = conn.prepareStatement("UPDATE table SET blobField=? WHERE ...");
> ...
> ps.setBlob(idx, dbBlob);
>
>
> See this Oracle Q&A article as an example:
>
> -----------------------
>
> When Do I Use EMPTY_BLOB() and EMPTY_CLOB()?
> Question
>
> When must I use EMPTY_BLOB() and EMBPTY_CLOB()? I always thought it was
> mandatory for each insert of a CLOB or BLOB to initialize the LOB
> locator first with either EMPTY_CLOB() or EMPTY_BLOB().
> Answer
>
> In Oracle, you can initialize a LOB with data using the INSERT statement
> as long as the data is <4K. This is why your insert statement worked.
> Note that you can also update a LOB with data that is <4K using the
> UPDATE statement. If the LOB is larger than 4K perform the following
> steps:
>
> 1. Insert into the table initializing the LOB using EMPTY_BLOB() or
> EMPTY_CLOB() and use the returning clause to get back the locator
> 2. For LOB attributes, call ocilobwrite() to write the entire data to
> the
> LOB. For other than LOB attributes, you can insert all the data using
> the INSERT statement.
>
> Note the following:
>
> * Oracle has now removed the <4K restriction and you can insert >4K
> worth of data into the LOB using the insert or even the update statement
> for LOB columns. Note however, that you cannot initialize a LOB
> attribute which is part of an object type with data and you must use
> EMPTY_BLOB()/EMPTY_CLOB().
> * Also you cannot use >4K as the default value for a LOB even though
> you
> can use >4k when inserting or updating the LOB data.
> * Initializing the LOB value with data or using
> EMPTY_BLOB()/EMPTY_CLOB() is orthogonal to how the data is stored. If
> the LOB value is less than approximately 4K, then the value is stored
> inline (as long as the user doesn't specify DISABLE STORAGE IN ROW) and
> once it grows larger than 4K, it is moved out of line.
> -----------------------
>
> Is this behavior feasible in Cayenne?
This archive was generated by hypermail 2.0.0 : Wed Nov 10 2004 - 16:39:09 EST