HOWTO: Workaround the lack of SUBSTRB in DBMS_LOB (Doc ID 1571041.1)

Last updated on JUNE 21, 2022

Applies to:

Goal

DBMS_LOB does not include a SUBSTRB function, similar to what is found in SQL.  

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.

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.