3 Replies Latest reply: Aug 14, 2012 5:57 AM by Mkirtley-Oracle RSS

    Problem with dg4msql and table-valued functions

    639216
      Have configured dg4msql to connect from my oracle db to ms sql server.
      Am able to do simple SELECTs from ms sql tables like:
      select * from "sys"."procedures"@dg4msql;
      or
      select * from "dbo"."SomeTable"@dg4msql;

      But am unable to do a SELECT from a table-valued function:
      select * from "dbo"."GetData"@dg4msql('param1value');
      or
      select * from "dbo"."GetData"('param1value')@dg4msql;

      In both cases I get:
      ORA-00933: SQL command not properly ended

      It does not like the parameters portion of the query ("('param1value')")

      initdg4msql.ora:
      HS_FDS_CONNECT_INFO=[svr1]//mydb
      HS_FDS_TRACE_LEVEL=OFF
      HS_FDS_PROC_IS_FUNCT=TRUE
      HS_FDS_RESULTSET_SUPPORT=TRUE

      Have tried the other set of params:

      HS_FDS_PROC_IS_FUNCT=FALSE
      HS_FDS_RESULTSET_SUPPORT=TRUE

      Same story. After changing the init*** file have bounced both Listeners (DB and Gateway), reconnected, and re-run the query.
      Have I missed something?

      Any help is greatly appreciated!
        • 1. Re: Problem with dg4msql and table-valued functions
          Mkirtley-Oracle
          Hi,
          The documentation -

          Oracle® Database
          Heterogeneous Connectivity User’s Guide
          11g Release 2 (11.2)

          discusses calling remote functions in the section -

          4.6 Executing User-Defined Functions on a Non-Oracle Database

          You can execute user-defined functions in a remote non-Oracle database. For example:
          SELECT getdeptforemp@Remote_DB(7782) FROM dual;
          In this example, a SELECT statement was issued that executes a user-defined function
          in the remote database that returns department information for employee 7782.

          and also discusses other ways of calling them.

          You also need to define the function name and owner in the gateway parameter HS_CALL_NAME -

          HS_CALL_NAME = "owner1.A1, owner2.A2 "

          Can you try the syntax suggested and let us know what happens ?

          Regards,
          Mike
          • 2. Re: Problem with dg4msql and table-valued functions
            639216
            Sorry, but for me it looks you did not get the problem.
            Oracle® Database Gateway for SQL Server User's Guide,:
            11g Release 2 (11.2)
            Part Number E12069-02

            *2 SQL Server Gateway Features and Restriction*

            Result Sets and Stored Procedures

            The Oracle Database Gateway for SQL Server provides support for stored procedures which return result sets.

            By default, all stored procedures and functions do not return a result set to the user. To enable result sets, set the HS_FDS_RESULTSET_SUPPORT parameter value to TRUE.

            PL/SQL Program Fetching from Result Sets in Sequential Mode
            -- Execute procedure
            out_arg := null;
            refcurproc@MSQL('Hello World', out_arg, rc1);
            Somewhere in this forum I've seen a message that the syntax "SELECT ... FROM sp@db(param1, param2)" works.
            Anyway, even with the PL/SQL block the error message is the same - ORA-00933 "SQL command not properly ended"
            and the cursor (* in SQL*PLUS) is put just at the first bracket.

            Edited by: user636213 on Aug 10, 2012 5:17 AM
            • 3. Re: Problem with dg4msql and table-valued functions
              Mkirtley-Oracle
              Hi,
              Can you confirm if you are using a SQL*Server stored procedure or a function ?
              If it is a function did you define the function name and owner in the gateway parameter HS_CALL_NAME -

              HS_CALL_NAME = "owner1.A1, owner2.A2 "

              Can you post the exact syntax you used for the PL/SQL procedure ?
              It would help if you could give a simple test case we can set up on our systems to test further.

              Regards,
              Mike