Issue

When performing an UPDATE after replacing a specific column in a table using the REGEXP_REPLACE function, a TBR-11050 or JDBC-11050 error occurs.

SQL> UPDATE T_QUESTION
SET QUESTION_ANSWER =
    REGEXP_REPLACE(QUESTION_ANSWER,
                   '"monitor/MNT21_Question.do',
                   '/monitor/MNT21_Question.do')
WHERE DBMS_LOB.INSTR(QUESTION_ANSWER, '"monitor/MNT21_Question.do') > 0;
;

TBR-11050: The length of the source document used in the regular expression function is too long.

Cause

Exceeding Data Size of LOB Column

The REGEXP_REPLACE function can only process up to 4MBYTES of data per entry.

However, this issue occurs because certain data in the QUESTION_ANSWER column in the UPDATE statement exceeds 4MBYTES.

• Upon checking the T_QUESTION table, the QUESTION_ANSWER column type is confirmed as CLOB.
• Since VARCHAR in TIBERO can only be declared up to a maximum of 65,53288 BYTES, it can be inferred that this table uses a LOB TYPE even without checking the DDL.
• Because the DBMS_LOB function is used in the UPDATE statement, it confirms that the type is LOB.

Checking Targets Exceeding 4MBYTES 

CLOB stores data at 2BYTES per character, so a query is used to check if any data exceeds 4MBYTES. 

The DBMS_LOB.GETLENGTH(LOB column) function returns the length of the target LOB data.
At this time, the length is returned either in BYTES or characters depending on the LOB data type entered.

Also, 0 (for BLOB data) or blank (for CLOB data) characters are included in the length of the target LOB data.

SELECT QUESTION_SEQ
       , DBMS_LOB.GETLENGTH(QUESTION_ANSWER)
FROM APPLUSER.T_QUESTION
ORDER BY 2 DESC;

QUESTION_SEQ DBMS_LOB.GETLENGTH(QUESTION_ANSWER)
-------------------------------
BMM134846073430000052A 2,604,807
BMM134846073496666718A 2,403,748
BMM134846073563333384A 2,212,759
BMM134846073574444495A 2,185,353

As a result of the function execution, it was confirmed that there are four data entries exceeding 4MBYTES.

2,185,353 * 2BYTES / 1024 / 1024 = 4.16MBYTES

Solution

Parameter Modification

Currently, the _EX_REGEXP_CLOB_MAX_LEN parameter is set to the default value of 2097152 (2MBYTES).

You can extend the processing range of REGEXP_REPLACE by increasing it up to 4194304 (4MBYTES).

SQL> select NAME, DFLT_VALUE, IS_OPTIONAL from vt_parameter where NAME = '_EX_REGEXP_CLOB_MAX_LEN';

NAME                      DFLT_VALUE           IS_OPTIONAL
------------------------- -------------------- -----------
_EX_REGEXP_CLOB_MAX_LEN   2097152                        1

SQL> alter system set _EX_REGEXP_CLOB_MAX_LEN = 4194304;

System altered.

SQL> UPDATE T_QUESTION
SET QUESTION_ANSWER =
    REGEXP_REPLACE(QUESTION_ANSWER,
                   '"monitor/MNT21_Question.do',
                   '/monitor/MNT21_Question.do')
WHERE DBMS_LOB.INSTR(QUESTION_ANSWER, '"monitor/MNT21_Question.do') > 0;
;

1 row updated.