View from CLOB chunks
yuri....@gmail.com
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
DA Morgan
Use DBMS_LOB.SUBSTR
http://www.psoug.org/reference/dbms_lob.html
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damo...@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Vladimir M. Zakharychev
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
Michel Cadot
"Vladimir M. Zakharychev" <vladimir.z...@gmail.com> a écrit dans le message de news:
7f32fe4b-4819-48da...@z28g2000prd.googlegroups.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
Vladimir M. Zakharychev
> "Vladimir M. Zakharychev" <vladimir.zakharyc...@gmail.com> a écrit dans le message de news:
> 7f32fe4b-4819-48da-aacc-4f4b0dfdd...@z28g2000prd.googlegroups.com...
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,
Palooka
is talking about.
But what I don't understand is why you want to put it into the database
as a CLOB, then pull it out again split into chunks of varchar2. If
that's what is needed, why not store it as a child table containing
varchar2s in the first place?
I suspect that the design may benefit from a revisit.
Palooka
Jonathan Lewis
news:8a22f11a-c250-4dd4...@e1g2000pra.googlegroups.com...
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.
--
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
Jonathan Lewis
"Jonathan Lewis" <jona...@jlcomp.demon.co.uk> wrote in message
news:4POdnYz0iJmmor7U...@bt.com...
> <yuri....@gmail.com> wrote in message
> news:8a22f11a-c250-4dd4...@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/
DA Morgan
> "Jonathan Lewis" <jona...@jlcomp.demon.co.uk> wrote in message
> news:4POdnYz0iJmmor7U...@bt.com...
>> <yuri....@gmail.com> wrote in message
>> news:8a22f11a-c250-4dd4...@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.