"surprisingly - there are no capabilities to catch those and exit or report an error."

There is no capability to catch and exit on SP2 errors but you can surely report them with the new 11g SQL*Plus error logging feature.


SQL> set errorlogging on
SQL> selct abc from dual;
SP2-0734: unknown command beginning "selct abc ..." - rest of line ignored.

SQL> col timestamp format a15
SQL> col username format a15
SQL> col script format a10
SQL> col identifier format a15
SQL> col statement format a20
SQL> col message format a20
SQL> select * from sperrorlog;

USERNAME TIMESTAMP SCRIPT IDENTIFIER MESSAGE STATEMENT
--------------- --------------- ---------- --------------- -------------------- --------------------
USER1 23-APR-10 06.34 SP2-0734: unknown co selct abc from dual;
.25.000000 PM mmand beginning "sel
ct abc ..." - rest o
f line ignored.


Read about it here:

http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-features/11g-misc.html

I have been trying the below stuff in SQLPLUS.

SQL>@myscript.sql
SQL>select * from sperrorlog;

Shows only error of mysql1.sql and not the one from mysql2.sql! Why??? Is there anyway I can see that too?

myscript.sql -->

set errorlogging on truncate
@mysql1.sql
@mysql2.sql
/
sho errors procedure my_proc

mysql1.sql -->

create table already_existing_table (myvar number);

mysql2.sql -->

create or replace procedure my_proc
as
my_var varchar2(10);
begin
select 'blah blah' into my_var from non_existing_table;
end;

set errorlogging on truncate
@mysql1.sql
@mysql2.sql
/
sho errors procedure my_proc

Yes sir. Its ugly but we have been trying to impose it on developers since long but they tend to forget it or whatever. Hence, I thought of giving "/" explicitly. This script of mine "myscript.sql" is an auto generated one.

Yes true, the procedure got created but with compilation errors. Can we log this in the sperrorlog table? Or is it like only ORA- errors will be logged in that table and not PL/SQL errors or warnings?

11:36:58 my_schema@my_DB> @myscript.sql
Connected.
create table already_existing_table
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object


Elapsed: 00:00:00.01

Warning: Procedure created with compilation errors.

Elapsed: 00:00:00.51
Errors for PROCEDURE MY_PROC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/1      PL/SQL: SQL Statement ignored
5/39     PL/SQL: ORA-00942: table or view does not exist
11:37:24 my_schema@my_DB>

Thanks a lot for your time...

$ cat assert.bind.variable.exists.sql

whenever sqlerror exit 1

variable test varchar2(4000)
set verify off

define bind_variable_name=&&1

exec :test := null
declare
    l_test varchar2(4000);
begin
    l_test := :&&bind_variable_name;
    :test := 1;
end;
/
begin
    if :test is null
    then
        raise_application_error(-20001, 'bind variable does not exist');
    end if;
end;
/

prompt bind variable &&bind_variable_name exists


SQL> variable x number
SQL> @ assert.bind.variable.exists.sql x

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

bind variable x exists
SQL> @ assert.bind.variable.exists.sql y

PL/SQL procedure successfully completed.

SP2-0552: Bind variable "Y" not declared.
begin
*
ERROR at line 1:
ORA-20001: bind variable does not exist
ORA-06512: at line 4


Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.18.0.0.0

Hi ASKTOM

We just had a minor disaster from a batch job (shell script) trying to execute a .sql script that had gone missing.

Call in .sh script is like:

sqlplus -s /nolog @thisScriptNoLongerExists.sql
rc=$?

And the call to sqlplus seems to have just echoed out:

SP2-0310 Unable to open file "thisScriptNoLongerExists.sql"

and then returned 0 - So our job did not recognize it failed.

Any known way around this?
(Apart from making the .sh first check that the .sql file exists)

BR
Peter G

[oracle@dbaas23ai ~]$ cat test.sh
sqlplus -s /nolog <<EOF

whenever oserror exit 42
@asdf
exit
EOF
[oracle@dbaas23ai ~]$ ./test.sh
O/S Message: No such file or directory
[oracle@dbaas23ai ~]$ echo $?
42

Thanks Chris,

That's a nice idea - (We are ofc already oserror in the non-existing-script)

But envoking sqlplus with such "EOF" - Nice idea!

Thanks
Peter