oracle 显示 4000 多个字符的 CLOB 列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7357999/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Displaying CLOB Column with 4000+ characters
提问by pein Asd
I have this CLOB column and I need to display it using a select statement.
我有这个 CLOB 列，我需要使用 select 语句显示它。
I used DBMS_LOB.SUBSTRto convert it to varchar2:
我曾经DBMS_LOB.SUBSTR将其转换为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?
我的问题是我的一些 CLOBS 包含超过 4000 个字符。我怎样才能显示它...任何想法/建议？
thanks a lot..
多谢..
回答by Kevin Burton
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
或者，如果您的系统中可能存在 clob 的最大大小限制，您可以硬编码返回的文本列数
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
回答by Vincent Malgrat
VARCHAR2 can only be 4000 bytes long when accessed with SQL. If you want to work with a CLOB larger than 4000 bytes, you can't convert it to VARCHAR2, you have to work it as a CLOB.
使用 SQL 访问时，VARCHAR2 的长度只能为 4000 字节。如果您想使用大于 4000 字节的 CLOB，则无法将其转换为 VARCHAR2，您必须将其作为 CLOB 使用。
What tool do you use?
你用什么工具？
Most tools/languages can work with CLOB natively. Just select the column:
大多数工具/语言都可以在本地使用 CLOB。只需选择列：
select T1.CLOB_COL from T1
