This discussion is archived
5 Replies Latest reply: Oct 1, 2012 3:12 AM by 452512 RSS

Calling Postgres function over DB link?

493168 Newbie
Currently Being Moderated
Hi,

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?

Regards
Bart Dabr
  • 1. Re: Calling Postgres function over DB link?
    mkirtley-Oracle Expert
    Currently Being Moderated
    Hi Bart,
    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 -

    Known Restrictions
    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.

    Regards,
    Mike
  • 2. Re: Calling Postgres function over DB link?
    493168 Newbie
    Currently Being Moderated
    Hi,

    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..

    Regards
    Bart Dabr
  • 3. Re: Calling Postgres function over DB link?
    mkirtley-Oracle Expert
    Currently Being Moderated
    HI Bart,
    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.

    Regards,
    Mike
  • 4. Re: Calling Postgres function over DB link?
    493168 Newbie
    Currently Being Moderated
    Hi,

    Well, there is a one 'little' problem- I am not able to bind variables:

    DECLARE
    val VARCHAR2(100);
    c INTEGER;
    nr INTEGER;
    m_strVal Varchar2(10) := 'sth';
    BEGIN
    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);
    DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@PG(c);
    END;

    Is there any way I could bing variables to function?

    Regards
    Bart Dabr
  • 5. Re: Calling Postgres function over DB link?
    452512 Newbie
    Currently Being Moderated
    Instead of Execute&Fetch, try the following procedure:

    DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY@PG(c,1,m_strVal);

    BR,
    Martin

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points