如何将CLOB转换为多个VARCHAR2行？

I have a single column (COMMENTS) that is currently a CLOB. Is there a way I can split the COMMENTS CLOB column into separate (4k) VARCHAR2 columns in oracle SQL?

我有一个列（COMMENTS），目前是一个CLOB。有没有办法可以将COMMENTS CLOB列拆分为oracle SQL中的单独（4k）VARCHAR2列？

Thanks in advance

提前致谢

Here is my attempt below:

以下是我的尝试：

create or replace procedure longsubstr(p_rowid in rowid,
                                       comment in comment)
                            return varchar2
                            as l_tmp long;
                            begin
                            select COMMENT into l_tmp from table_name 
                                                     where rowid = p_rowid;
                            return substr(l_tmp, p_form, p_for);
                            end;
                            /

2 个解决方案

#1

SQLFIDDLE:

SQLFIDDLE：

SELECT DBMS_LOB.SUBSTR( t.comments, 4000, l.COLUMN_VALUE )
FROM   table_name t
       CROSS JOIN
       TABLE(
         CAST(
           MULTISET(
             SELECT LEVEL * 4000 - 3999
             FROM   DUAL
             CONNECT BY LEVEL * 4000 - 3999 <= DBMS_LOB.GETLENGTH( t.comments )
           ) AS SYS.ODCINUMBERLIST
         )
       ) l

要么

WITH positions ( comments, pos ) AS (
  SELECT comments,
         1
  FROM   table_name
UNION ALL
  SELECT comments,
         pos + 4000
  FROM   positions
  WHERE  pos + 4000 <= DBMS_LOB.GETLENGTH( comments )
)
SELECT DBMS_LOB.SUBSTR( comments, 4000, pos ) AS split_comment
FROM   positions

#2

You may use

你可以用

dbms_lob.substr( clob_column, for_how_many_bytes, from_which_byte ) function

dbms_lob.substr（clob_column，for_how_many_bytes，from_which_byte）函数

for this :

为了这：

declare
    type typ_comment is table of varchar2(4000);
    v_varchar typ_comment := typ_comment();
    v_clob    table_name.comments%type;
    k         number;
    j         number := 4000;
begin
    select comments into v_clob from table_name where rowid = '&p_rowid'; -- like AAAS9BAAEAAAAEeAAA without quotation
    k := ceil(dbms_lob.getlength(v_clob)/j);
    v_varchar.extend(k); 
  for i in 1..k 
  loop
    v_varchar(i):= dbms_lob.substr( v_clob, j, 1 + j * ( i - 1 ) );
  end loop;
end;

#1