CODEWITHSUNDEEP
sqloracle-databaseplsqlsqlplus
Mike
Mike

Reputation: 1

SQL script execution control

I am looking for tips how to control execution of large sql script. It will contain various DDL and DML statement. Mostly I was looking for DDL control.What do I actually mean is, that basically I have a script with multiple DDL statements that are generated from one of our DBs. When I start script it will go through even if there is compilation error or error on create. Thx for any ideas

Upvotes: 0

Views: 1195

Answers (1)

user272735
user272735

Reputation: 10648

Do you want to quit after an error ? Here is a few examples. Be sure to check documentation of WHENEVER SQLERROR.

DDL (DML) example:

prompt continues after error
prompt =====================
prompt

create table foo;

prompt quits after error with error code 
prompt =================================
prompt

whenever sqlerror exit sql.sqlcode

create table foo;

prompt never gets here
prompt ===============
prompt

quit

PL/SQL subprogram raises an exception:

create or replace function foo return number as
  foo_error exception;
begin
  raise foo_error;
end;
/
show errors

prompt continues after error
prompt =====================
prompt

select foo from dual;

prompt quits after error with error code 
prompt =================================
prompt

whenever sqlerror exit sql.sqlcode

select foo from dual;

prompt never gets here
prompt ===============
prompt

quit

PL/SQL unit compilation fails:

create or replace procedure compile_function (f in varchar2) as
begin
  execute immediate 'alter function :f compile' using f;
exception
  when others then
    raise_application_error(-20000, 'Failed to compile function ' || f);
end;
/
show errors

prompt continues after error
prompt =====================
prompt

create or replace function foo return number as
begin
  compilation will fail
end;
/
show errors

exec compile_function('foo')

prompt quits after error with error code 
prompt =================================
prompt

whenever sqlerror exit sql.sqlcode

create or replace function foo return number as
begin
  compilation will fail
end;
/
show errors

exec compile_function('foo')

prompt never gets here
prompt ===============
prompt

quit

Upvotes: 2

Related Questions