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?


Who is Participating?
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Check out dbms_lob.copy  This will create another CLOB.
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?

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
Get your problem seen by more experts

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

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);
slightwv (䄆 Netminder) Commented:
Right. I meant concatenate much later, like when you output them.
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.