HOWTO: Workaround the lack of SUBSTRB in DBMS_LOB
Last updated on JULY 05, 2017
Applies to:PL/SQL - Version 220.127.116.11 and later
Information in this document applies to any platform.
As such, using DBMS_LOB.SUBSTR in a SQL statement and passing 4000 for the amount parameter in a database with multi-byte characterset may result in the following error, if the substring contains multibyte characters, as the 4000 byte limit will be exceeded.
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
The behavior can be reproduced with the following:
The behavior can be avoided by passing a smaller number for the amount parameter to DBMS_LOB.SUBSTR, but it is difficult to gauge what number to pass if you want the maximum amount of data as it depends upon how many multibyte characters there are in the string.
The following enhancement request has been logged, but currently not implemented:
<Bug 7525960> : CONSIDER DBMS_LOB.SUBSTRB/LENGTHB STYLE FUNCTIONS FOR CLOBS
The goal of this note is to provide a potential workaround to get as much data as possible without exceeding the 4000 byte limit.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms