ORA-01704: string literal too long error update clob field

Question

Can you advice on a method of doing updates on clob fields in Oracle?

The query is a simple one and running it I get

ORA-01704: string literal too long:
Update table_name
Set clob_field=value2
Where column=condition1 and clob_field=value1

The scope is to update the value in a clob column with a new value.

Thanks

srasulic · Answer 1 · 2019-01-22T09:40:47.217

3

Is Your code part of some PLSql procedure or it is simple SQL statement? Do You pass variable "value2" as bind variable or it is quoted string inside the query? Are You on 12c or some earlier release of Oracle DB?

Generally, the most common "not obvious" issue is related to the fact that varchar2 type is limited to 4000 characters in SQL statements. If You are in PLSql procedure, the limit is 32K characters.

Can You provide code sample? The fact is that following two statements result in different behavior:

update table_name 
set clob_field=value2 
where column=condition1 
  and clob_field=value1

update table_name 
set clob_field='Some very long string.....end of very long string' 
where column=condition1 
  and clob_field='Some even longer string.....end of even longer string'

Take a look at post Error : ORA-01704: string literal too long - example how to put update in plsql block in order to achieve 32.767 character limit.

edit: Take a look at post Working with very large text data and CLOB column, too

edited Jan 22 '19 at 09:40

answered Jan 17 '19 at 19:38

srasulic

43
5

I am using plsql – Mathew Linton Jan 21 '19 at 08:17
I see. I beelave that You are trying to assign string which is larger than 32k to clob value. Any string in plsql which is longer than 32k will raise that error. You have to asign it to CLOB with `dbms_lob.append` in chunks of 32k or less. Take a look at post https://stackoverflow.com/questions/20613348/working-with-very-large-text-data-and-clob-column – srasulic Jan 22 '19 at 09:39
Thanks for the link here: https://stackoverflow.com/questions/13945710/error-ora-01704-string-literal-too-long – thebiggestlebowski Aug 09 '23 at 10:30

score 2 · Answer 2 · edited Sep 18 '20 at 14:37

As you may know, it is not possible to insert more than 4k characters at one time in a clob field with Oracle Database.

A workaround to solve this issue is to split the whole string in 2 string < 4k

Example :

create table t_test (id number, texte clob);
insert into t_test (id, texte) values(1, to_clob ('value of 3999 characters') || to_clob ('value of remaining 1001 characters'));

You can use the "Lorem ipsum" to do the test :-)

score 0 · Answer 3 · answered Aug 30 '19 at 06:52

Put your string value to a CLOB variable first:

declare
   c clob;
   s varchar2(4000);
begin

    s:=rpad('x',4000,'x');
    for i in 1..100 loop
        c:=c||s;    
    end loop;
    dbms_output.put_line( dbms_lob.getlength(c) );
    -- length of "c" is 400000 now

    update table_name set clob_field=c where id=12345;

end; 
/

ORA-01704: string literal too long error update clob field

3 Answers3