Please see my other blog for Oracle EBusiness Suite Posts - EBMentors

Search This Blog

Note: All the posts are based on practical approach avoiding lengthy theory. All have been tested on some development servers. Please don’t test any post on production servers until you are sure.

Monday, October 20, 2014

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

No comments: