Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Prabhu.

Asked: February 22, 2017 - 5:04 pm UTC

Last updated: February 23, 2017 - 3:18 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi,

using dbms_lob.substr(), I can extract upto 4k chars only, But I want to extract all characters (6k out of 6k chars). Importantly I should return char or varchar type.

Like below query

dbms_lob.substr(x, 6000, 1).

Thanks

and Connor said...

A varchar2 (in 11g) has a ceiling of 4000 bytes. So you cannot return *more* than 4000 bytes as a varchar2.

You could return *multiple* varchar2 variables, eg

select 
  dbms_lob.substr(x, 4000, 1),
  dbms_lob.substr(x, 4000, 4001),
  dbms_lob.substr(x, 4000, 8001),
  dbms_lob.substr(x, 4000, 12001)
  ...
from 
  ...


but that is exactly why clobs are there - for strings *longer* than 4000.


Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here