Skip to Main Content

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

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.


We're not taking comments currently, so please try again later if you want to add 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 channel and Chris's channel 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