HOWTO: Workaround the lack of SUBSTRB in DBMS_LOB
(Doc ID 1571041.1)
Last updated on JUNE 21, 2022
Applies to:
PL/SQL - Version 11.2.0.1 and laterInformation in this document applies to any platform.
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
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
Goal |
Solution |
References |