Toad World® Forums

Toad causes unjustified ORA-24450 errors when running script


#1

All,

this relates to Beta 13.0.0.50 of Toad for Oracle.

I have a test script that runs fine in Toad 12.12, but fails with ORA-24450 in Toad 13.0.0.50.

When I select the first Block (lines 1 - 89) and hit F5 the block executes OK.
When I select the second Block (lines 91 - 113) and hit F5 the block executes OK.
Yet when I select both (lines 1 - 113) and hit F5, I get the dratted ORA-24450: Cannot pre-process OCI statement.

So I spooled the SQL and it seems Toad is appending some stuff before passing it to Oracle.
Below please find both my test script and the spooled output.

Thanks in advance,
Abe Kornelis

Here’s the test script:

set serveroutput on

declare
– Validate enviroment script is RUN ON
– NOT Accaptable to run on TST and PRD

– OMSD02 – Sandbox
– OMSD01 – DEV
db_name_sb varchar2(20) := ‘OMSD02’;
db_name_dev varchar2(20) := ‘OMSD01’;

db_name_curr varchar2(20); – VALID: OMSD02 ; OMSD01

db_user_used varchar2(20); – User used to execute this Script Allowed only EHDA_KRG

l_aantal pls_integer;
l_status iib_krg.poll_status.status%type;
begin
– select ora_database_name from dual;
select ora_database_name
into db_name_curr
from dual;

– see what DB you run this script on
dbms_output.put_line('The Test is Run on Current DB name: ’ || db_name_curr);

– validate environment
utl_krg.install.validate_environment(p_databases => db_name_sb || ’ ’ || db_name_dev);

– validate user EHDA_KRG
select user
into db_user_used
from dual;

if db_user_used <> ‘EHDA_KRG’
then
raise_application_error(-20001, 'Please Use EHDA_KRG user to run this script. You are using: ’ || db_user_used);
end if;

/* Make sure there’s only 1 master_data polling process /
select count(
)
into l_aantal
from iib_krg.poll_status
where poll_name = ‘MASTER_DATA’
and poll_seq <> 0
and status <> ‘DOWN’;

if l_aantal <> 0
then
raise_application_error(-20002, ‘Polling process for MASTER_DATA has active clones, please cleanup before testing’);
end if;

/* Set maintenance mode for master_Data polling process */
update iib_krg.poll_status
set maint_sid = sys_context(‘userenv’, ‘sessionid’)
where poll_name = ‘MASTER_DATA’
and poll_seq = 0;

commit;

select status
into l_status
from iib_krg.poll_status
where poll_name = ‘MASTER_DATA’
and poll_seq = 0;

if l_status = ‘RUNNING’
then
iib_krg.poll_control.alter_poll_process(p_poll_name => ‘MASTER_DATA’, p_command => ‘stop’);
end if;

if l_status <> ‘DOWN’
then
dbms_output.put_line(‘Waiting for poll_process to finish’);

while l_status <> ‘DOWN’
loop
utl_krg.sys_utils.sleep(p_seconds => 1);

select status
into l_status
from iib_krg.poll_status
where poll_name = ‘MASTER_DATA’
and poll_seq = 0;
end loop;
end if;

iib_krg.poll_control.alter_poll_process(p_poll_name => ‘MASTER_DATA’, p_command => ‘sleep=-1’);
end;


– Prepare test set

delete from ehda.vendbuy
where vendno like ‘ABE%’;

delete from iib_krg.vendbuy_log
where vendno like ‘ABE%’;

delete from iib_krg.master_data_log
where schema_name = ‘EHDA’
and table_name = ‘VENDBUY’
and key1 like ‘ABE%’;

delete from iib_krg.vendor_msg
where vendno like ‘ABE%’;

delete from iib_krg.saved_messages
where message_type = ‘vendor_msg’
and message_key1 like ‘ABE%’;

commit;

– Insert live vendor

insert into ehda.vendbuy
select ‘ABE_I2’
, name1
, name2
, street1
, street2
, street3
, pobox
, city
, zipcode
, uszipcode
, usstatecd
, countrycd
, payvendno
, termsdel
, termsdel1
, vendcat
, goname1
, goname2
, gostreet1
, gostreet2
, gopobox
, gocity
, gozipcode
, vendref
, vendref1
, vendref2
, tel
, telex
, telefax
, purchcom1
, language
, purchcom2
, implicneed
, delday
, domforwarder
, precarr
, trspmode
, forwarder
, freightcha
, inscha
, compldel
, purchstop
, numbconf
, termsprice
, checkcd
, credate
, null – killdate
, vendortype
, maxporows
, gostreet3
, gouszipcode
, gousstatecd
, vendnotemp
, svrlshiphead
, scaccode
, emaillong
, intraregion
, url
, calcmethpohead
, calcmethpoline
, routingcd
from ehda.vendbuy
where vendno = ‘1020701’;

commit;

begin
dbms_output.put_line(‘executing polling process’);
iib_krg.poll_control.run_poll_process(p_poll_name => ‘MASTER_DATA’, p_debug => ‘Nodebug’);
end;

select *
from no_table; – stop hier maar!

– Insert killed vendor

insert into ehda.vendbuy
select ‘ABE_I3’
, name1
, name2
, street1
, street2
, street3
, pobox
, city
, zipcode
, uszipcode
, usstatecd
, countrycd
, payvendno
, termsdel
, termsdel1
, vendcat
, goname1
, goname2
, gostreet1
, gostreet2
, gopobox
, gocity
, gozipcode
, vendref
, vendref1
, vendref2
, tel
, telex
, telefax
, purchcom1
, language
, purchcom2
, implicneed
, delday
, domforwarder
, precarr
, trspmode
, forwarder
, freightcha
, inscha
, compldel
, purchstop
, numbconf
, termsprice
, checkcd
, credate
, to_date(‘2010-05-17’, ‘yyyy-mm-dd’) – killdate
, vendortype
, maxporows
, gostreet3
, gouszipcode
, gousstatecd
, vendnotemp
, svrlshiphead
, scaccode
, emaillong
, intraregion
, url
, calcmethpohead
, calcmethpoline
, routingcd
from ehda.vendbuy
where vendno = ‘1020701’;

commit;

– insert vendor with future killdate

insert into ehda.vendbuy
select ‘ABE_IB’
, name1
, name2
, street1
, street2
, street3
, pobox
, city
, zipcode
, uszipcode
, usstatecd
, countrycd
, payvendno
, termsdel
, termsdel1
, vendcat
, goname1
, goname2
, gostreet1
, gostreet2
, gopobox
, gocity
, gozipcode
, vendref
, vendref1
, vendref2
, tel
, telex
, telefax
, purchcom1
, language
, purchcom2
, implicneed
, delday
, domforwarder
, precarr
, trspmode
, forwarder
, freightcha
, inscha
, compldel
, purchstop
, numbconf
, termsprice
, checkcd
, credate
, to_date(‘2018-04-13’, ‘yyyy-mm-dd’) – killdate
, vendortype
, maxporows
, gostreet3
, gouszipcode
, gousstatecd
, vendnotemp
, svrlshiphead
, scaccode
, emaillong
, intraregion
, url
, calcmethpohead
, calcmethpoline
, routingcd
from ehda.vendbuy
where vendno = ‘1020701’;

commit;

– Update live vendor, remains live

insert into ehda.vendbuy
select ‘ABE_U22’
, name1
, name2
, street1
, street2
, street3
, pobox
, city
, zipcode
, uszipcode
, usstatecd
, countrycd
, payvendno
, termsdel
, termsdel1
, vendcat
, goname1
, goname2
, gostreet1
, gostreet2
, gopobox
, gocity
, gozipcode
, vendref
, vendref1
, vendref2
, tel
, telex
, telefax
, purchcom1
, language
, purchcom2
, implicneed
, delday
, domforwarder
, precarr
, trspmode
, forwarder
, freightcha
, inscha
, compldel
, purchstop
, numbconf
, termsprice
, checkcd
, credate
, null – killdate
, vendortype
, maxporows
, gostreet3
, gouszipcode
, gousstatecd
, vendnotemp
, svrlshiphead
, scaccode
, emaillong
, intraregion
, url
, calcmethpohead
, calcmethpoline
, routingcd
from ehda.vendbuy
where vendno = ‘1020701’;

commit;

update ehda.vendbuy
set name1 = ‘Hotseflots’
where vendno = ‘ABE_U22’;

commit;

– Kill a live vendor

insert into ehda.vendbuy
select ‘ABE_U23’
, name1
, name2
, street1
, street2
, street3
, pobox
, city
, zipcode
, uszipcode
, usstatecd
, countrycd
, payvendno
, termsdel
, termsdel1
, vendcat
, goname1
, goname2
, gostreet1
, gostreet2
, gopobox
, gocity
, gozipcode
, vendref
, vendref1
, vendref2
, tel
, telex
, telefax
, purchcom1
, language
, purchcom2
, implicneed
, delday
, domforwarder
, precarr
, trspmode
, forwarder
, freightcha
, inscha
, compldel
, purchstop
, numbconf
, termsprice
, checkcd
, credate
, null – killdate
, vendortype
, maxporows
, gostreet3
, gouszipcode
, gousstatecd
, vendnotemp
, svrlshiphead
, scaccode
, emaillong
, intraregion
, url
, calcmethpohead
, calcmethpoline
, routingcd
from ehda.vendbuy
where vendno = ‘1020701’;

commit;

update ehda.vendbuy
set name1 = ‘Hotseflots’
, killdate = to_date(‘2014-04-30’, ‘yyyy-mm-dd’)
where vendno = ‘ABE_U23’;

commit;

– Kill a live vendor, boundary value = current_date

insert into ehda.vendbuy
select ‘ABE_U24’
, name1
, name2
, street1
, street2
, street3
, pobox
, city
, zipcode
, uszipcode
, usstatecd
, countrycd
, payvendno
, termsdel
, termsdel1
, vendcat
, goname1
, goname2
, gostreet1
, gostreet2
, gopobox
, gocity
, gozipcode
, vendref
, vendref1
, vendref2
, tel
, telex
, telefax
, purchcom1
, language
, purchcom2
, implicneed
, delday
, domforwarder
, precarr
, trspmode
, forwarder
, freightcha
, inscha
, compldel
, purchstop
, numbconf
, termsprice
, checkcd
, credate
, null – killdate
, vendortype
, maxporows
, gostreet3
, gouszipcode
, gousstatecd
, vendnotemp
, svrlshiphead
, scaccode
, emaillong
, intraregion
, url
, calcmethpohead
, calcmethpoline
, routingcd
from ehda.vendbuy
where vendno = ‘1020701’;

commit;

update ehda.vendbuy
set name1 = ‘Hotseflots’
, killdate = current_date
where vendno = ‘ABE_U24’;

commit;

– Change vendor_type from P to C

insert into ehda.vendbuy
select ‘ABE_U25’
, name1
, name2
, street1
, street2
, street3
, pobox
, city
, zipcode
, uszipcode
, usstatecd
, countrycd
, payvendno
, termsdel
, termsdel1
, vendcat
, goname1
, goname2
, gostreet1
, gostreet2
, gopobox
, gocity
, gozipcode
, vendref
, vendref1
, vendref2
, tel
, telex
, telefax
, purchcom1
, language
, purchcom2
, implicneed
, delday
, domforwarder
, precarr
, trspmode
, forwarder
, freightcha
, inscha
, compldel
, purchstop
, numbconf
, termsprice
, checkcd
, credate
, null – killdate
, ‘P’ – vendortype
, maxporows
, gostreet3
, gouszipcode
, gousstatecd
, vendnotemp
, svrlshiphead
, scaccode
, emaillong
, intraregion
, url
, calcmethpohead
, calcmethpoline
, routingcd
from ehda.vendbuy
where vendno = ‘1020701’;

commit;

update ehda.vendbuy
set vendortype = ‘C’
where vendno = ‘ABE_U25’;

commit;

– Change vendor_type from C to P

insert into ehda.vendbuy
select ‘ABE_U26’
, name1
&nbsp


#2

I have the same issue. Package has been compiled in Toad 12 and it works in SQL Developer but when I am compiling code in Toad 13 I get the error ORA-24450: Cannot pre-process OCI statement


#3

I’ve solved the issue by removing empty comment (–) in the end of the code


#4

This is fixed for 13.1 and the fix is in place in the current beta.

Thanks,

Michael