BUG: DOA, Win2000 and 8.1.6 client

[Peter Laursen]

Hi,
We are experiencing a weird bug. The problem occurs only when combining an 8i client on Win 2000 and DOA.
Description:
In Oracle make two tables both with primary keys maintained by a trigger and a sequence. Make a foreign key between them.
In Delphi/DOA using TOracleQuery, insert a row into the master, retrieving the key by a returning clause and insert a row into the child retrieving the key by a returning clause.
Insert another pair of rows.
After inserting the second row into the child, the code never returns from the OracleQuery.Execute !!
However, if the exact same code is executed on a machine (NT 4.0 or Win2k) with an 8.0.5 client there is no problem ?

If anybody can reproduce this error I would very much like to know! Below is simple code to reproduce.

Peter Laursen
EG Utility
ptl@edbgruppen.dk

SYSTEM:
Delphi Enterprise build 5.62
DOA 3.4.3
Oracle Client 8.1.6.3.4 on Win2000 build 2195
Oracle Server 8.0.5.2.1 on NT 4.0

{
-- RUN THIS IN SQLPLUS:
drop table t2;
drop table t1;
drop sequence t1_seq;
drop sequence t2_seq;

create table t1(a number primary key, b number not null);
create table t2(a number primary key, b number not null references t1(a));

create sequence t1_seq;
create sequence t2_seq;

create or replace trigger t1_trig
before insert on t1
for each row
begin
select t1_seq.nextval into :new.a from dual;
end;
/

create or replace trigger t2_trig
before insert on t2
for each row
begin
select t2_seq.nextval into :new.a from dual;
end;
/
}

procedure TForm1.Button1Click(Sender: TObject);
// place an TOracleSession and two TOracleQueries on a form
var
pk1: integer;
begin
OracleQuery1.SQL.Text := 'begin insert into t1(b) valuesnum) returning a into

k1; end;';
OracleQuery2.SQL.Text := 'insert into t2(b) values(

k1) returning a into

k2';
OracleQuery1.DeclareVariable('num', otInteger);
OracleQuery1.DeclareVariable('pk1', otInteger);
OracleQuery2.DeclareVariable('pk1', otInteger);
OracleQuery2.DeclareVariable('pk2', otInteger);
OracleSession1.LogOn;
// first pair of rows:
OracleQuery1.SetVariable('num', 1);
OracleQuery1.Execute;
pk1 := OracleQuery1.GetVariable('PK1');
OracleQuery2.SetVariable('PK1', pk1);
OracleQuery2.Execute;
// second pair of rows:
OracleQuery1.SetVariable('num', 1);
OracleQuery1.Execute;
pk1 := OracleQuery1.GetVariable('PK1');
OracleQuery2.SetVariable('PK1', pk1);
OracleQuery2.Execute; // never returns from this execute
// never reaches this line:
Button1.Caption := IntToStr(OracleQuery2.GetVariable('PK2'));
end;

 

[Peter Laursen]

Sorry, smilies changed the code, here is the correct code:
{
-- RUN THIS IN SQLPLUS:
drop table t2;
drop table t1;
drop sequence t1_seq;
drop sequence t2_seq;

create table t1(a number primary key, b number not null);
create table t2(a number primary key, b number not null references t1(a));

create sequence t1_seq;
create sequence t2_seq;

create or replace trigger t1_trig
before insert on t1
for each row
begin
select t1_seq.nextval into :new.a from dual;
end;
/

create or replace trigger t2_trig
before insert on t2
for each row
begin
select t2_seq.nextval into :new.a from dual;
end;
/
}

procedure TForm1.Button1Click(Sender: TObject);
// place an TOracleSession and two TOracleQueries on a form
var
pk1: integer;
begin
OracleQuery1.SQL.Text := 'begin insert into t1(b) valuesnum) returning a into k1; end;';
OracleQuery2.SQL.Text := 'insert into t2(b) valuespk1) returning a into k2';
OracleQuery1.DeclareVariable('num', otInteger);
OracleQuery1.DeclareVariable('pk1', otInteger);
OracleQuery2.DeclareVariable('pk1', otInteger);
OracleQuery2.DeclareVariable('pk2', otInteger);
OracleSession1.LogOn;
// first pair of rows:
OracleQuery1.SetVariable('num', 1);
OracleQuery1.Execute;
pk1 := OracleQuery1.GetVariable('PK1');
OracleQuery2.SetVariable('PK1', pk1);
OracleQuery2.Execute;
// second pair of rows:
OracleQuery1.SetVariable('num', 1);
OracleQuery1.Execute;
pk1 := OracleQuery1.GetVariable('PK1');
OracleQuery2.SetVariable('PK1', pk1);
OracleQuery2.Execute; // never returns from this execute
// never reaches this line:
Button1.Caption := IntToStr(OracleQuery2.GetVariable('PK2'));
end;