ORACLE BLOB, CLOB Handling

Some time it is necessary to convert blob, clob text data to varchar, want to datalenght of blob/clob field. That situation DBMS_LOB packgae can be utilize.

Some Example

1. SELECT DBMS_LOB.SUBSTR(CLOB_Field) AS VARCHAR_FIELD FROM AnyTable

2. SELECT DBMS_LOB.LENGTH(CLOB_Field) AS VARCHAR_LENGTH FROm AnyTable

I hope It helps.