4
votes

I exported some data from my database table into sql file as insert statements. Now I want to launch them but I get error ORA-01704: string literal too long. The problem cause is propably one CLOB column which has XML data more than 4000 chars.

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

4

4 Answers

2
votes

Rather than using insert statements, you could leave the data in a delimited file and look at using either SQLLDR, or external tables. External tables are awesome.

1
votes

The way to get something larger then 4000 bytes in is to use pl\sql which supports up to 32767 bytes. Here is an example of how to solve the ORA-01704: string literal too long error:

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

you can also change your 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

0
votes

Just a thought for that particular column you should have split columns. For example say you current column is RANDOM_TEXT as VARCHAR(4000) it is exceeding the limit, you can split this into 2 columns say RANDOM_TEXT_1 and RANDOM_TEXT_2, when you are writing you should take the first 4000 characters to first RANDOM_TEXT_1 and the remaining to RANDOM_TEXT_2. When you are giving this back to any app or any api you have to combine and give as a single string.

0
votes

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;