Insert instructions from a .sql file into an Oracle database, making ORA-01704: string literal too long

Question

I exported some data from my database table to sql file as insert statements. Now I want to run them, but I get error ORA-01704: the string literal is too long. The problem is with a single CLOB column that has XML data over 4000 characters.

What would be the best solution? I have about ~ 50 SQL insert statements in this file.

Answer 1

Instead of using insert statements, you can leave the data in a delimited file and look at using SQLLDR or external tables . The external tables are awesome.

Answer 2

To get something larger than 4000 bytes, you need to use pl \ sql, which supports up to 32767 bytes. Here is an example of how to solve ORA-01704: string literal error is too long:

declare vClobVal varchar2(32767) := '<Add text string here>'; begin update CLOBTAB set CLOBCOL = vClobVal; end; 

you can also change colomun type from varchar2 to CLOB

also see if this link can help you - http://www.dba-oracle.com/t_ora_01704_string_literal_too_long.htm

Answer 3

Just a thought for this particular column should have separated columns. For example, let's say that the current column RANDOM_TEXT, like VARCHAR (4000), exceeds the limit, you can divide it into 2 columns, for example RANDOM_TEXT_1 and RANDOM_TEXT_2, when you write, you must be the first first 4000 characters, the first RANDOM_TEXT_1, and the rest - RANDOM_TEXT_2 you give it back to any application or any api, you must combine and pass as a single line.

Answer 4

You can try this, it worked for me:

 DECLARE big_text_ CLOB := 'very very very very long text or XML.......'; BEGIN INSERT INTO table_name (column1, column2, column3_CLOB) VALUES ('value1', 'value2', big_text_); COMMIT; END;