Eclipselink Oracle Lob

You always need special care to read/write lobs not only with JPA but also with JDBC.

In case of JPA you can have persistent lob fields e.g.

 @Lob

@Basic(fetch = FetchType.LAZY)

@Column(nullable = false)

private byte[] content;

Lazy fetch type is usually good, so that not to read lob unless it is required. 

If your entity is detached (almost always in j2ee/web) you have to query for the lob field

SELECT o.content FROM Message o where o.id  = ?

Query.getSingleResult()

In case of Oracle, correct way to read/write lobs varies with db version and driver version. Lobs of size less than 4K are handled normally but for larger sizes it can be an issue. In case of EclipseLink, you just need to set following properties in persistence.xml to read/write lobs > 4K. Behind the screen EclipseLink will first insert empty blob field and then update it to actual value.

<property name="eclipselink.target-database" value=" org.eclipse.persistence.platform.database.oracle.OraclePlatform "/>

<property name="eclipselink.target-server" value="WebLogic_10" />

Assuming you are using Weblogic 10g or higher, for other values refer to Eclipselink docs.


You can verify size of lob fields by following SQL query
select DBMS_LOB.GETLENGTH(content) from message where id = 3

SQL Sequence Cache - Missing ids

Carefully decide your sql sequence caching strategy especially in case of multiple connections accessing the table. We often use db as a storage for Producer Consumer scenario, like below


App1 ---(inserting)---> TableA <---(querying)--- App2 


TableA has a before-insert trigger which creates primary key using the sequence. 


Now for consumer application i.e. App2, I was using a query 
select * from TableA where id > :lastReadId
where :lastReadId is obviously the id which App2 accessed on last query. 


Run both apps and you'll see a problem of missing ids, i.e. some ids are missed during insertion and consequently data is not consumed completely. A peek will show that this problem arises when we use cached sequence, so change it no-cache and it'll work fine.