My Oracle Support Banner

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

Last updated on FEBRUARY 04, 2019

Applies to:

Oracle Database Gateway for DRDA - Version 11.2.0.1 to 11.2.0.3 [Release 11.2]
Oracle Database - Enterprise Edition - Version 11.2.0.1 to 11.2.0.3 [Release 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 dept@DBLINK_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@DBLINK_1123('update dept 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 dept@DBLINK_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

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.