Re: View from CLOB chunks

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Mon, 17 Nov 2008 17:21:33 +0100
Message-ID: <49219a09$0$6931$426a74cc@news.free.fr>

"Vladimir M. Zakharychev" <vladimir.zakharychev_at_gmail.com> a écrit dans le message de news: 7f32fe4b-4819-48da-aacc-4f4b0dfdde6e_at_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


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

Does it?
SQL> set autotrace on
SQL> select * from (select level from dual connect by level <= 1000);

     LEVEL


         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
        11
        12
<snip>
       983
       984
       985
       986
       987
       988
       989
       990
       991
       992
       993
       994
       995
       996
       997
       998
       999
      1000

1000 rows selected.

Execution Plan


          0                    SELECT STATEMENT Optimizer=CHOOSE
          1                  0    VIEW
          2                  1     CONNECT BY (WITHOUT FILTERING)
          3                  2      TABLE ACCESS (FULL) OF 'DUAL'

Statistics


          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
       6806  bytes sent via SQL*Net to client
        482  bytes received via SQL*Net from client
         11  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       1000  rows processed

SQL> select * from v$version where rownum=1; BANNER



Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production

1 row selected.

Regards
Michel Received on Mon Nov 17 2008 - 10:21:33 CST

Original text of this message