According to the SQL manual, about sql string functions like replace and regexp_replace work like this:
For functions that return CHAR or VARCHAR2, if the length of the return value exceeds the limit, then Oracle Database truncates it and returns the result without an error message.
This was so in the oldest version I have tested, 10 as well in the most recent one via livesql.
By the way, the result on the newer databases depends on the setting of max_string_size, if it is switched to EXTENDED, then the limit is not 4000 but 32767.
Below I have replace function returning 8000 bytes by replacing 4000 times "1" by 4000 times "12"
select length(replace(lpad('1',4000,'1'),'1','12')) from dual;
This returns 4000, (2000 times '12'), and thus 2000 times '12' disappeared without informing us
Moreover, it does not replace and truncate like it suggests, if the replacement string does not fit at the end, then the search text is replaced by null, returning a shorter string than 4000.
See for yourself in livesql.
So why does everything else, like variables in plsql, listagg or string concatenation ( || ) return an error ? Is this a form of backward compatibility ?
If you have to replace a string by something bigger, How can we prevent throwing away data ? Shoul
There is so little information I found about this issue. No-one seems to know, and it is surely not what you expect to happen.
Kind regards,
Dirk Vanhaute