8

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..

benRollag
  • 1,219
  • 4
  • 16
  • 21
pein Asd
  • 81
  • 1
  • 1
  • 2

1 Answers1

11

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
Kevin Burton
  • 11,676
  • 2
  • 24
  • 37
  • After searching and trying many different techniques, this is the only thing that worked for me, using straight SQL. Most other solutions require PL/SQL or other methods that require special permissions. – bobfet1 Jul 17 '16 at 20:31
  • You should add +1 at substr and change <= to < in connect by to perfect it: SELECT ROWNUM as chunk_no,ID, SUBSTR (t1.clob_col, (ROWNUM-1)*4000 +1, 4000) AS chunk FROM t1 CONNECT BY (ROWNUM-1)*4000 < LENGTH(t1.clob_col) otherwise you will get a character duplication @pos:4000 – vmatyi Mar 18 '20 at 16:55