Grrr! How do you make sqlplus exit on a syntax error!
Peter Lacey
if they succeeded or failed. After a series of setbacks I have the
following code preceeding the SQL itself:
WHENEVER OSERROR EXIT 255
WHENEVER SQLERROR EXIT 255
CONNECT &1/&2@&3
BEGIN
NULL;
END;
/
WHENEVER SQLERROR EXIT SQL.SQLCODE
The 6 silly lines in the middle were needed simply to get sqlplus to
exit on a failed login. However, if the SQL that follows contains a
-syntax- error, nothing happens, sqlplus just keeps running the SQL.
So the question is, where is the Oracle equivalent of WHENEVER
SYNTAXERROR EXIT (whatever). Be aware that there are tens of files
each thousands of lines long. A good answer would allow me to modify
each file in just one place.
<rant>Who wrote this thing, anyway? Hasn't sqlplus been around for
100 years? Hasn't anyone ever needed to simply exit sqlplus when
something -anything- goes wrong?</rant>
Karsten Farell
> <rant>Who wrote this thing, anyway? Hasn't sqlplus been around for
> 100 years? Hasn't anyone ever needed to simply exit sqlplus when
> something -anything- goes wrong?</rant>
I'm not sure who wrote sqlplus, but your rant is shared by others.
Jonathan Gennick, who wrote "Oracle SQL*Plus: The Definitive Guide" had
this to say when talking about handling errors in scripts:
"SQL*Plus doesn't offer too much in the way of error handling. By
default, SQL*Plus simply ignores errors and goes on to execute either
the next command ... in the script you are running. ... Depending on
what the script is doing, you may not want SQL*Plus to blindly proceed
to the next command when an error occurs. ..."
He goes on to explain the two version of WHENEVER, lamenting that they
are sorely inadequate. So you have company.
What I've done in perl scripts is to put the sqlplus output in a string
which I can parse with regular expressions, looking for errors (after
coding REs that bypass all the verbose stuff that sqlplus prints). I
don't know if this is a viable option for you. Short of that, there
isn't much hope. (You could write to Larry Ellison - :) - that will
surely motivate him to change - yeah, right.)
Richard Kuhler
there isn't. They know that it works this way ... "WHENEVER SQLERROR
... SQL*Plus will not exit on a SQL*Plus error."
FYI, I typically use this logon sequence as the top of batch scripts ...
scott/tiger@orcl
--/--
--/--
If the login is successful then those are treated as comments otherwise
they fail as login attempts and the script exits. Just seems a little
clearer to me but decide for yourself.
Richard
Daniel Morgan
What's everyone whining about?
Just use an anonymous block.
BEGIN
your SQL statement's here
END;
/
Daniel Morgan
Christop...@oracle.comx
> FYI, I typically use this logon sequence as the top of batch scripts ...
>
> scott/tiger@orcl
> --/--
> --/--
>
> If the login is successful then those are treated as comments otherwise
> they fail as login attempts and the script exits. Just seems a little
> clearer to me but decide for yourself.
In SQL*Plus 9.2 the -L option to SQL*Plus removes the need for the
second and third line. See:
http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/server.920/a90842/ch4.htm#1005887
A WHENEVER SQLPLUSERROR command has been mooted before, (Metalink
users can see enhancement 429330. See also 518123).
Chris
--
Christopher Jones, Oracle Corporation, Australia.
Richard Kuhler
Request System" (ers.oracle.com) using those numbers (429330, 518123) as
the "Enhancement ID" for the search. Note that ERS is accessible with a
technet account but I couldn't find anything on Metalink either.
Where are these exactly?
Richard
Christop...@oracle.comx
> I can't find those enhancements. I'm looking on the "Oracle Enhancement
> Request System" (ers.oracle.com) using those numbers (429330, 518123) as
> the "Enhancement ID" for the search. Note that ERS is accessible with a
> technet account but I couldn't find anything on Metalink either.
>
> Where are these exactly?
I found them in ERS after I wound the "Created after" date back to
1996.
429330 is the request for WHENEVER SQLPLUSERROR.
518123 is the earliest discussion that CONNECT is a SQL*Plus command
and not a SQL command.
Niall Litchfield
news:un0pn2...@oracle.comX...
> Richard Kuhler <no...@nowhere.com> writes:
>
> > I can't find those enhancements. I'm looking on the "Oracle Enhancement
> > Request System" (ers.oracle.com) using those numbers (429330, 518123) as
> > the "Enhancement ID" for the search. Note that ERS is accessible with a
> > technet account but I couldn't find anything on Metalink either.
> >
> > Where are these exactly?
>
> I found them in ERS after I wound the "Created after" date back to
> 1996.
>
> 429330 is the request for WHENEVER SQLPLUSERROR.
This was last updated in 1997 with a 'pass to external user group for
comments' status. Did this ever get done? If so I assume this enhancement
has effectively been rejected.
--
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
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.
Daniel Morgan
Christop...@oracle.comx
> <Christop...@oracle.comX> wrote in message
> news:un0pn2...@oracle.comX...
> > 429330 is the request for WHENEVER SQLPLUSERROR.
>
> This was last updated in 1997 with a 'pass to external user group
> for comments' status. Did this ever get done? If so I assume this
> enhancement has effectively been rejected.
I haven't forgotten it. It has never risen high enough in the queue
given the complexity/benefit/destabilization/resource issues.
Niall Litchfield
news:3DA5BE71...@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>
Daniel Morgan
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:
:= . ( @ % ;
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
Niall Litchfield
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" <dmo...@exesolutions.com> wrote in message
news:3DA6EDCC...@exesolutions.com...
Daniel Morgan
Agreed.
Daniel Morgan
Christop...@oracle.comx
> 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.
I think improved error handling would be very useful. It's just the
cost (of a full solution) is high and the cost/benefit ratio has been
low relative to other things.
Patrick Elliott
WHENEVER SQLERROR EXIT SQL.SQLCODE;
in the beginning of your script.
"Niall Litchfield" <n-litc...@audit-commission.gov.uk> wrote in message
news:3daaaa3f$0$1292$ed9e...@reading.news.pipex.net...
Niall Litchfield
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" <pelliot...@NOSPAMmn.NOSPAMrr.com> wrote in message
news:nLft9.113859$w63.1...@twister.rdc-kc.rr.com...