CODEWITHSUNDEEP
sqloracle-databaseoracle11g
icerabbit
icerabbit

Reputation: 89

How to convert CLOB into multiple VARCHAR2 rows?

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?

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;
                            /

Upvotes: 1

Views: 8916

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65228

You may use

dbms_lob.substr( clob_column, for_how_many_bytes, from_which_byte ) function

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;

Upvotes: 1

MT0
MT0

Reputation: 167982

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

or

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

Upvotes: 3

Related Questions