Fraska Portal

Exploring the WebSphere Commerce world

Oracle, avoid oracle.sql.CLOB issue

Posted by on in Tips
  • Font size: Larger Smaller
  • Hits: 3385
  • 60 Comments
  • Subscribe to this entry
  • Print

If you are working with Oracle and jdbc API and you are facing an odd behaviour trying to fetch the value of a CLOB or BLOB field, the following note could help you.

Making a SELECT includes the fetch of one or more CLOBs the system can returns a value like "oracle.sql.CLOB@...." instead of the real value of the field. It seems the buffer cannot handle a so big and memory expensive value. A workaround is to use an Oracle function converts the CLOB in a VARCHAR and trim the value as requested.

 

For example: 

#query with the issue (guessing Description is a CLOB):

SELECT Description FROM Cities;

Above query returns oracle.sql.CLOB@....- for each row in the table Cities- instead of the Description's value.

 

#query overcome the issue applying the suggested workaround:

SELECT dbms_lob.substr( Description, 4000, 1 ) FROM Cities

This query returns the correct values (to be honest just the first 4000 chars of the field, but it's fair enough!).

 

When I faced this issue the resource I found helpful and where I got above info is the following:

Ask Tom, Converting CLOBS TO VARCHAR

 

Rate this blog entry:
0

Comments

Leave your comment

Guest
Guest Monday, 18 November 2019

Most Popular Post

WebSphere Commerce, the curious life of a front-end catalog request
Core
Rate this blog entry:
5
WebSphere Commerce, the SOLR extension index
Administration
Rate this blog entry:
4
WebSphere Commerce, Data Load and SOLR Delta Index
Data Load
Rate this blog entry:
0

Latest Blogs

WebSphere Commerce, CommandLevelAuthorizationCache
Cache
Rate this blog entry:
0
WebSphere Commerce v8, toolkit exception, ClassNotFound db2
Administration
Rate this blog entry:
1
WebSphere Commerce, ATP migration
Store
Rate this blog entry:
0
WebSphere Commerce, the curious life of a front-end catalog request
Core
Rate this blog entry:
5
WebSphere Commerce, Performance analysis of few European stores
Performance
Rate this blog entry:
0