Using a Database Gateway Dbms_hs_passthrough.Execute_immediate Always Returns 0 (Doc ID 1447411.1)

Last updated on JANUARY 09, 2017

Applies to:

Oracle Database Gateway for DRDA - Version: 11.2.0.1 to 11.2.0.3 - Release: 11.2 to 11.2
Oracle Server - Enterprise Edition - Version: 11.2.0.1 to 11.2.0.3   [Release: 11.2 to 11.2]
Information in this document applies to any platform.

Symptoms


Using DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE with a gateway always returns 'results = 0' even though rows have been updated or changed.

For example -

SQL> select * from tkhodept@SYSC0_1123 ;

DEPTNO DNAME
---------- ------------------------------------------
LOC
---------------------------------------
20 dept1
loc1

30 dept2
loc2


Then run DBMS_HS_PASSTHROUGH to make changes -


SQL> declare
vn_nr INTEGER;
begin
vn_nr := DBMS_HS_PASSTHROUGH.execute_immediate@SYSC0_1123('update tkhodept set dname = ''dept3'' where dname = ''dept1'' ');
dbms_output.put_line('rows affected: '||vn_nr);
end; 
/
rows affected: 0

PL/SQL procedure successfully completed.

but rows have been updated -

SQL> select * from tkhodept@SYSC0_1123 ;

DEPTNO DNAME
---------- ------------------------------------------
LOC
---------------------------------------
20 dept3
loc1

30 dept2
loc2


SQL>


The same result is returned if an insert is made into the remote table.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms