5 Replies Latest reply: Oct 1, 2012 5:12 AM by 452512 RSS

    Calling Postgres function over DB link?

    493168
      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
          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
            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
              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
                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
                  Instead of Execute&Fetch, try the following procedure:

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

                  BR,
                  Martin