How to perform a search and replace on a NCLOB?

Question

With an Oracle 11g database, and a NCLOB column in a table, I'm trying to replace some text with another text. At a high level, it's pretty simple, I did it successfully on a SQL Server version of a SQL script, but with Oracle, things are getting complicated, mainly because of data in NCLOB column can be easily more than 46k in length.

With an error ORA-22835 (Buffer too small for CLOB to CHAR or BLOB to RAW conversion), the action, as suggested, is not possible because of variable length of data, and chunking this data with SUBSTR could split in middle of my "search string" to be found in data.

I'm looking for a straightforward and simple solution I could use in a SQL script.

Here is an example of script I'm using with SQL Server:

DECLARE @replacestring NVarChar(MAX) = '0D0D000402175300008950.. very long string 46k+ in length ..1CA68848EEB58360000000049454E44AE426082'

DECLARE @oldFingerprintStart NVarChar(MAX) = '0D0D0004002BA80000FFD8FFE000104A46494600010201004800480000FFE10B304578696600004D4D002A0000000800070';
DECLARE @oldFingerprintEnd NVarChar(MAX) = '02800A002800A002800A002800A002800A002800A002800A002800A002800A002800A002800A002800A002800A002803FFD9';

UPDATE Table1
SET datacolumn =
    CONCAT(
        SUBSTRING(datacolumn, 0, CHARINDEX(@oldFingerprintStart, datacolumn)), 
        @replacestring,
        SUBSTRING(datacolumn, CHARINDEX(@oldFingerprintEnd, datacolumn) + LEN(@oldFingerprintEnd), LEN(datacolumn) - (CHARINDEX(@oldFingerprintEnd, datacolumn) + LEN(@oldFingerprintEnd))+1)
    )
WHERE CHARINDEX(@oldFingerprintStart, datacolumn) > 0
    AND CHARINDEX(@oldFingerprintEnd, datacolumn) > 0

score 0 · Answer 1 · answered Jun 29 '13 at 17:59

You can find nice and detailed explanation here, but as for my experience (and as it is stated in Oracle documentation), standard REPLACE function works on NCLOB fields the same way, as on VARCHAR2.

UPDATE a_table 
SET that_field = REPLACE(that_field, 'XYZ', 'ABC') 
WHERE CONTAINS(that_field, 'XYZ') > 0

And this way you will avoid any trouble with buffer overflow, as there is none to take care of.

How to perform a search and replace on a NCLOB?

1 Answers1