Wednesday, December 26, 2007

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).



17 comments:

Anonymous said...

Genial dispatch and this enter helped me alot in my college assignement. Say thank you you on your information.

Anonymous said...

[url=http://www.payloansonline.com]payday advance[/url]
This is the best way to get all your health products online like green coffee, african mango, phen375 and others. Visit now

[url=http://www.amazines.com/article_detail.cfm/5247759?articleid=5247759]Oxyhives[/url]

Anonymous said...

[url=http://garciniacambogiaselectz.weebly.com]
where to buy garcinia cambogia[/url] is the superior fat fervid force out readily obtainable in superstore contemporarily a days. Yield upto 10 kg in 1 month. garcinia cambogia select

Anonymous said...

Your style is really unique compared to other folks I've read stuff from. Many thanks for posting when you've got the
opportunity, Guess I will just bookmark this site.


My website Wegith Loss Products

Anonymous said...

Hi there, I found your blog by way of Google while looking for a similar
matter, your website came up, it looks great. I've bookmarked it in my google bookmarks.
Hello there, just changed into alert to your blog through Google, and found that it is truly informative. I'm gonna watch out
for brussels. I will be grateful if you proceed this in future.
A lot of people shall be benefited from your writing. Cheers!



my blog post :: muscle builder

Anonymous said...

Pretty part of content. I just stumbled upon your weblog and in accession capital to claim that I get actually enjoyed account your blog posts.
Any way I will be subscribing to your augment and even I success you get entry to persistently fast.


Also visit my page muscle building tips

Anonymous said...

I'm really enjoying the design and layout of your site. It's a very easy on the eyes which makes it much more enjoyable for me to come here and
visit more often. Did you hire out a developer to
create your theme? Fantastic work!

Here is my web-site Wrinkle cream

Anonymous said...

I don't know whether it's just me or if perhaps everyone
else experiencing issues with your site. It appears as
though some of the text on your content are running off the screen.
Can someone else please comment and let me know if this is
happening to them too? This could be a problem with my web browser because I've had this happen previously. Thank you

Look at my weblog; Xtrasize Review

Anonymous said...

Hey there! Do you know if they make any plugins to help with Search Engine Optimization?
I'm trying to get my blog to rank for some targeted keywords but I'm
not seeing very good success. If you know of any please share.
Many thanks!

My blog post :: Auravie Skin Care Review

Anonymous said...

Hi, I do think this is an excellent site. I stumbledupon it ;) I
may revisit once again since i have saved as a favorite it.

Money and freedom is the best way to change, may you be rich and continue
to guide other people.

My blog post - Profit web system reviews

Anonymous said...

Taste is the key here because you want something that you’re going to be able to drink every single day.

While they are struggling to pack on any muscle at all, you.
The reason is that too often people consume too much
protein at one time and most of it goes to waste.


Look into my web blog Power Pump XL

Anonymous said...

Thanks for finally talking about > "lobs vs strings" < Loved it!

Here is my homepage - Adorna Reviews

Anonymous said...

Thank you a bunch for sharing this with all people you actually recognize what you're speaking approximately! Bookmarked. Please additionally talk over with my site =). We may have a hyperlink change agreement between us

My website ... green coffee reviews

Anonymous said...

Fine way of describing, and fastidious post to take facts
concerning my presentation subject matter, which i am going to present in institution of higher education.



Check out my weblog :: Pay day loan lenders

Anonymous said...

WOW just what I was looking for. Came here by searching for
coffee maker

my web blog - Coffee Pure Cleanse Weight Loss\Coffee Pure Cleanse Diet

Anonymous said...

Hello! I've been reading your blog for a long time now and finally got the courage to go ahead and give you a shout out from Porter Tx! Just wanted to say keep up the excellent job!

Here is my website Home income kit

Anonymous said...

Do you have a spam issue on this website; I also am a blogger,
and I was curious about your situation; we have created some nice procedures and we are
looking to exchange methods with other folks, why not shoot me
an email if interested.

Take a look at my site: last longer durning sex