We store queries for some of our processes in a varchar2 field of an Oracle table. Heretofore, those queries were always < 4000 characters. Recently, we have had occasion to store queries that are greater than 4000 characters. This causes a problem because our database, though 12c, does not support extended varchar2 fields.
My strategy was to create a before insert / update trigger that contains code like the following:
:new.queryClob = :new.queryString;
:new.queryString := '';
where queryClob is a CLOB field and queryString is a varchar2 field.
This works as long as queryString < 4000 characters. If queryString > 4000 characters, then Oracle throws an exception and the trigger is not executed. Is it possible to execute the trigger anyway?
Thanks.
Unfortunately no, because in order to be able to reference " :new.queryString" we need to be able to store the incoming data in that field...and of course, it wont fit.
Is there any reason why you can't "bite the bullet" so to speak, and change 'queryString' to clob ?
The alternative would be to have a wrapper around the code, where people call a routine (plsql perhaps) to add a row, and you do the translation there.
But I would just convert to clob, or look at going with max_string_size = extended.