Re: View from CLOB chunks

From: Vladimir M. Zakharychev <>
Date: Sun, 16 Nov 2008 23:00:45 -0800 (PST)
Message-ID: <>

On Nov 14, 9:55 pm, wrote:
> Hello,
> Is it possible to creare a view that will split single CLOB column
> into multiple chunks of VARCHAR2 something like this:
> ---------------------------
> ID       INTEGER
> into VIEW:
> --------------------------------------------------
> ID               INTEGER
> DATA         VARCHAR(1000 char)
> Thanks, Yuri

Yes, it is possible. You just need a subquery that will generate sequence numbers you can use as chunk_id. For example, something like this will give you up to 1000 chunks from every CLOB:

with sqgen as
  select level as val
    from sys.dual
    connect by level <= 1000
select id

       ,sqgen.val as chunk_id
       ,dbms_lob.substr(data, 1000, 1+(sqgen.val-1)*1000) as data
  from sqgen,

 where sqgen.val <= 1+dbms_lob.getlength(data)/1000

Note that on some older Oracle versions unfiltered CONNECT BY can't go beyond 100 levels due to some internal limits. doesn't have such restriction, but does.


   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm) Received on Mon Nov 17 2008 - 01:00:45 CST

Original text of this message