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
-
How are you running the script?Tom H– Tom H2011-09-19 19:32:13 +00:00Commented Sep 19, 2011 at 19:32
-
its oracle db,using sqlplus(@file.sql) since this would be easier solutionMike– Mike2011-09-19 19:37:21 +00:00Commented Sep 19, 2011 at 19:37
-
Please be aware that, depending on the script, some statements will error-out if previous statements also had errors. For example, indicies and constraints generally can't be created if the base table is non-existant. On the converse side, there are some statements that otherwise complete (aliases), which may give other programs ideas about the state of the database which (because of new errors) simply isn't true.Clockwork-Muse– Clockwork-Muse2011-09-19 20:34:23 +00:00Commented Sep 19, 2011 at 20:34
Add a comment
|
1 Answer
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