lobs vs strings

Recently one of the emails I read at a client site (wherein the lead had recommended his team to use dbms_lob.substr in the sqls) probed me do this research and as always learning new stuff in oracle is never ending. :)

SqL>Create table test_clob (c1 clob,c2 number); Table created. SqL>ed Wrote file afiedt.buf 1* Insert into test_clob values (Rpad('Test',50000,'*'),null) SqL>/ 1 row created. SqL>ed Wrote file afiedt.buf 1* Insert into test_clob values (Rpad('Test2',100000,'*'),null) SqL>/ 1 row created. SqL>update test_clob set c2=dbms_lob.getlength(c1); 2 rows updated. SqL>commit; Commit complete. SqL>set long 30 SqL>col c1 format a30 trunc SqL>select c1,c2 from test_clob; C1 C2 ------------------------------ ---------- Test************************** 4000 Test2************************* 4000 SqL>ed Wrote file afiedt.buf 1* select c1,c2,length(c1) from test_clob SqL>/ C1 C2 LENGTH(C1) ------------------------------ ---------- ---------- Test************************** 4000 4000 Test2************************* 4000 4000 SqL>ed Wrote file afiedt.buf 1 declare 2 lv_tmp clob; 3 lv_tmp1 varchar2(4000); 4 begin 5 lv_tmp1:= Rpad('Test3',4000,'*'); 6 for i in 1..10 loop 7 lv_tmp:= lv_tmp||lv_tmp1; 8 end loop; 9 Insert into test_clob(c1) values (lv_tmp); 10 commit; 11* end; 12 / PL/SQL procedure successfully completed. SqL>update test_clob set c2=dbms_lob.getlength(c1) where c2 is null; 1 row updated. SqL>commit; Commit complete. SqL>select c1,c2,length(c1) from test_clob; C1 C2 LENGTH(C1) ------------------------------ ---------- ---------- Test************************** 4000 4000 Test2************************* 4000 4000 Test3************************* 40000 40000

first interesting thing I noticed was I couldnt use rpad function to generate a string more than 4000 chrs..Its strange that oracle silently ignores the request to create longer strings (request was made to make it as long as 100000 chrs long) but it extended only upto 4000.

Well, that takes me to using PLSQL to insert clob data.

now comes the interesting part..lets say we need to fetch only part of the data from clob..what would you use? dbms_lob.substr function or regular substr function?

SqL>ed Wrote file afiedt.buf 1 With v1 as (select &length len,&starting_chr startpos from dual) 2 select 3 length(substr(c1,startpos,len)) substr_len 4 , length(dbms_lob.substr(c1,len,startpos)) substr_lob 5* from test_clob,v1 where c2=40000 SqL>/ Enter value for length: 4000 Enter value for starting_chr: 1 old 1: With v1 as (select &length len,&starting_chr startpos from dual) new 1: With v1 as (select 4000 len,1 startpos from dual) SUBSTR_LEN SUBSTR_LOB ---------- ---------- 4000 4000 SqL>/ Enter value for length: 4001 Enter value for starting_chr: 1 old 1: With v1 as (select &length len,&starting_chr startpos from dual) new 1: With v1 as (select 4001 len,1 startpos from dual) , length(dbms_lob.substr(c1,len,startpos)) substr_lob * ERROR at line 4: ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 1 SqL>ed Wrote file afiedt.buf 1 With v1 as (select &length len,&starting_chr startpos from dual) 2 select 3 length(substr(c1,startpos,len)) substr_len 4 , dbms_lob.getlength(substr(c1,startpos,len)) substr_lob 5 -- , length(dbms_lob.substr(c1,len,startpos)) substr_lob 6* from test_clob,v1 where c2=40000 SqL>/ Enter value for length: 5000 Enter value for starting_chr: 1 old 1: With v1 as (select &length len,&starting_chr startpos from dual) new 1: With v1 as (select 5000 len,1 startpos from dual) SUBSTR_LEN SUBSTR_LOB ---------- ---------- 5000 5000

what does that tell? dbms_lob.substr cannot return more than 4K characters ..whereas substr can..

It all comes back to datatypes and why understanding them is necessary..I checked the manual and its clearly stated substr can take a clob as its input and return "clob" ..whereas dbms_lob if it accepts clob, returns varchar2 (hence limited to 4000 characters).