Error : ORA-01704: string literal too long

Question

While I try to set the value of over 4000 characters on a field that has data type CLOB, it gives me this error :

ORA-01704: string literal too long.

Any suggestion, which data type would be applicable for me if I have to set value of unlimited characters although for my case, it happens to be of about 15000 chars.

Note : The long string that I am trying to store is encoded in ANSI.

score 61 · Accepted Answer · answered Dec 19 '12 at 06:56

61

What are you using when operate with CLOB?

In all events you can do it with PL/SQL

DECLARE
  str varchar2(32767);
BEGIN
  str := 'Very-very-...-very-very-very-very-very-very long string value';
  update t1 set col1 = str;
END;
/

Proof link on SQLFiddle

answered Dec 19 '12 at 06:56

knagaev

2,897
16
20

Thank you, but CI is not supporting these procedure stuffs http://stackoverflow.com/questions/13946641/error-this-feature-is-not-available-for-the-database-you-are-using#comment19232999_13946641 – hsuk Dec 19 '12 at 07:00
I guess you can wrap this code in a procedure and Codeigniter can execute it. Sorry but I don't have Codeigniter for checking. Look here [link](http://stackoverflow.com/a/7930092/1910242) – knagaev Dec 19 '12 at 08:27
Did it, but could I define that size of varchar2, check this link, http://stackoverflow.com/questions/13947222/error-ora-06553-pls-306-wrong-number-or-types-of-arguments-in-call-to-proced/13947420#13947420 – hsuk Dec 19 '12 at 08:38
Will [this info (http://www.oracle.com/technetwork/articles/fuecks-lobs-095315.html)](http://www.oracle.com/technetwork/articles/fuecks-lobs-095315.html) be useful for you? – knagaev Dec 19 '12 at 09:16
I don't know what I have changed somewhere, but this is working fine now. Thanks. But still I am not clear why the same thing did not work from straight query. Is it always we have to do like this for storing string having 4000 and more characters ? – hsuk Dec 20 '12 at 04:46
@K u s H Yes, it's a simple limit of realization. – knagaev Dec 20 '12 at 06:12

score 46 · Answer 2 · answered May 13 '15 at 15:29

46

Try to split the characters into multiple chunks like the query below and try:

Insert into table (clob_column) values ( to_clob( 'chunk 1' ) || to_clob( 'chunk 2' ) );

It worked for me.

answered May 13 '15 at 15:29

Ameer Tamboli

1,218
12
20

1

I confirm, this one worked for me even in simple insert statement to clob column – RLapinski Dec 19 '18 at 15:01
this one works, but Is there any disadvantage using to_clob() ? – bzuo Mar 04 '21 at 09:19

score 3 · Answer 3 · answered Dec 24 '19 at 14:05

To solve this issue on my side, I had to use a combo of what was already proposed there

DECLARE
  chunk1 CLOB; chunk2 CLOB; chunk3 CLOB;
BEGIN
  chunk1 := 'very long literal part 1';
  chunk2 := 'very long literal part 2';
  chunk3 := 'very long literal part 3';

  INSERT INTO table (MY_CLOB)
  SELECT ( chunk1 || chunk2 || chunk3 ) FROM dual;
END;

Hope this helps.

kazzikazzi · Answer 4 · 2015-08-06T15:27:35.157

2

The split work until 4000 chars depending on the characters that you are inserting. If you are inserting special characters it can fail. The only secure way is to declare a variable.

edited Aug 06 '15 at 15:27

answered Aug 03 '15 at 19:11

kazzikazzi

57
5

score 1 · Answer 5 · answered Feb 20 '22 at 11:28

Though its a very old question but i think sharing experience still might help others:

Large text can be saved in a single query if we break-down it in chunks of 4000 bytes/characters by concatinating them using '||'

Running following query will tell you:

Required Number of chunks containing 4000 bytes
Remaining bytes

Since, in given example you are trying to save text contining 15000 bytes (characters), so,

select 15000/4000 chunk,mod(15000,4000) remaining_bytes from dual;

Result:

That means, you need to concatenate 3 chunks of 4000 bytes and one chunk of 3000 bytes, so it would be like:

INSERT INTO <YOUR_TABLE> 
VALUES (TO_CLOB('<1st_4K_bytes>') || 
        TO_CLOB('<2nd_4K_bytes>') || 
        TO_CLOB('<3rd_4K_bytes>') || 
        TO_CLOB('<last_3K_bytes>)');

shajahan · Answer 6 · 2022-11-14T17:00:41.090

0

create a function that return a clob

create  function  ret_long_chars return clob is
begin

 return to_clob('put here long characters');

end;

update table set column = ret_long_chars;

edited Nov 14 '22 at 17:00

answered Nov 06 '22 at 07:06

shajahan

101
1
4

score -1 · Answer 7 · edited Dec 02 '20 at 11:34

-1

INSERT INTO table(clob_column) SELECT TO_CLOB(q'[chunk1]') || TO_CLOB(q'[chunk2]') ||
            TO_CLOB(q'[chunk3]') || TO_CLOB(q'[chunk4]') FROM DUAL;

edited Dec 02 '20 at 11:34

Elletlar

3,136
7
32
38

answered Dec 01 '20 at 17:59

Troy Oliver

1

Nolyurn · Answer 8 · 2022-09-15T07:49:55.587

-1

Accepted answer did not work for me in sql developper but combination of this answer and another one did :

DECLARE
  str varchar2(32767);
BEGIN
  update table set column = to_clob('Very-very-...-very-very-very-very-very-very long string value');
END;
/

edited Sep 15 '22 at 07:49

answered Jun 02 '22 at 09:29

Nolyurn

568
4
17

Error : ORA-01704: string literal too long

8 Answers8

Linked

Related