CODEWITHSUNDEEP
oracle-database
VictorDDT
VictorDDT

Reputation: 613

Oracle: Dynamic Query: ORA-01704: string literal too long

EDIT 2:

After a small research I found that the problem is with long text value (more than 2000 symbols). I put values in single quotes in INSERT statement which I try to perform in "EXECUTE IMMEDIATE insert_query;".

Like:

INSERT INTO
tablename(
    field1,
    field2,
    field3
)
VALUES(
    '123',
    'some value',
    'long value more than 2000 symbols'
)

So the real question is:

How shall I insert long values correctly into the table?

I need to import a large string data from an external data source to an Oracle DB.

I have Oracle 12c (12.1.0.2.0)

What I try to do

This is a simplified code just to explain my idea

CREATE OR REPLACE PROCEDURE parsedata AS 
    TYPE string_dict IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(100);
    field_name_list string_dict;
    query_field_list varchar2(3000);
    query_value_list varchar2(10000);
    insert_query varchar2(20000);
    --insert_query CLOB;
BEGIN
    -- Field mapping for data source and destination database
    field_name_list('source_field1') := 'destination_field1';
    field_name_list('source_field2') := 'destination_field2';
    field_name_list('source_field3') := 'destination_field3';

    -- Iterate over records in the data source
    FOR record_index IN 1..record_count_total LOOP
        insert_query := 'INSERT INTO tablename([FIELDS]) VALUES([VALUES])';
        query_field_list := '';
        query_value_list := '';

        -- Iterate over fields per record
        FOR field_index IN 1..field_count_total LOOP
            query_field_list := query_field_list || field_name_list(source_field_name) || ',';
            query_value_list := query_value_list || field_value || ',';     

        insert_query := REPLACE(insert_query, '[FIELDS]', query_field_list);
        insert_query := REPLACE(insert_query, '[VALUES]', query_value_list);
        EXECUTE IMMEDIATE insert_query;
END parsedata;

What I have

Error: ORA-01704: string literal too long in line "EXECUTE IMMEDIATE insert_query;"

I get the same error if I use CLOB

insert_query CLOB;

EDIT 1:

I checked the length of "insert_query": 

length(insert_query)

And I get the error when the length is greather than 3000:

length(insert_query): 3038

Upvotes: 1

Views: 4875

Answers (1)

Popeye
Popeye

Reputation: 35910

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!!

Upvotes: 1

Related Questions