Re: SQLPLUS fails when there is an extra line in the sql ?

From: <fitzjarrell_at_cox.net>
Date: Fri, 15 Feb 2008 12:07:08 -0800 (PST)
Message-ID: <d099d99f-a292-43a0-bf6e-90ab89b187be@h11g2000prf.googlegroups.com>

On Feb 15, 1:37 pm, John <chunj..._at_gmail.com> wrote:
> On Feb 15, 11:25 am, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net>
> wrote:
>
>
>
>
>
> > On Feb 15, 1:14 pm, John <chunj..._at_gmail.com> wrote:
>
> > > This may have been reported by someone, but I just want to share it
> > > with you, just in case.
>
> > > I am using sqlplus ( Release 10.1.0.5.0 - Production), on my linux
> > > box. Usually I am using this command to access a databae to execute a
> > > "deploy.sql" that invokes another hundreds sql files, such as:
> > > "
> > > sqlplus username/password_at_mydatabase @deploy.sql
> > > ",
>
> > > And here is the piece of the sql that gives me the headache.
> > > "
> > > CREATE TABLE "DM_ENUM"
> > >    (    "CLASS_NAME" VARCHAR2(60 BYTE),
> > >         "MEMBER_NAME" VARCHAR2(60 BYTE),
> > >         "MEMBER_ORDER" NUMBER
>
> > >    );
> > > ",
> > > as you can see there is an extra blank line before the right
> > > parenthesis, and because of that, I see such errors in my log file,
> > > "
> > > SP2-0042: unknown command ") " - rest of line ignored.
> > > ".
>
> > > But If I manually remove that blank line, everything works fine.
>
> > > I wonder if there is a patch, or higher version of sqlplus that have
> > > such problem solved ?
>
> > > John
>
> > It isn't a problem.  A  blank line in a SQL statement terminates that
> > statement:
>
> > SQL> select empno, empname,
> >   2   deptno,
> >   3  hiredate
> >   4
> > SQL> from emp;
> > SP2-0042: unknown command "from emp" - rest of line ignored.
> > SQL>
>
> > And it isn't a bug.  It's the accepted behaviour SQL*Plus has been
> > exhibiting for years.
>
> > David Fitzjarrell- Hide quoted text -
>
> > - Show quoted text -
>
> Hi David
> Can you try the sql that I gave, to see if the table is being
> created ?
>
> John.- Hide quoted text -
>
> - Show quoted text -

No, and I wouldn't expect it to be:

SQL> @blank_line_sql
SQL> set echo on
SQL> CREATE TABLE "DM_ENUM"
  2     (    "CLASS_NAME" VARCHAR2(60 BYTE),
  3          "MEMBER_NAME" VARCHAR2(60 BYTE),
  4          "MEMBER_ORDER" NUMBER

  5
SQL> );
SP2-0042: unknown command ")" - rest of line ignored. SQL>
SQL> desc dm_enum
ERROR:
ORA-04043: object dm_enum does not exist

SQL> You should be able to see why in the posted output above. Removing the blank line completes the buffer properly and the table is created:

SQL> @no_blank_line
SQL> set echo on
SQL> CREATE TABLE "DM_ENUM"
  2     (    "CLASS_NAME" VARCHAR2(60 BYTE),
  3          "MEMBER_NAME" VARCHAR2(60 BYTE),
  4          "MEMBER_ORDER" NUMBER
  5     );

Table created.

SQL> Note also you are informed of the table creation by SQL*Plus.

David Fitzjarrell Received on Fri Feb 15 2008 - 14:07:08 CST