Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, David.

Asked: April 22, 2010 - 10:41 pm UTC

Last updated: March 14, 2023 - 1:12 am UTC

Version: 10.2.0

Viewed 10K+ times! This question is

You Asked

In sqlplus called from Unix shell scripts, I'm trapping SQL errors via 'whenever sqlerror exit N' and OS errors via 'whenever oserror exit N'. However sqlplus errors (SP2 errors) are not trapped by either of these mechanisms, for example:

SQL> whenever sqlerror exit 7
SQL> whenever oserror exit 8
SQL> execute a_stored_procedure(:a_param);
SP2-0552: Bind variable "A_PARAM" not declared.
SQL> show sqlcode
sqlcode 0
SQL>


How do I trap SP2 errors?

1) what is the logical equivalent of 'whenever sqlplus error exit N'?
2) what other errors could I be missing?

and Tom said...

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


You would be missing anything that is not an OSERROR (like file system full, unable to open file, etc) or a pure SQL or PLSQL error - a sql statement that looks like SQL but isn't - select abc from dual is a SQL error, selct abc from dual is not.

ops$tkyte%ORA11GR2> whenever sqlerror exit
ops$tkyte%ORA11GR2> selct abc from dual;
SP2-0734: unknown command beginning "selct abc ..." - rest of line ignored.
ops$tkyte%ORA11GR2> select abc from dual;
select abc from dual
       *
ERROR at line 1:
ORA-00904: "ABC": invalid identifier


In short, sqlplus has no builtin capability to catch SP errors...

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Sqlplus error logging

Enrique Aviles, April 23, 2010 - 1:44 pm UTC

"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



Tom Kyte
April 23, 2010 - 2:39 pm UTC

Nice, thanks for the heads up, wrote that up

http://asktom.oracle.com/Misc/new-thing-about-sqlplus.html

sqlerrorlogging

bijunator, April 09, 2014 - 11:44 am UTC

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;

Tom Kyte
April 16, 2014 - 4:56 pm UTC

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

</code>



putting "/" like that is a really really bad idea..... that belongs in mysql2.sql




the create procedure "succeeded", it was not an error. there was no ORA-xxxxx raised. that is why you don't see an error, it wasn't one.






regarding sperrorlog

bijunator, April 21, 2014 - 10:49 am UTC

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...

sqlplus test for bind variable existence

Brian Fitzgerald, March 09, 2023 - 9:09 pm UTC

$ 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

Connor McDonald
March 14, 2023 - 1:12 am UTC

nice

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library