All,
This relates to Toad for Oracle Beta 12.12.0.8.
I’m running a script (using F5) that defines a table or a materialized view of the table, depending on the system.
After defining the object, it invokes three scripts to define triggers,
and then it invokes a script to set up the initial content (if it is a table, rather than an MV).
Like this:
– Triggers first: insert statement in _data.sql depends on insert trigger!
@…\Triggers\database_tbl_rbi.trg
@…\Triggers\database_tbl_rbu.trg
@…\Triggers\database_tbl_rbd.trg
@…\Setup_data\database_tbl_data.sql
Unfortunately, the third trigger does not compile.
But that should not cause the script from terminating;
and certainly not without reporting why it is quitting.
Here’s the final piece of the script output window:
Trigger created.
PL/SQL procedure successfully completed.
Trigger created.
PL/SQL procedure successfully completed.
Warning: compiled but with compilation errors
I’m not worried about the error in the trigger.
What bothers me is that the script execution is terminated
without any message.
So I spooled SQL to see if that yields any clue.
Please see below for content.
I have lots of nested scripts.
It’s really an issue if I cannot depend on the scripts
being run completely 
If you need any additional info, please let me know!
Abe Kornelis.
Spooled output:
– Session: UTL_KRG@WMT920
– Timestamp: 08:20:46.620
BEGIN SYS.DBMS_OUTPUT.ENABLE(buffer_size => NULL); END;
– Session: UTL_KRG@WMT920
– Timestamp: 08:20:46.672
begin dbms_output.enable(0); end;
– Session: UTL_KRG@WMT920
– Timestamp: 08:20:46.685
select PARAMETER,VALUE from nls_session_parameters where PARAMETER in(‘NLS_NUMERIC_CHARACTERS’,‘NLS_DATE_FORMAT’,‘NLS_CURRENCY’);
– Session: UTL_KRG@WMT920
– Timestamp: 08:20:46.696
select to_char(9,‘9C’) from dual;
– Session: UTL_KRG@WMT920
– Timestamp: 08:20:46.711
begin
utl_krg.install.drop_table(p_schema => ‘UTL_KRG’
, p_table => ‘database_tbl’
, p_save_data => ‘N’
, p_drop_ref_constraints => ‘Y’
);
exception
when others
then
utl_krg.msg_log.standard_exception_handler(p_schema => ‘utl_krg’
, p_routine => ‘database_tbl’
, p_msg_code => sqlcode
, p_local_msg_prefix => ‘UTL’
, p_message => sqlerrm
);
end;
– Session: UTL_KRG@WMT920
– Timestamp: 08:20:46.857
begin dbms_output.get_line(:ln,:st); end;
:ln(LONG,OUT)=
:st(INTEGER,OUT)=
– Session: UTL_KRG@WMT920
– Timestamp: 08:20:46.882
create table utl_krg.database_tbl(database_name varchar2(20 char) not null /* Name of database as we use it /
, seq numeric(3, 0) not null / Meaningless sequence number /
, internal_name varchar2(50 char) not null / Name of database as defined to itself /
, database_descr varchar2(80 char) not null / Description /
, otap_levels varchar2(8 char) not null / O=dev, T=tst, A=Accept, P=Prod, S=Support, E=Engineering /
, utl_krg_start date / date UTL_KRG schema was installed. Null when UTL_KRG not installed /
, utl_krg_end date / date UTL_KRG schema was uninstalled. Null when UTL_KRG not uninstalled /
, msg_repository varchar2(8 char) / DB_name of central database when utl_krg_installed=Y; null otherwise /
, standard_repository varchar2(1 char) not null / Y/N repository must match the TO_REPPRD target DB /
, start_date date not null / First day compno is valid /
, end_date date / Last day this compno is valid */
, change_date date not null
, change_user varchar2(22 char) not null
, comments varchar2(1024 char)
);
– Session: UTL_KRG@WMT920
– Timestamp: 08:20:46.925
comment on table utl_krg.database_tbl is ’ /******************************************************************************
NAME: database_tbl
REF: Quality Improvement Program
PURPOSE: We want a single location to store database-related data
REVISIONS:
Ver Date Author Description
1.0 16-Mar-15 Abe Kornelis Created this definition
1.1 07-Sep-15 Abe Kornelis ORADEV-176 Create common parameter table
NOTES:
******************************************************************************/’;
– Session: UTL_KRG@WMT920
– Timestamp: 08:20:46.968
declare
l_row_count pls_integer;
begin
if upper(substr(ora_database_name, 1, 3)) <> ‘REP’
then
utl_krg.install.drop_table(p_schema => ‘UTL_KRG’
, p_table => ‘database_TBL’
, p_save_data => ‘N’
, p_drop_ref_constraints => ‘N’
);
execute immediate ‘select count(*)
from all_tab_cols@rep_utl_krg
where owner = ‘‘UTL_KRG’’
and table_name = ‘‘DATABASE_TBL’’
and column_name = ‘‘STANDARD_REPOSITORY’’’ into l_row_count;
if l_row_count = 1
then – column standard_repository is defined on target system
execute immediate ‘create materialized view utl_krg.database_tbl
build immediate
refresh complete on demand
as
select *
from utl_krg.database_tbl@rep_utl_krg’;
else
execute immediate ‘create materialized view utl_krg.database_tbl
build immediate
refresh complete on demand
as
select db.*
, ‘‘N’’ as standard_repository
from utl_krg.database_tbl@rep_utl_krg db’;
end if;
end if;
exception
when others
then
utl_krg.msg_log.standard_exception_handler(p_schema => ‘utl_krg’
, p_routine => ‘database_tbl’
, p_msg_code => sqlcode
, p_local_msg_prefix => ‘UTL’
, p_message => sqlerrm
);
end;
– Session: UTL_KRG@WMT920
– Timestamp: 08:20:47.399
begin dbms_output.get_line(:ln,:st); end;
:ln(LONG,OUT)=
:st(INTEGER,OUT)=
– Session: UTL_KRG@WMT920
– Timestamp: 08:20:47.422
comment on column utl_krg.database_tbl.database_name is ‘Name of database as we know it’;
– Session: UTL_KRG@WMT920
– Timestamp: 08:20:47.451
comment on column utl_krg.database_tbl.seq is ‘Meaningless sequence number to create unique keys’;
– Session: UTL_KRG@WMT920
– Timestamp: 08:20:47.480
comment on column utl_krg.database_tbl.internal_name is ‘Internal name the database has defined for itself’;
– Session: UTL_KRG@WMT920
– Timestamp: 08:20:47.518
comment on column utl_krg.database_tbl.database_descr is ‘Full description’;
– Session: UTL_KRG@WMT920
– Timestamp: 08:20:47.547
comment on column utl_krg.database_tbl.otap_levels is
‘Intended usage: O=development, T=test, A=Acceptance, P=Production, S=Support, E=Engineering’;
– Session: UTL_KRG@WMT920
– Timestamp: 08:20:47.576
comment on column utl_krg.database_tbl.utl_krg_start is ‘Date utl_krg schema was installed; null when utl_krg not (yet) installed’;
– Session: UTL_KRG@WMT920
– Timestamp: 08:20:47.615
comment on column utl_krg.database_tbl.utl_krg_end is ‘Date utl_krg schema was uninstalled’;
– Session: UTL_KRG@WMT920
– Timestamp: 08:20:47.648
comment on column utl_krg.database_tbl.msg_repository is ‘Database where propagated messages are stored’;
– Session: UTL_KRG@WMT920
– Timestamp: 08:20:47.682
comment on column utl_krg.database_tbl.standard_repository is ‘Y/N repository must match the TO_REPPRD target DB’;
– Session: UTL_KRG@WMT920
– Timestamp: 08:20:47.714
comment on column utl_krg.database_tbl.start_date is ‘First day this database is valid’;
– Session: UTL_KRG@WMT920
– Timestamp: 08:20:47.748
comment on column utl_krg.database_tbl.end_date is ‘Last day this database is valid’;
– Session: UTL_KRG@WMT920
– Timestamp: 08:20:47.782
comment on column utl_krg.database_tbl.change_date is ‘Timestamp of last update’;
– Session: UTL_KRG@WMT920
– Timestamp: 08:20:47.816
comment on column utl_krg.database_tbl.change_user is ‘Initiator of last update’;
– Session: UTL_KRG@WMT920
– Timestamp: 08:20:47.845
comment on column utl_krg.database_tbl.comments is ‘Comment field’;
– Session: UTL_KRG@WMT920
– Timestamp: 08:20:47.891
begin
if upper(substr(ora_database_name, 1, 3)) = ‘REP’
then
execute immediate ‘create unique index utl_krg.database_tbl_pk
on utl_krg.database_tbl(database_name, seq)’;
execute immediate ‘alter table utl_krg.database_tbl add constraint database_tbl_pk
primary key(database_name, seq)’;
utl_krg.install.declare_table(p_schema => ‘utl_krg’, p_table => ‘database_tbl’);
else
utl_krg.install.declare_materialized_view(p_schema => ‘utl_krg’, p_materialized_view => ‘database_tbl’);
end if;
exception
when others
then
utl_krg.msg_log.standard_exception_handler(p_schema => ‘utl_krg’
, p_routine => ‘database_tbl’
, p_msg_code => sqlcode
, p_local_msg_prefix => ‘UTL’
, p_message => sqlerrm
);
end;
– Session: UTL_KRG@WMT920
– Timestamp: 08:20:47.910
begin dbms_output.get_line(:ln,:st); end;
:ln(LONG,OUT)=
:st(INTEGER,OUT)=
– Session: UTL_KRG@WMT920
– Timestamp: 08:20:47.933
alter table database_tbl
add constraint database_tbl_fk_otap_levels foreign key(otap_levels) references utl_krg.otap_levels(otap_level) enable validate;
– Session: UTL_KRG@WMT920
– Timestamp: 08:20:47.968
alter table utl_krg.database_tbl
add constraint database_tbl_dates check
( ( start_date is null
and end_date is null)
or ( start_date is not null
and ( end_date is null
or end_date > start_date)));
– Session: UTL_KRG@WMT920
– Timestamp: 08:20:48.135
alter table utl_krg.database_tbl
add constraint database_tbl_utl_dates check
( ( utl_krg_start is null
and utl_krg_end is null)
or ( utl_krg_start is not null
and ( utl_krg_end is null
or utl_krg_end > utl_krg_start)));
– Session: UTL_KRG@WMT920
– Timestamp: 08:20:48.168
alter table utl_krg.database_tbl
add constraint database_tbl_repository check
( ( utl_krg_start is null
and msg_repository is null)
or ( utl_krg_start is not null
and msg_repository is not null)
or ( utl_krg_start is not null
and utl_krg_end is not null
and msg_repository is not null));
– Session: UTL_KRG@WMT920
– Timestamp: 08:20:48.206
alter table utl_krg.database_tbl
add constraint database_tbl_std_repository check(standard_repository in (‘Y’, ‘N’));
– Session: UTL_KRG@WMT920
– Timestamp: 08:20:48.238
grant select on utl_krg.database_tbl to public;
– Session: UTL_KRG@WMT920
– Timestamp: 08:20:48.294
create or replace trigger utl_krg.database_tbl_rbi
/******************************************************************************
NAME: database_tbl_rbi
REF: NEPS
PURPOSE: Validate data before inserting a row into the database_tbl table
INPUT:
OUTPUT:
REVISIONS:
Ver Date Author Description
— --------- ----------- ------------------------------------
1.0 16-Mrt-15 Abe Kornelis Created this definition
NOTES:
******************************************************************************/
before insert
on utl_krg.database_tbl
for each row
declare
l_my_schema constant varchar2(40) := ‘utl_krg’;
l_my_routine constant varchar2(40) := ‘database_tbl_rbi’;
l_message varchar2(4000);
l_aantal pls_integer;
begin
:new.change_date := current_date;
select env.osuser
into :new.change_user
from utl_krg.current_env_vw env;
end database_tbl_rbi;
– Session: UTL_KRG@WMT920
– Timestamp: 08:20:48.322
begin
if upper(substr(ora_database_name, 1, 3)) <> ‘REP’
then
utl_krg.install.drop_trigger(p_schema => ‘UTL_KRG’, p_trigger => ‘database_TBL_RBI’);
utl_krg.msg_log.log_info_msg(p_caller => ‘utl_krg.database_tbl_rbi’
, p_message => 'Trigger UTL_KRG.database_tbl_rbi is not needed on ’ || ora_database_name
);
else
utl_krg.install.declare_trigger(p_schema => ‘UTL_KRG’, p_trigger => ‘database_tbl_rbi’);
utl_krg.msg_log.log_info_msg(p_caller => ‘utl_krg.database_tbl_rbi’
, p_message => ‘Trigger UTL_KRG.database_tbl_rbi has been (re-)created’
);
end if;
exception
when others
then
utl_krg.msg_log.standard_exception_handler(p_schema => ‘utl_krg’
, p_routine => ‘database_tbl_rbi’
, p_msg_code => sqlcode
, p_local_msg_prefix => ‘UTL’
&
