Re: View from CLOB chunks

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:4POdnYz0iJmmor7UnZ2dnUVZ8r2dnZ2d_at_bt.com...
> <yuri.dench_at_gmail.com> wrote in message
> news:8a22f11a-c250-4dd4-adff-9d9e7145d157_at_e1g2000pra.googlegroups.com...
>> 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
>
> You could write a pipelined function that takes the ID value
> for a row, and pipes back a list of (line_number, clob_chunk).
>
> You could then create a view that uses this function in a lateral
> join with the clob table.
>

I've just done a quick write-up and demonstration on my blog: http://jonathanlewis.wordpress.com/2008/11/19/lateral-lobs/

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html