I had a field that was varchar2(4000) and changed it to CLOB to allow more than 4000 characters. When I try to do and update to the table, I still get this error when updating this field with more than 4000 characters.
Does the CLOB field support using the update statement? This is being done using straight SQL through a ColdFusion web application. Are there any attributes that need to be set for field length when using CLOB? Any help would be greatly appreciated.
CLOB fields do support the SQL update statement (the statement
update xyztable set clob_col='this is a modified clob col val';
will work as expected.
And you do not need to set any length attributes for CLOB fields either
With PL/SQL you can populate a CLOB col with a max value of 32767 (using the max length supported for char/varchar2 types in pl/sql). If you need to populate the CLOB col with larger values you will have to use the DBMS_LOB package functions WRITE or WRITEAPPEND.
You get the error because you are executing a sql statement.
In SQL the max of varchar2 is 4000 bytes and hence the error. Modify your code snippet to something like the following and it should work. (it becomes a pl/sql program).
myClobVar varchar2(32767) := 'string >4000 and <32767 bytes';
update tableWithClob set clobcol = myClobVar;