Link to home
Start Free TrialLog in
Avatar of corgano
corgano

asked on

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
Avatar of Sean Stuber
Sean Stuber

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?


SOLUTION
Avatar of TommySzalapski
TommySzalapski
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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);
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Right. I meant concatenate much later, like when you output them.