Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Thursday, September 11, 2008

How to see the contents of a CLOB column without special tools

Recently Sabrix had some issues and they turned on logging. Sabrix detailed messages go into the CLOB column extended_message present in the table sabrix_log. However we were unable to see beyond 4000 characters of a row in sqlplus. Instead of using any special tools to see the contents of a CLOB column, I advised the DBAs to extract the data like this:

create table sabrix_clob as select extended_message from sabrix_log;

exp file=sabrix_clob.txt userid=apps/apps tables=apps.sabrix_log

vi sabrix_clob.txt

Export dump file is an ASCII file if all you are exporting is character data. You can very easily see the contents of the CLOB column inside the export dump file.


DomBrooks said...

> we were unable to see beyond 4000 characters of a row in sqlplus

set long(size) <bignumber>

e.g. set long 10000

Vikram Das said...

Hi dombrooks,

set long n

Here 'n' can not be set to more than 2 million. A clob can contain 4GB of data, a lot more than 2 million characters. By using an export dump, you can see the full 2 million characters.

- Vikram