0

I want to export an Oracle database to a file and i'm using SQLDeveloper on Windows for that (the resulting SQL text file is about 35MB). This results in very long lines.

I hit a snag when importing the file using sqlplus on linux:

SP2-0341: line overflow during variable substitution (>3000 characters at line 1)

This occurs on multiple lines.

I see no options on how to limit the length of the lines when exporting. I've tried to set line size and then export, to no avail. I've also tried to edit the file but the vim tools i used focus on explicit line length and not the semantics of SQL (they'll break strings). Also my regex-fo can only go as far as surrounding the VALUES keyword with newlines, but even then the lines are still much longer than 3000. Also, 35MB is a lot to edit by hand.

I'm limited to those tools and RDBMS but i'm open to suggestions.

3

SQL*Plus is not a data loading tool. Use the proper tools.

SQL Developer can create CSV files. Instead of SQL*Plus, use SQL*Loader to load the data.

SQL*Plus Limits

enter image description here

  • Well pontied out, thanks, but the complexity of the control file(s) versus just loading CREATE TABLE and INSERT INTO statements just baffles me. It seems almost like each and every Oracle-related tool is intentionally over-engineered and bloated. – vesperto Jan 9 at 16:10
1

You can try SQLcl. I had the same issue with sqlplus and switching to SQLcl helped.

0

The 3000-character limit is for any single line in the SQL. Add line breaks to reduce the maximum line length.

Instead of

select columna, columnb, columnc ........ from table

do this

select
columna,
columnb,
columnc
from table

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.