This content has been marked as final. Show 5 replies
Hi Bart,1 person found this helpful
The DG4ODBC does not support calling remote stored procedures so this will never work. Because it is a 'generic' gateway designed to be used with many different data sources it does not have this functionality as the non-Oracle databases may not have stored procedures.
This is mentioned in the documentation -
Oracle® Database Gateway for ODBC User’s Guide 11g Release 2 (11.2)
Page 2-2 -
If you encounter incompatibility problems not listed in this section or in "Known
Problems" on page 2-3, contact Oracle Support Services. The following section
describes the known restrictions:
Does not support stored procedures
It also applies to the earlier 11.1 DG4ODBC and also 10.2 HSODBC.
Thanks for replay; you are right- it doesn't support procedures- directly, however- I've found a procedure DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE- it seems, it covers all my needs! I belive a combination Oracle Express with Postgres via ODBC Gateway may be very interesting..
Yes, DBMS_HS_PASSTHROUGH will allow you to run anything directly on the non-Oracle database so could be used to replace whatever the procedure was doing.
Well, there is a one 'little' problem- I am not able to bind variables:
m_strVal Varchar2(10) := 'sth';
c := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@PG;
DBMS_HS_PASSTHROUGH.PARSE@PG(c, 'select PG_function(?)');
nr := DBMS_HS_PASSTHROUGH.FETCH_ROW@PG(c);
DBMS_HS_PASSTHROUGH.GET_VALUE@PG(c, 1, val);
Is there any way I could bing variables to function?
Instead of Execute&Fetch, try the following procedure: