• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 976
  • Last Modified:

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
0
corgano
Asked:
corgano
  • 2
  • 2
  • 2
2 Solutions
 
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
 
TommySzalapskiCommented:
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
 
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
slightwv (䄆 Netminder) Commented:
Check out dbms_lob.copy  This will create another CLOB.
0
 
slightwv (䄆 Netminder) Commented:
0
 
TommySzalapskiCommented:
Right. I meant concatenate much later, like when you output them.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now