Re: View from CLOB chunks

From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: Sun, 16 Nov 2008 23:00:45 -0800 (PST)
Message-ID: <7f32fe4b-4819-48da-aacc-4f4b0dfdde6e@z28g2000prd.googlegroups.com>

On Nov 14, 9:55 pm, yuri.de..._at_gmail.com wrote:
> Hello,
> Is it possible to creare a view that will split single CLOB column
> into multiple chunks of VARCHAR2 something like this:
>
> TABLE:
> ---------------------------
> ID       INTEGER
> DATA CLOB
>
> into VIEW:
> --------------------------------------------------
> ID               INTEGER
> CHUNK_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,
       myclobtable

 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. 10.2.0.4 doesn't have such restriction, but 9.2.0.8 does.

Hth,

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