I have established a link to Postgres DB (over ODBC Gateway), I am able to select data from Postgres tables (SQL Developer :) )- I would like to call a Postgres function- I have tried combinations with RETNUM@PGDB() [where retnum() is my function in Postgres, and PGDB is a working link]- but I'am unable to communicate with it- could you give me a hint, how to do it?
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..
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(?)'); DBMS_HS_PASSTHROUGH.BIND_VARIABLE@PG(c,1,m_strVal);
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?