Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

"ORA-01704: string literal too long" in Oracle 10g

631079Mar 28 2008 — edited Apr 10 2008
I would like to explain my problem with an example, let's create the following table:

CREATE TABLE longtest
(text LONG);

I have to insert lots of data into this table and when I run this command:

INSERT INTO LONGTEST VALUES ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
..................more than 4000 characters..................
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');

I get the following error:
ORA-01704: string literal too long

To workaround this, I used bind variables, like this:

BEGIN
EXECUTE IMMEDIATE 'INSERT INTO LONGTEST VALUES (:a) '
using
'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
..............(repeat more than 32768 characters)..............
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa';
COMMIT;
END;

This worked fine with Oracle 9.2.0.4 but when I do the same thing
in Oracle 10.2.0.2 there is a 32768 characters limit.

I am planning to upgrade the database but I need a workaround for
this, anyone knows why Oracle behaves differently in 10g??
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 8 2008
Added on Mar 28 2008
15 comments
4,781 views