用 4000+ 显示 CLOB 列人物

发布于 2024-12-03 20:26:31 字数 244 浏览 4 评论 0原文

我有这个 CLOB 列，我需要使用 select 语句显示它。

我使用 DBMS_LOB.SUBSTR 将其转换为 varchar2：

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

我的问题是我的某些 CLOBS 包含超过 4000 个字符。我怎样才能显示它......有什么想法/建议吗？

多谢..

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

慕巷 2024-12-10 20:26:31

我想你可以将块显示为单独的行？

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)

或者，如果系统中 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

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

回复收藏 0 原文

~没有更多了~