CODEWITHSUNDEEP
sqlxmloracle-databaseoracle11g
Nik
Nik

Reputation: 140

ORA-19011: Character string buffer too small - Is there a character limit on Regex_replace buffer limit in Oracle?

I am getting the following error when I try to execute the below code.

ORA-19011: Character string buffer too small

SELECT   filecontent
            FROM
      (SELECT  XMLTYPE(REGEXP_REPLACE(filecontent, '<Transaction .*>', '<Transaction>')) filecontent
       FROM example_table where somecondition = true)
       ) xmlContent

filecontent is a column whose type is XMLTYPE. We are storing a XML file in this column.

This error occurs if the xml file contents is > 4000 characters. If any of the the xml file has < 4000 characters this error doesn't occur. We are in Oracle 11G. So as per the documentation we should be able to store 4GB of content in the filecontent column. I am thinking the problem is REGEXP_REPLACE function which tries to read the file contents where we are facing this issue with the buffer. I may be wrong. Any help in this regard is much appreciated.

Upvotes: 2

Views: 5666

Answers (1)

Mr. Llama
Mr. Llama

Reputation: 20889

It appears that Oracle is attempting to convert either the filecontent column or the return value to CHAR or VARCHAR2 which has a size limit of 4000.

The expected input data types for REGEXP_REPLACE are CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The return type rule is: "The function returns VARCHAR2 if the first argument is not a LOB and returns CLOB if the first argument is a LOB."

If you can convert your filecontent column to CLOBs, REGEXP_REPLACE should work correctly and return a CLOB data type.

Upvotes: 1

Related Questions