Using CLOB/BLOB field

[srobiolle]

Hello,

I have a field in my Oracle database table which is of type "CLOB" (seems similar to BLOB).

How can I use it in a report ? Designer refuse to create an object based on that field.

Thanx

Stéphane

 

[Subhashab]

what is the column used for. i think BO support only long type column

 

[srobiolle]

My field contains plain text, but the CLOB type can surely contains something else.

 

[Subhashab]

in designer rewrite the select defintion to
to_char(yourclobcolumn)
and select character type for the object this should work

 

[srobiolle]

I've tried this, and all kinds of conversions, but it fails.

So, diving into the oracle books (their documentation web site is just incredible !), here is some explanation :

- the CLOB type is a TEXT type of 4 Gb size,
- you can SELECT a CLOB field from SQL/Plus, but not inside Designer or while developping an application (Delphi, Java,VB...),
- The solution is to use the DBMS_LOB package for manipulating BLOB, CLOB and NCLOB objects.

Exemple :

SELECT DBMS_LOB.SUBSTR(MyField,1000,1)FROM MyTable

This only returns the first 1000 bytes (yes, Oracle has changed the usual syntax...).

Stéphane