Monday, June 13, 2005

Blank Lines and SQLPlus

Try creating the following table using SQLPlus Worksheet.

create table atable (

aint integer

);

You will get this error:

SP2-0734: unknown command beginning "aint integ..." - rest of line ignored.

It thinks "aint integer" is a new command. What is the problem? Repeat this test using SQLPlus at a prompt:

SQL> create table atable
2 (
3
SQL>

The blank line stops the statement.

This doesn't apply to all statements, but it does apply to select/insert/update/delete queries. It doesn't apply to creating procedures:

SQL> CREATE OR REPLACE PROCEDURE MyProc
2
3 AS
4
5 BEGIN
6
7 NULL;
8
9 END;
10 /

Procedure created.

At first I just made the blank lines a comment:

create table atable (
--
aint integer
--
);

Table created.

But eventually I took a closer look and arrived at the correct solution. Here it is.

set sqlblanklines on;

SQL> create table atable
2 (
3
4 aint integer
5
6 );

Table created.

Its just a little SQLPlus foible.

If you see something similar and you aren't using blank lines, see if you're using any special symbols and look at "set sqlprefix" instead.

So what should we do?
1. Put in comments to avoid the blank lines.
2. Use something other than sqlplus.
3. Explicitly set blanklines on at the top of your statement.

Comments:
Thank you for the tip ... had 1361 lines long query failing ... strange tool this sqlplus.
 
Many thanks for the tip. Very useful article.
 
Many thanks for posting this. I figured out by trial and error what was going wrong but had a hard time finding any online reference.

I discovered when reading an SQL reference that SQL *Plus is different from SQL in that no semi-colon is required to indicate the end of a statement. Instead, the enter key (a blank line) signals that a command should be executed. Hence the problems with blank lines being included.
 
Thanks for the tip. I was struggeling why my query worked fine in TOAD but not in SQLPlus. This one has fixed it
 
Great tip, saved me a lot of headache when a script saved from TOAD ran fine there but failed in SqlPlus.
 
Thanks for the tip.
 
Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?