Re: View from CLOB chunks

On Nov 17, 7:21 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> "Vladimir M. Zakharychev" <vladimir.zakharyc..._at_gmail.com> a écrit dans le message de news:
> 7f32fe4b-4819-48da-aacc-4f4b0dfdd..._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

It does if you don't use the inline view trick. Note that my suggested query doesn't use it so the query as is will not work as expected in 9.2.0.8. The inline view seems to work around this and other issues in 9.2, but is not necessary in 10g and later.

Regards,

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