Question about use of dbms_lob.substr function

I know that the use of dbms_lob.substr() has a limit of 4000 characters.

Is there any way to pull more than that from a CLOB field? If so can you please provide example or at least guidance?

Thanks

JC
corganoAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Check out dbms_lob.copy  This will create another CLOB.
0
 
sdstuberCommented:
dbms_lob.substr has a limit of 32767  when used within pl/sql  

4000 when used within sql

the difference is due to the limits of the varchar2  type within the respective contexts


what are you trying to do?


0
 
TommySzalapskiConnect With a Mentor Commented:
Can't you just pull multiple substrings and concatenate them later?
dbms_lob.substr(field1, 4000, 1) and dbms_lob.substr(field1, 4000, 4001), etc
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
sdstuberCommented:
if you are doing that in sql  then no.

if you attempt to concatenate two 4000-character varchar2 extractions you'll exceed the 4000 varchar2 limit for sql


if you are doing that in pl/sql  then it's inefficient because you could simply extract the entire 8000 characters


v_my_8k_string := dbms_lob.substr(field1,8000,1);
0
 
slightwv (䄆 Netminder) Commented:
0
 
TommySzalapskiCommented:
Right. I meant concatenate much later, like when you output them.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.