Error : ORA-01704: string literal too long
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
Oracle: Dynamic Query: ORA-01704: string literal too long
This issue occurs when you try to insert string value in the CLOB column with a length of the string greater than 4000.
You need to use TO_CLOB to convert string to CLOB, but unfortunately, It can also accept 4000 characters.
To solve the issue, You need to divide your string in chunks of 4000 characters and use TO_CLOB as described in the following example:
SQL> CREATE TABLE CLOB_TEST (
2 MY_CLOB CLOB
3 );
Table created.
Trying to insert a string of length 4282 > 4000 -- Error
SQL> INSERT INTO CLOB_TEST ( MY_CLOB ) -- inserting a string of length 4282 > 4000
2 VALUES ( '0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
3 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
4 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
5 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
6 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
7 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
8 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
9 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
10 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
11 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
12 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
13 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
14 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
15 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
16 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
17 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
18 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
19 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
20 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
21 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
22 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
23 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
24 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
25 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
26 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
27 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
28 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
29 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
30 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
31 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
32 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
33 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
34 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
35 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
36 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
37 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
38 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
39 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
40 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
41 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
42 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
43 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789'
44 );
VALUES ( '0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
*
ERROR at line 2:
ORA-01704: string literal too long
Trying to insert the string of length 4282 with TO_CLOB -- Error
SQL> INSERT INTO CLOB_TEST ( MY_CLOB ) -- trying to insert the string of length 4282 with TO_CLOB
2 VALUES ( TO_CLOB('0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
3 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
4 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
5 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
6 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
7 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
8 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
9 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
10 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
11 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
12 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
13 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
14 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
15 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
16 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
17 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
18 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
19 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
20 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
21 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
22 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
23 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
24 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
25 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
26 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
27 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
28 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
29 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
30 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
31 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
32 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
33 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
34 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
35 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
36 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
37 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
38 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
39 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
40 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
41 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
42 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
43 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789'
44 ) );
VALUES ( TO_CLOB('0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
*
ERROR at line 2:
ORA-01704: string literal too long
Trying to insert a string of length 3670 < 4000 -- Success
SQL> INSERT INTO CLOB_TEST ( MY_CLOB ) -- inserting a string of length 3670
2 VALUES ( '0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
3 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
4 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
5 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
6 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
7 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
8 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
9 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
10 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
11 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
12 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
13 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
14 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
15 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
16 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
17 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
18 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
19 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
20 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
21 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
22 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
23 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
24 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
25 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
26 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
27 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
28 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
29 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
30 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
31 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
32 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
33 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
34 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
35 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
36 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
37 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789'
38 );
1 row created.
Trying to insert the string of length 4282 with TO_CLOB -- Error
SQL> INSERT INTO CLOB_TEST ( MY_CLOB ) -- trying to insert the string of length 4282 with TO_CLOB
2 VALUES ( TO_CLOB('0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
3 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
4 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
5 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
6 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
7 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
8 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
9 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
10 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
11 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
12 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
13 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
14 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
15 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
16 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
17 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
18 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
19 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
20 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
21 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
22 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
23 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
24 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
25 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
26 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
27 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
28 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
29 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
30 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
31 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
32 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
33 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
34 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
35 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
36 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
37 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
38 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
39 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
40 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
41 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
42 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
43 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789'
44 ) );
VALUES ( TO_CLOB('0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
*
ERROR at line 2:
ORA-01704: string literal too long
Trying to insert the string in two parts of length (3670,610) with TO_CLOB () -- Success
SQL> INSERT INTO CLOB_TEST ( MY_CLOB ) -- trying to insert the data in two parts(3670,610) with TO_CLOB ()
2 VALUES ( TO_CLOB('0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
3 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
4 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
5 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
6 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
7 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
8 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
9 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
10 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
11 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
12 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
13 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
14 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
15 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
16 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
17 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
18 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
19 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
20 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
21 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
22 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
23 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
24 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
25 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
26 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
27 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
28 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
29 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
30 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
31 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
32 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
33 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
34 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
35 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
36 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
37 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789')
38 --
39 || TO_CLOB('0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
40 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
41 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
42 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
43 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
44 0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789'
45 )
46 );
1 row created.
SQL>
Note: If you concate the two strings of length less than 4000 characters(resulting string length > 4000) then also it will not work and fails with error: ORA-01489: result of string concatenation is too long
So the conclusion is to use TO_CLOB with chunks of less than 4000 characters and concate the CLOBs using concatanation operator ||
Cheers!!
Oracle Sql Developer string literal too long error
You will need to use a CLOB as the input to XMLTYPE() instead of a VARCHAR.
Using either dbms_lob.loadclobfromfile to load the xml from a file, or by breaking up the xml into 32000 character chunks and appending to the CLOB.
DECLARE
xmlClob CLOB;
BEGIN
/* Build Clob here */
WITH openedXml AS (
SELECT extractvalue(column_value, '/theRow/First') FIRST,
extractvalue(column_value, '/theRow/Last') LAST,
to_number(extractvalue(column_value, '/theRow/Age')) Age
FROM TABLE(XMLSequence(XMLTYPE(xmlClob).extract('/theRange/theRow')))
)
SELECT *
FROM openedxml
WHERE age BETWEEN 30 AND 35;
END;
ORA-01704: string literal too long 'Error when inserting XML document in Oracle XMLTYPE column type'
My guess is you are passing the XML as a literal to the insert statement. Oracle's SQL can only handle up to 4000 characters in a literal. Otherwise you need to use bind variables and pass it in chunks. Or you can use PL/SQL.
For example, this should work without issue because the literal
<MyMessage>Meeesaaagee</MyMessage>
is only 34 characters:
CREATE TABLE TEST_REPORTS
(
ID NUMBER,
DESCRIPTION VARCHAR2 (50),
XML XMLTYPE NULL
);
INSERT INTO TEST_REPORTS (ID, DESCRIPTION, XML)
VALUES (1, 'BLAH BLAH', XMLTYPE.CREATEXML ('<MyMessage>Meeesaaagee</MyMessage>'));
COMMIT;
But if you had:
Meeesaaagee (+ 3976 extra characters)
You will get the ORA-01704: string literal too long error.
You could try:
DECLARE
in_xml_value varchar2(32767);
BEGIN
in_xml_value := '<MyMessage>MeeesaaageeBLAHBLAHBLAH<--repeat--></MyMessage>';
INSERT INTO TEST_REPORTS (ID, DESCRIPTION, XML)
VALUES (1, 'BLAH BLAH', XMLTYPE.CREATEXML (in_xml_value);
commit;
END;
/
Do do it w/o PL/SQL code and to use bind variables, well you would have to talk with an application developer. It is outside of Oracle (and outside of my knowledge).
Error : ORA-01704: string literal too long. Dynamically assign CLOB to variable
You're getting the ORA-01704 because your string literal is more than 4000 bytes, which is the size limit for string literals in SQL calls. In PL/SQL the limit is 32k, so if all your values are less than that you can assign them to a PL/SQL variable and use that for the insert:
DECLARE
newId NUMBER(38,0) := &1;
newDescription varchar2(32767); -- or clob
BEGIN
newDescription := 'LARGE CLOB WHICH PRODUCES EXCEPTION';
Insert into FOO ("ID", "DESCRIPTION")
values (newId+1, newDescription);
newDescription := 'ANOTHER LARGE CLOB WHICH PRODUCES EXCEPTION';
Insert into FOO ("ID", "DESCRIPTION")
values (newId+1, newDescription);
...
END;
/
If any of the values are more than 32k you'll need a PL/SQL CLOB variable, and will need to construct that by appending shorted (<32k) string literals, which is messy.
Using multiple insert statements may not be the best way to go anyway. You might be able to use SQL*Loader or an external table to load the data more simply. Or you could read the values using utl_file, e.g. into the same PL/SQL variable, and then insert in a loop - which would be less code and easier to maintain.
You could also use a collection to hold the string values:
DECLARE
TYPE stringTab IS table of varchar2(32767); -- or clob
newDescriptions stringTab := new stringTab();
BEGIN
newDescriptions.extend;
newDescriptions(newDescriptions.last) := 'LARGE CLOB WHICH PRODUCES EXCEPTION';
newDescriptions.extend;
newDescriptions(newDescriptions.last) := 'ANOTHER LARGE CLOB WHICH PRODUCES EXCEPTION';
forall i in newDescriptions.first..newDescriptions.last
insert into FOO ("ID", "DESCRIPTION")
values (&1 + 1, newDescriptions(i));
END;
/
... which will be a trade-off between performance and (maybe) readability, against memory usage by the collection. And you can populate that in the block, or again read the values into the collection from a file, if that's feasible for your situation.
You can still generate this from queries against an existing table, with something like:
set pages 0
set lines 32767
set long 32767
set define off
select 'DECLARE' || chr(10)
|| ' newId NUMBER(38,0) := &1;' || chr(10)
|| ' newDescription varchar2(32767);' || chr(10)
|| 'BEGIN'
from dual;
select ' newDescription := q''[' || description || ']'';' || chr(10)
|| ' newId := newId + 1;' || chr(10)
|| ' insert into FOO ("ID", "DESCRIPTION") values (newId, newDescription);' || chr(10)
from foo;
select 'END;' || chr(10)
|| '/' || chr(10)
|| 'exit'
from dual;
set define on
I've used the alternative quoting mechanism in case any of your string values contain single quotes, but you'll need to pick a suitable quote delimiter. And again this assumes none of your CLOB values exceeds 32k.
I'd also reconsider if you really want to do this with a script full of insert statements; if the data is coming from a table anyway then an export/import might be more appropriate.
Related Topics
How to Prevent Ssis from Writing Column Names to the Flat File Output
Connect to Remote SQL Database Using Excel
Generic SQL That Both Access and Odbc/Oracle Can Understand
How to Call a Stored Proc from a Function
Average Difference Between Two Dates, Grouped by a Third Field
SQL - Converting 24-Hour ("Military") Time (2145) to "Am/Pm Time" (9:45 Pm)
Add 2 Months to Current Timestamp
Getting Extra Rows - After Joing the 3 Tables Using Left Join
Generally, Are String (Or Varchar) Fields Used as Join Fields
SQL Server Split Comma Separated Values into Columns
SQL Function Issue "The Last Statement Included Within a Function Must Be a Return Statement"
Why Isn't Postgres Using the Index
Create a SQL View Based Converting Ranges into Rows