Hi.
I have created dblink from Oracle database 11g (11.2.0.4.3) to Sybase ASE (15.5) using Oracle Database Gateway for Sybase (11.2.0.4.3) on OEL x64 (6.5).
When I make changes to a remote table with triggers and use the variable in the WHERE clause, the value of SQL%RowCount incorrectly calculated.
Found the antidote on the side of Sybase - the use of "set nocount on" inside a trigger.
Is there a solution on the side of Oracle (gateway or database) ?
Detailed description is given below :
1) Table and trigger description (Sybase)
create table test_TR (
id integer identity,
oper_id integer not null,
counter integer not null,
constraint XPKtest_TR primary key clustered (id))
go
insert into test_TR (oper_id,counter) values (1,10)
insert into test_TR (oper_id,counter) values (2,21)
insert into test_TR (oper_id,counter) values (2,36)
go
create trigger tUtest_TR on test_TR for UPDATE as
begin
declare @numrows int
select @numrows = @@rowcount
return
error:
raiserror 31000 'UPDATE'
rollback transaction
end
go
2) Gateway description (Gateway)
HS_FDS_CONNECT_INFO=xxx.xxx.xxx.xxx.xxx:xxxx/test
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=***
HS_FDS_TRANSACTION_MODEL=COMMIT_CONFIRM
HS_FDS_TRANSACTION_LOG=HS_TRANSACTION_LOG
HS_FDS_RESULTSET_SUPPORT=TRUE
HS_FDS_PROC_IS_FUNC=FALSE
3) Dblink description (Oracle)
CREATE PUBLIC DATABASE LINK "SYB_TEST" CONNECT TO "***" IDENTIFIED by "***" USING 'SYB_TEST' ;
4) Test query (Oracle)
declare per_id numeric;
begin
update "test_TR"@SYB_TEST set "counter" = "counter" + 1 where "oper_id" = 0 ;
dbms_output.put_line('0 rows = ' || SQL%RowCount) ;
per_id := 0 ;
update "test_TR"@SYB_TEST set "counter" = "counter" + 1 where "oper_id" = per_id ;
dbms_output.put_line('0 rows = ' || SQL%RowCount) ;
rollback;
end ;
/
0 rows = 0
0 rows = 1
Thanks in advance for any kind of information !