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?
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.
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.