Thanks for the question, Don.
Asked: December 20, 2018 - 5:50 pm UTC
Last updated: December 21, 2018 - 9:20 am UTC
Version: 12c
Viewed 1000+ times
You Asked
My company uses distribution scripts for deploying database updates to multiple environments. I need to control the scripts such that an "error" (defined as resulting in an "Ora-#" code) abends the script, but a warning (e.g. Warning: compiled but with compilation errors) does not.
The "whenever sqlerror" statement does not appear to discriminate between warnings and errors. Is there a way to do this?
Thanx, D
with LiveSQL Test Case:
and Chris said...
You're going to need to give more details about what the issue is here.
When I run your LiveSQL script I get the behaviour you've asked for. The package body has a compilation error. But the script continues until it hits the create view for TEST_VIEW2. Which throws an ORA error:
SQL> ho type errors.sql
REM Script: whenever sqlerror
REM Control distribution scripts with whenever sqlerror
whenever sqlerror exit SQL.SQLCODE
create or replace package TEST1
is
procedure test_procedure;
end TEST1;
/
sho errors
create or replace package body TEST1
is
procedure test_procedure
as
begin
for i in (
select 1 as N
from foo
)
loop
DBMS_OUTPUT.put_line (i.N);
end loop i;
end;
end TEST1;
/
sho errors
create or replace view TEST_VIEW as
select 1 as N from dual;
create or replace view TEST_VIEW2 as
select 1 as N from foo;
create or replace view TEST_VIEW3 as
select 2 as N from dual;
SQL> @errors
Package created.
No errors.
Warning: Package Body created with compilation errors.
Errors for PACKAGE BODY TEST1:
LINE/COL ERROR
-------- -----------------------------------------------------------------
9/9 PL/SQL: SQL Statement ignored
10/14 PL/SQL: ORA-00942: table or view does not exist
13/9 PL/SQL: Statement ignored
13/31 PLS-00364: loop index variable 'I' use is invalid
View created.
select 1 as N from foo
*
ERROR at line 2:
ORA-00942: table or view does not exist
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production