Re: View from CLOB chunks
From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 20 Nov 2008 12:30:54 -0800
Message-ID: <1227213030.992966@bubbleator.drizzle.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
Date: Thu, 20 Nov 2008 12:30:54 -0800
Message-ID: <1227213030.992966@bubbleator.drizzle.com>
Jonathan Lewis wrote:
> "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/
Nice.
-- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Thu Nov 20 2008 - 14:30:54 CST