Home » Other » Client Tools » Additional new lines in SQL script file and problem with SQL*Plus
icon5.gif  Additional new lines in SQL script file and problem with SQL*Plus [message #432163] Sun, 22 November 2009 10:31 Go to next message
dariyoosh
Messages: 538
Registered: March 2009
Location: France
Senior Member
Dear all,


I would like to ask a question about new line characters inside SQL file scripts. I have a very long SQL query saved in a txt file (ANSI coding). Because the query statements are very long (many table joins and WHERE conditions) I tried to separate the FROM, WHERE and ORDER BY clauses by inserting several new line characters (just hitting the Enter) among them to improve the clarity of the source code. Now the problem is, whenever these new line characters are inserted into the file, apparently SQL*Plus can no more parse the file properly and gives me the following error message:

SP2-0042: unknown command "FROM" - rest of line ignored.
SP2-0734: unknown command beginning "myenterpri..." - rest of line ignored.


The problem is definitely related to these new line characters because when I remove them, the scripts runs without any problem. I would appreciate if you could kindly guide me how should I deal with this problem. Is there any SQL*Plus command to run in order to interpret new line characters correctly?


Thanks in advance,


Kind Regards,
Dariyoosh
Re: Additional new lines in SQL script file and problem with SQL*Plus [message #432165 is a reply to message #432163] Sun, 22 November 2009 10:41 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
> I would appreciate if you could kindly guide me how should I deal with this problem.
Do not have blank lines within single SQL statement.

>Is there any SQL*Plus command to run in order to interpret new line characters correctly?
None as far as I know.
Re: Additional new lines in SQL script file and problem with SQL*Plus [message #432166 is a reply to message #432163] Sun, 22 November 2009 10:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Instead of adding empty lines, insert a line with empty comment (or better meaningful comment):
--

Regards
Michel
Re: Additional new lines in SQL script file and problem with SQL*Plus [message #432175 is a reply to message #432166] Sun, 22 November 2009 12:56 Go to previous messageGo to next message
dariyoosh
Messages: 538
Registered: March 2009
Location: France
Senior Member
Michel Cadot wrote on Sun, 22 November 2009 17:41
Instead of adding empty lines, insert a line with empty comment (or better meaningful comment):
--

Regards
Michel




Dear Michel


Thanks a lot for your solution. In fact it works if these lines are in comments.


Regards,
Dariyoosh
Re: Additional new lines in SQL script file and problem with SQL*Plus [message #432183 is a reply to message #432163] Sun, 22 November 2009 16:01 Go to previous messageGo to next message
dariyoosh
Messages: 538
Registered: March 2009
Location: France
Senior Member
And finally I found a better solution:

What I had to do was to add this at the first line of the script file:

SET    SQLBLANKLINES   ON


Again, thanks all of you for your help.


Kind Regards,
Dariyoosh
Re: Additional new lines in SQL script file and problem with SQL*Plus [message #432195 is a reply to message #432183] Sun, 22 November 2009 23:12 Go to previous message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Thanks for the feedback.

Regards
Michel
Previous Topic: To identify SYSTEM user password
Next Topic: Running a procedure in sql*plus
Goto Forum:
  


Current Time: Fri Dec 06 13:45:23 CST 2024