1

I have the following error with an INSERT statement with a very large string:

INSERT INTO MYTABLE (CLOB_COLUMN) VALUES ('string longer than 4000 characters');

This returns the following error:

ORA-01704: string literal too long

I've tried even the function TO_CLOB:

INSERT INTO MYTABLE (CLOB_COLUMN) VALUES (TO_CLOB('string longer than 4000 characters'));

The error is the same.

How to insert a large string in a CLOB column without concatenating the string into chunks, avoiding the error ORA-01704?

Improve this question
8
  • 2
    to_clob('string less') || to_clob(' than 4000 characters'). Or use external program and pass clob as clob. This is described in docs: A text literal can have a maximum length of 4000 bytes if the initialization parameter MAX_STRING_SIZE = STANDARD, and 32767 bytes if MAX_STRING_SIZE = EXTENDED Commented Apr 6, 2023 at 8:59
  • 1
    "without concatenating" - you can't avoid the limit on literal length, no. As you're presumably copying and pasting that long string from somewhere, can you make the source available as a text file and use SQL*Loader or an external table instead of using a manual insert? Or you can use an external tool to split the string into chunks for you, but that's still a pain. Commented Apr 6, 2023 at 9:01
  • Other databases don't have this limitation. I don't want to split the string. I want to insert the string as long as it is. Commented Apr 6, 2023 at 9:07
  • You can use PL/SQL and DECLARE v_str VARCHAR2(32000) := 'your 32k string'; BEGIN INSERT INTO MYTABLE (CLOB_COLUMN) VALUES (TO_CLOB(v_str)); END; but that just moves the limit from 4k to 32k. Commented Apr 6, 2023 at 9:08
  • 1
    "I don't want to split the string. I want to insert the string as long as it is." Then don't use literals. Pass the value as a CLOB bind parameter from the program you are using to access the database into a parameterised query INSERT INTO MYTABLE (CLOB_COLUMN) VALUES (:your_bind_variable) or load the value from a file. Commented Apr 6, 2023 at 9:09
Related questions
54
Error : ORA-01704: string literal too long
0
ORA-01704: string literal too long
0
Is there any way using generic sql to insert a string longer than 4000 chars into a CLOB
Load 5 more related questions Show fewer related questions

0

Reset to default

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.