How to override >2499 character error in Oracle DB

问题:

I have a Oracle query which I'm executing through shell script and in that my query is getting exceeded the maximum length of 2499.

I'm getting error

SP2-0027: INPUT IS TOO LONG(> 2499 CHARACTERS) - LINE IGNORED

回答1:

Here are some options for working around SQL*Plus line length limitations:

1. Upgrade to 12.2(?) client. On 12.2 the client allows up to 4999 characters. Which is infuriating in a way - if Oracle finally admits that 2499 is not enough, why did they only increase the limit to 4999?
2. Add line breaks. Split the results into multiple lines. If using Windows make sure to use both carriage return and newline - chr(13)||chr(10).
3. Use another program. Many programs have a SQL*Plus-like option. In general I recommend not using a SQL*Plus clone. The main advantage of SQL*Plus is that it's a simple tool and works just about the same everywhere. None of the SQL*Plus clones are fully compatible and many programs will break if you run SQL*Plus scripts on a clone.

回答2:

Try to add a line break somewhere in that long line; that's a limitation of SQL*Plus and, as far as I can tell, you can't avoid it by some setting (like SET LINESIZE 100 and similar).

回答3:

The real issue is input SQL itself is too long, single line exceed 2500 characters, not about the data inside the database. To workaround the error, you should follow the steps from @Jon Heller