All geek questions in one place

Mapping a 4000-character CLOB column

I have this CLOB column and I need to display it using the 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 that some of my CLOBS contain more than 4000 characters. How can I display it ... any idea / suggestion?

Many thanks.

+6
source share
3 answers

I think you could display the pieces as separate lines?

 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 limit on the maximum size that may be on your system, you can hard copy the number of returned text columns.

 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
+6
source

VARCHAR2 can be 4000 bytes long when accessing SQL. If you want to work with CLOBs larger than 4000 bytes, you cannot convert it to VARCHAR2, you must work with it as CLOB.

Which tool do you use?

Most tools / languages ​​can work with CLOB natively. Just select a column:

 select T1.CLOB_COL from T1
0
source

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

This one solved my problem. Thanks for the share.

0
source

Source: https://habr.com/ru/post/896944/

More articles:


All Articles