• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Unable to store large String in Long type

 
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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!!!
Regards.
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Jigar M Gohil
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?

Regards
Jigar
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic