All, I have defined a column c1 as CLOB in Oracle. I am trying to insert a large text using the sql query insert into t1 values(<large text>); This query is giving the error as SQL Error: ORA-01704: string literal too long. When I divide the into multiple chunks and insert using the sql command: insert into values (to_clob(<chunk1>) || to_clob(<chunk2>));, is successful.
I did not understand why defining the column as CLOB and trying to insert a large text failed?
Is dividing the large text into smaller chunks the only solution?
2 Answers
I am going to assume that your query is:
insert into t1 ( clob_column ) values( '< ... more than 4000 characters .... >' );
'' delimits a string literal that will be a CHAR or VARCHAR2 data type; it does not indicate that the literal is a CLOB data type. Therefore if you try to put more than 4000 bytes of characters into it then it will fail as the literal (denoted by the surrounding '' characters) will be larger than its maximum size.
When I divide it into multiple chunks and insert using the SQL command:
INSERT INTO t1 ( clob_column ) values (to_clob('<chunk1>') || to_clob('<chunk2>'));is successful.
That is because none of your string literals contain more than 4000 bytes of characters.
Alternatively, you can use:
INSERT INTO t1 ( clob_column )
VALUES ( EMPTY_CLOB() || '<chunk1>' || '<chunk2>' );
Without the EMPTY_CLOB(), the concatenation operator || would concatenate the two string literals into a single string literal (and fail as the resultant literal would have more than 4000 bytes); when using EMPTY_CLOB() you are telling the SQL parser that the resultant expression from the concatenation operator should be a the CLOB data type of the left-hand operand and not a CHAR or VARCHAR2 data type of the literal. The same is true when you wrap the literals in the TO_CLOB function then the concatenation will result in a CLOB result.
Is dividing the large text into smaller chunks the only solution?
No, you can:
- load the
CLOBfrom a file; or - pass the
CLOBvalue as a bind variable from a 3rd party application.
But if you want to declare it entirely in an SQL query, then you cannot declare string literals greater than 4000 bytes and if you were trying to do so then, yes, you would need to split them up into appropriately sized chunks.
Comments
The string literal more than 4000 characters looks like a limitation with the SQL client. When I wrote a procedure with a variable holding the large data and use this variable in the insert query it worked. Also when I did test the same behavior through Java code it worked fine. So closing this question.
1 Comment
MT0
You are attributing the fact that it works via Java to the wrong thing. When passed as a bind variable you can pass the value as a
CLOB data type and that can have more than 4000 bytes of characters. When you try creating a string literal in SQL (whatever the client) then it has the same limitations as the CHAR/VARCHAR2 data type and cannot have more than 4000 bytes. If you are saying that you are passing it as a CHAR/VARCHAR2 with more than 4000 bytes in Java then I suggest that you edit your answer to include the code because you are going to find that that is not the case.
varchar2. But it is really uncommon to want to insert aclobvalue as a literal. If you have a SQL statement in an application, you'd normally be using a bind variable for the text value, which doesn't have the limitation on the length of a string literal.