Toad World® Forums

Script runner aborts after compile error

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 :frowning:

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’
&

I do not know if this has influence in your case:

scripts.png

I have a similar issue. When running a script from the Editor, when an error is thrown I get the popup, but choosing Ignore or Ignore This Error both halt the execution of the script. If I choose Ignore All, the script continues to the end.

I also have the same exact problem as above where as soon as the scripts reports ‘Warning: compiled but with compilation errors’, it stops

Hi Abe,

It looks like a fix to a different issue broke this one. It’s been fixed for the next beta on Thursday.

Sorry about that!

-John

John,

No problem - I’m glad it’ll be fixed soon.
For the time being, I’ll use 12.11 to run my scripts :slight_smile:

Thanks for the quick fix,
Abe

Damir,

thanks for the suggestion. Good to know it’s possible to have Toad auto-ignore specified exceptions.

On my environment, the feature is turned off altogether, so should not have an impact.

Thanks anyway!
Abe