OraFAQ Forum: Client Tools » Additional new lines in SQL script file and problem with SQL*Plus

Home » Other » Client Tools » Additional new lines in SQL script file and problem with SQL*Plus

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Additional new lines in SQL script file and problem with SQL*Plus [message #432163]

Sun, 22 November 2009 10:31

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

Report message to a moderator

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

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.

Report message to a moderator

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

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

Report message to a moderator

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

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

Report message to a moderator

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

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

Report message to a moderator

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

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Thanks for the feedback.

Regards
Michel

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

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