ORA-01704: String Literal Too Long. Cause: String Literal is Longer Than 4000 Characters

A text literal can have a maximum length of 4000 bytes.  In order to update a column greater than 4K, bind variables must be used and in the case of lobs if the data set is greater than 32k, use DBMS_LOB for piece wise manipulation.



Use a CLOB variable for data > 4k and < 32K:


drop table clobtab;
create table clobtab (id number, clobcol clob);
insert into clobtab values (1, 'Hello');
commit;

select dbms_lob.getlength(clobcol) from clobtab;

DBMS_LOB.GETLENGTH(CLOBCOL)
---------------------------
                          5

declare
vClobVal CLOB := rpad('X', 32767, 'Y');
begin
update CLOBTAB set CLOBCOL = vClobVal
where id = 1;
end;
/

select dbms_lob.getlength(clobcol) from clobtab;

DBMS_LOB.GETLENGTH(CLOBCOL)
---------------------------
                      32767