0 Rows Returned When Using DBMS_HS_PASSTHROUGH.Execute_immediate with DG4ODBC and PostgreSQL
(Doc ID 1488795.1)
Last updated on AUGUST 26, 2021
Applies to:
Oracle Database - Enterprise Edition - Version 11.2.0.3 and laterInformation in this document applies to any platform.
Symptoms
When using DBMS_HS_PASSTHROUGH.Execute_immediate with Database Gateway for ODBC (DG4ODBC) on Linux x86-64 against a PostgreSQL 08.04.0009 the results are returned as '0 rows' even though rows are changed.
The ODBC driver is Data Direct ddpsql26.so version 07.00.0030.
The patch for 12731763 which should resolve the problem has been applied.
For example -
SQL select * from "public"."test2"@GATEWAY;
- 12 rows returned
SQL DECLARE
2 num_rows integer;
3 BEGIN
4 num_rows:=DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@GATEWAY('insert into
"public"."test2" values (7777,''2012-07-31'', ''2012-07-31'', ''test'',
''test'')');
5 dbms_output.put_line('NUM_ROWS: '||num_rows);
6 END;
7 /
NUM_ROWS: 0
PL/SQL procedure successfully completed.
SQL commit;
Commit complete.
SQL select * from
"public"."test2"@GATEWAY;
- 13 rows returned including the new row inserted
- 12 rows returned
SQL DECLARE
2 num_rows integer;
3 BEGIN
4 num_rows:=DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@GATEWAY('insert into
"public"."test2" values (7777,''2012-07-31'', ''2012-07-31'', ''test'',
''test'')');
5 dbms_output.put_line('NUM_ROWS: '||num_rows);
6 END;
7 /
NUM_ROWS: 0
PL/SQL procedure successfully completed.
SQL commit;
Commit complete.
SQL select * from
"public"."test2"@GATEWAY;
- 13 rows returned including the new row inserted
The parameter 'NUM_ROWS' should report the number of rows affected by the DBMS_HS_PASSTHROUGH statement. In this case it shoule be -
NUM_ROWS: 1
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 |