wood burning stoves
The moose likes Oracle/OAS and the fly likes Unable to store large String in Long type Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Unable to store large String in Long type" Watch "Unable to store large String in Long type" New topic

Unable to store large String in Long type

Jigar M Gohil

Joined: Dec 14, 2011
Posts: 25
Hi All,
We have a legacy Database table with a column of type LONG which is used for storing report queries(SQL).
I have a sql query of length ~7000 characters which I am unable to store.
It throws ORA-01704: string literal too long

Does anyone know the limit for Long in terms on the character length?
How can I resolve this problem?

Thanks in advance!!!
Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33102

Oracle says it is up to 2GB. They also say it is deprecated and you should use CLOB instead.

I think the thing to do is write a script that tries to insert strings of different sizes to see where your limit lies. Maybe something else is going on. Or maybe there is an unescaped special character (like a quote) that is messing up the string and causing it to never end.

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
Jigar M Gohil

Joined: Dec 14, 2011
Posts: 25
Thanks Jeanne for the response...

I tried the incremental approach to find max string length for Long column.
It gives error with the string length 4058 (without any special character)... in spite of having 2GB capacity.

is there any configuration that needs to be done for utilizing this capacity?

Martin Vajsar

Joined: Aug 22, 2010
Posts: 3733

The limit you hit is not on the LONG column, but on the String literal. The error message even says so. Oracle won't accept a string constant that long. The same error would happen even if you were stuffing such a long string literal to CLOB.

You should use binds (PreparedStatement in Java), that way you don't put the text of the string into the SQL statement. I'm not sure there isn't a limit on the maximum length of the bind variable, though.

Are you using Java/JDBC? I didn't find a section specifying how to write/update a LONG in Oracle's JDBC Developer's Guide, only a section on how to read them. If you're using Java, try to treat the LONG as you would treat CLOB. If that doesn't work, you could still write a stored procedure that would take a temporary CLOB and stored it as a LONG to the database (hopefully that would be possible in PL/SQL). I haven't personally ever used LONGs, as they are obsolete for such a long time.
I agree. Here's the link: http://aspose.com/file-tools
subject: Unable to store large String in Long type
It's not a secret anymore!