Re: Grrr! How do you make sqlplus exit on a syntax error!
From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Wed, 23 Oct 2002 09:44:31 +0100
Message-ID: <3db66170$0$8514$ed9e5944_at_reading.news.pipex.net>
Date: Wed, 23 Oct 2002 09:44:31 +0100
Message-ID: <3db66170$0$8514$ed9e5944_at_reading.news.pipex.net>
turns out that connect was a very bad example since it gives an sql error
try
WHENEVER SQLERROR EXIT SQL.SQLCODE;
set l1ne 1000
instead of set line 1000
-- Niall Litchfield Oracle DBA Audit Commission UK ***************************************** Please include version and platform and SQL where applicable It makes life easier and increases the likelihood of a good answer ****************************************** "Patrick Elliott" <pelliott1NOSPAM_at_NOSPAMmn.NOSPAMrr.com> wrote in message news:nLft9.113859$w63.1718495_at_twister.rdc-kc.rr.com...Received on Wed Oct 23 2002 - 10:44:31 CEST
> This problem is already solved. Simply include the statement.
>
> WHENEVER SQLERROR EXIT SQL.SQLCODE;
>
> in the beginning of your script.
>
> "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message
> news:3daaaa3f$0$1292$ed9e5944_at_reading.news.pipex.net...
> > It isn't the sql thats the problem its the various sqlplus commands etc
> that
> > aren't accepted by pl/sql. suppose you get a connect statement wrong for
> > example (or the password has been changed etc). You would then want
> sqlplus
> > to exit. I agree with Chris that it is a highly minor enhancement
request,
> > but it would be a 'nice' feature.
> >
> >
> > --
> > Niall Litchfield
> > Oracle DBA
> > Audit Commission UK
> > *****************************************
> > Please include version and platform
> > and SQL where applicable
> > It makes life easier and increases the
> > likelihood of a good answer
> >
> > ******************************************
> > "Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message
> > news:3DA6EDCC.195EC257_at_exesolutions.com...
> > > Niall Litchfield wrote:
> > >
> > > > "Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message
> > > > news:3DA5BE71.87C125DF_at_exesolutions.com...
> > > > > I'll try again.
> > > > >
> > > > > What's wrong with:
> > > > >
> > > > > BEGIN
> > > > > ... your SQL statement here
> > > > > ... your SQL statement here
> > > > > ... your SQL statement here
> > > > > END;
> > > > > /
> > > > >
> > > > > Seems to me the functionality requested already exists. Just wrap
> > BEGIN
> > > > and END
> > > > > around your SQL.
> > > >
> > > > Sqlplus scripts do not necessarily just consist of sql
> > > >
> > > > SQL> begin
> > > > 2 set echo off
> > > > 3 execute immediate 'select sysdate from dual';
> > > > 4 end;
> > > > 5 /
> > > > set echo off
> > > > *
> > > > ERROR at line 2:
> > > > ORA-06550: line 2, column 6:
> > > > PL/SQL: ORA-00922: missing or invalid option
> > > > ORA-06550: line 2, column 2:
> > > > PL/SQL: SQL Statement ignored
> > > >
> > > > SQL>
> > > > --
> > > > Niall Litchfield
> > > > Oracle DBA
> > > > Audit Commission UK
> > > > *****************************************
> > > > Please include version and platform
> > > > and SQL where applicable
> > > > It makes life easier and increases the
> > > > likelihood of a good answer
> > > >
> > > > ******************************************
> > >
> > > Try running your SQL statement at the SQL*Plus command line and let me
> > know
> > > how it works?
> > >
> > > Connected to:
> > > Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
> > > With the Partitioning option
> > > JServer Release 8.1.7.0.0 - Production
> > >
> > > SQL> execute immediate 'select sysdate from dual';
> > > BEGIN immediate 'select sysdate from dual'; END;
> > >
> > > *
> > > ERROR at line 1:
> > > ORA-06550: line 1, column 17:
> > > PLS-00103: Encountered the symbol "select sysdate from dual" when
> > expecting
> > > one of the following:
> > > := . ( _at_ % ;
> > > The symbol ":=" was substituted for "select sysdate from dual" to
> > continue.
> > >
> > >
> > > SQL>
> > >
> > > Of course it isn't the solution to every problem: What is? But it sure
> > works
> > > with 90% of the SQL run from scripts which is mostly insert, update,
and
> > > delete.
> > >
> > > Daniel Morgan
> > >
> >
> >
>
>