Displaying CLOB Column with 4000+ characters

oracle clob
pein Asd picture pein Asd · Sep 9, 2011 · Viewed 39.3k times · Source

I have this CLOB column and I need to display it using a select statement.

I used DBMS_LOB.SUBSTR to convert it to varchar2:

select DBMS_LOB.SUBSTR(T1.CLOB_COL,4000,1) CLOB_COL from T1

My problem is some of my CLOBS contains more than 4000 characters. How can I display it...any idea/suggestion?

thanks a lot..

Answer

Kevin Burton picture Kevin Burton · Sep 9, 2011

I guess you could display the chunks as separate rows ?

SELECT ROWNUM as chunk_no,ID, SUBSTR (t1.clob_col, (ROWNUM-1)*4000, 4000) AS chunk
FROM t1
CONNECT BY (ROWNUM-1)*4000 <= LENGTH(t1.clob_col)

or if there is a constraint on the maximum size a clob could be in your system you could hard code the number of text columns returned

SELECT SUBSTR (t1.clob_col, 1, 4000) AS pt1,
       CASE WHEN LENGTH (t1.clob_col) > 4000  THEN SUBSTR (t1.clob_col, 4001, 4000) END AS pt2,
       CASE WHEN LENGTH (t1.clob_col) > 8000  THEN SUBSTR (t1.clob_col, 8001, 4000) END AS pt3,
       CASE WHEN LENGTH (t1.clob_col) > 12000 THEN SUBSTR (t1.clob_col, 1201, 4000) END AS pt4
FROM t1

Related questions

How to query a CLOB column in Oracle

I'm trying to run a query that has a few columns that are a CLOB datatype. If i run the query like normal, all of those fields just have (CLOB) as the value. I tried using DBMS_LOB.substr(column) …

Read More
ORA-00932: inconsistent datatypes: expected - got CLOB

Considering that TEST_SCRIPT is a CLOB why when I run this simple query from SQL*PLUS on Oracle, I get the error: ORA-00932: inconsistent datatypes: expected - got CLOB I have been reading a lot of questions about the …

Read More
How to convert CLOB to VARCHAR2 inside oracle pl/sql

I have a clob variable, need to assign it to varchar2 variable. The data inside clob var is less than 4000 (i..e varchar2's maxsize) oracle10+ I tried report_len := length(report_clob); report := TO_CHAR(dbms_lob.substr(report_…

Read More