5 Replies Latest reply on Jun 24, 2019 1:32 PM by mxallen-Oracle

    Data view for SQL server tables from Oracle

    3931344

      Hi,

       

      I am trying to query a table XYZ (included 22 columns) in SQL server from Oracle via Heterogeneous Gateway, but it has missing a lot of fields in the query result.   

      Specifically, I ran almost identical SQLs below for same table, one is specified dbo schema and other one is not. In reference, the table name is unique.

           ex) SELECT * FROM dbo.XYZ@DB_Link; --> It returned data for only 4 fields. (Total 22 fields exist in SQL server source table)

                 SELECT * FROM        XYZ@DB_Link; --> It returned data for only 14 fields. (Total 22 fields exist in SQL server source table)

       

      Questions :

      1. Why both SQLs above return two different result sets in Oracle query and why 2nd query (without specified schema name) has more columns returned?

      2. Why both SQLs can't get full columns (total 22) and actually missing lots of fields in Oracle query?

      3, I noticed that oracle query couldn't retrieve fields like nvarchar(max) fields in Oracle. Why?

      4. In addition, why 1st query has no issue to view field name having over 30 characters but 2nd query doesn't allow over 30 characters of field name.

           (We currently have Oracle 12c, which allows 128 characters)

        • 1. Re: Data view for SQL server tables from Oracle
          EdStevens

          3931344 wrote:

           

          Hi,

           

          I am trying to query a table XYZ (included 22 columns) in SQL server from Oracle via Heterogeneous Gateway, but it has missing a lot of fields in the query result.

          Specifically, I ran almost identical SQLs below for same table, one is specified dbo schema and other one is not. In reference, the table name is unique.

          ex) SELECT * FROM dbo.XYZ@DB_Link; --> It returned data for only 4 fields. (Total 22 fields exist in SQL server source table)

          SELECT * FROM XYZ@DB_Link; --> It returned data for only 14 fields. (Total 22 fields exist in SQL server source table)

           

          Questions :

          1. Why both SQLs above return two different result sets in Oracle query and why 2nd query (without specified schema name) has more columns returned?

          2. Why both SQLs can't get full columns (total 22) and actually missing lots of fields in Oracle query?

          3, I noticed that oracle query couldn't retrieve fields like nvarchar(max) fields in Oracle. Why?

          4. In addition, why 1st query has no issue to view field name having over 30 characters but 2nd query doesn't allow over 30 characters of field name.

          (We currently have Oracle 12c, which allows 128 characters)

          Many sites block attachments.  Many people refuse to open them for the same reason others block them.  I doubt there is any legitimate content in your attachment that cannot be pasted directly into a message on this forum.

           

          My guess would be that XYZ refers to a view, not a table, and that dbo.xyz is not the same view/table as simply xyz.

          • 2. Re: Data view for SQL server tables from Oracle
            3931344

            XYZ is a table in SQL server. In my environment, I tried to read the table with SQL script like select * from dbo.XYZ@db_link in Oracle without any physical data movement.

            Using this SQL, I created a view but the problem is I can only see the some fields partially from XYZ but not all columns included in the source table.

            Another problem is the table name is unique but why the query results are not same when query doesn't include dbo schema.

            Also, field nanmes not allow to exceed 30 characters. Please see my original question for more details.

            • 3. Re: Data view for SQL server tables from Oracle
              mxallen-Oracle

              With the query that returns 14 columns .... do the 8 columns that are not returned all have name greater than 30 characters in length?

               

              Please confirm the exact version of the gateway product you are using. Is it DG4ODBC or DG4MSQL?

               

              Matt

              • 4. Re: Data view for SQL server tables from Oracle
                3931344

                Here is our version. DG4ODBC

                Now output fields are same on with schema and w/o schema  in the sql scripts but still an issue for the field name size 30 characters limited for the query w/o schema specified.

                 

                • 5. Re: Data view for SQL server tables from Oracle
                  mxallen-Oracle

                  What version of DG4ODBC are you using?

                  The exact version number?

                   

                  Matt