sqlplus SP2-0341 (line overflow during variable substitution)

Question

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

• miracle173: why do you want to export the database? If you want to transport it to another Oracle database then use expdp or exp, depending on the Oracle version.
• vesperto: @miracle173 I don't remember anymore, I believe it was because of different Oracle RDBMS and tools versions, as well as different OS and compatibility issues. Anyway, it's been solved.
• miracle173: I see now this is an old question. It caught my attention because you made an edit 4 hours ago so it was diplayed as actibe question.

Answer 1

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

• vesperto: 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.

Answer 2

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

Answer 3

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