1 2 Previous Next 21 Replies Latest reply: Aug 30, 2012 6:33 AM by Kgronau-Oracle RSS

    Problems with dg4odbc after 11gr2 upgrade

    957289
      Hi

      Hi

      I have a dg4odbc setup through oracle gateway services which allows a connection to a sybase database.

      On our Oracle 10.2.0.5 database this works fine, eg:

      2991 23-JAN-12 04-APR-11

      on the 11.2.0.2.0 Oracle database i get this:

      2991 ¿

      The date field doesn't work just comes up with the squares.

      when i try and run a query on this database link I get this error:

      ORA-28528: Heterogeneous Services datatype conversion error
      ORA-02063: preceding line from 8888
      28528. 00000 - "Heterogeneous Services datatype conversion error"
      *Cause:    Either an Oracle datatype could not be converted to a non-Oracle
      datatype, or a non-Oracle datatype could not be converted
      to an Oracle datatype. The following are possible reasons for
      for the conversion failure:
      -- overflow problems (in the case of numbers)
      -- length limitations (in the case of character strings)
      -- invalid values passed into the conversion routines
      *Action:   Contact customer support of the agent vendor. If the problem is
      due to size discrepancies between Oracle and the non-Oracle system,
      it may not be possible to convert the value.

      The ODBC connection is onthe same windows 2003 server as the Oracle 11.2.0.2.0 database

      something that I've just noticed on the table through odbc, through the database link on our 10.2.0.5 database, the database sees the columns in the table as dates:


      SQL*Plus: Release 10.2.0.5.0 - Production on Wed Aug 22 09:04:34 2012

      Copyright (c) 1982, 2010, Oracle. All Rights Reserved.


      Connected to:
      Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
      With the Partitioning, OLAP, Data Mining and Real Application Testing options

      SQL>
      SQL> desc dba.View_Asset_ServiceStats@promaster
      Name Null? Type
      ----------------------------------------- -------- ----------------------------
      Lo_Code NUMBER(10)
      Maintenance_Contract_Ref VARCHAR2(10)
      Maintenance_Contract VARCHAR2(60)
      Last_Service_Date DATE
      Previous_Service_Date DATE
      Last_Service_Over_12_Months NUMBER(5)

      And in the test database which is 11.2.0.2.0 it shows them as varchar:

      SQL*Plus: Release 11.2.0.2.0 Production on Wed Aug 22 08:44:46 2012

      Copyright (c) 1982, 2010, Oracle. All rights reserved.


      Connected to:
      Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

      SQL> desc dba.View_Asset_ServiceStats@promaster
      Name Null? Type
      ----------------------------------------- -------- ---------------------------
      Lo_Code NUMBER(10)
      Maintenance_Contract_Ref VARCHAR2(10)
      Maintenance_Contract VARCHAR2(60)
      Last_Service_Date VARCHAR2(16)
      Previous_Service_Date VARCHAR2(16)
      Last_Service_Over_12_Months NUMBER(5)

      I've searched for hours on the net and can't find any resolutions so please help

      Thanks in advance
      Paul
        • 1. Re: Problems with dg4odbc after 11gr2 upgrade
          damorgan
          Looks like a character set issue. Describe, in detail, how the upgrade was done.
          • 2. Re: Problems with dg4odbc after 11gr2 upgrade
            Srini Chavali-Oracle
            Pl also post the version of the gateway you are using - such errors can happen if you are using a lower gateway version to access a higher version database

            HTH
            Srini
            • 3. Re: Problems with dg4odbc after 11gr2 upgrade
              Mkirtley-Oracle
              Hi,
              Could you post the following information -

              - What is the character set used by the 11.2 Oracle database and by the Sybase database ?
              - the DG4ODBC init<sid>.ora
              - full name and version of the ODBC driver
              - full name and version of the ODBC driver manager
              - the odbc.ini entry for the Sybase database

              Regards,
              Mike
              • 4. Re: Problems with dg4odbc after 11gr2 upgrade
                Mkirtley-Oracle
                Hi,
                If it isn't a NLS issue you may also be hitting the problem in this note available in MY Oracle Support -

                Datatype Conversion Error ORA-28528 Using Dg4odbc With Informix, MySQL or SQL*Server 64-bit ODBC Drivers on Unix Platforms (Doc ID 554409.1)

                Try adding the following to the DG4ODBC init<sid>.ora file and see if either resolves the problem -

                In 11.2 there is a new parameter which allows 64bit drivers to follow the 32bit standard so they do not need to be re-compiled - HS_FDS_SQLLEN_INTERPRETATION.

                To allow a 64-bit driver to follow the 32-bit standard add the following to init<dg40bdc_sid>.ora -

                HS_FDS_SQLLEN_INTERPRETATION=32

                To follow the 64-bit standard add -

                HS_FDS_SQLLEN_INTERPRETATION=64

                After making the change to the file try a select from a new SQLPLUS session.

                Regards,
                Mike
                • 5. Re: Problems with dg4odbc after 11gr2 upgrade
                  957289
                  Thanks for your responses. I'll try and give a bit more info:

                  Just to such on Srini's point the gateway version is 11.2.0.1.0 if that could be the problem??

                  init<sid>.ora
                  HS_FDS_CONNECT_INFO = PRO_ODBC
                  HS_FDS_TRACE_LEVEL = DEBUG

                  ODBC Driver = SQL Anywhere 11 version 11.00.01.2044

                  ODBC.ini entry
                  [ODBC 32 bit Data Sources]
                  PRO_ODBC=SQL Anywhere 11 (32 bit)
                  [PRO_ODBC]
                  Driver32=C:\Program Files\SQL Anywhere 11\bin64\dbodbc11.dll

                  THe NLS_LANG on the Oracle database is set to ENGLISH (if that's the characterset?) but I can't find the sybase character set I'm sorry

                  I've tried this HS_FDS_SQLLEN_INTERPRETATION=32 as 32 and 64 but in the trace file it says it ignores it becuase the number given is invalid:
                  from trace file -
                  Value of 64 given for HS_FDS_SQLLEN_INTERPRETATION on a 32 bit platform; ignored!
                  treat_SQLLEN_as_compiled = 1

                  Thanks again
                  Paul
                  • 6. Re: Problems with dg4odbc after 11gr2 upgrade
                    Mkirtley-Oracle
                    Hi,
                    You haven't said on which platform you are running DG4ODBC. If it is a 64-bit Unix or Linux platform then you should be running 64-bit Dg4ODBC and this needs a 64-bit driver. 64-bit DG4ODBC will not work correctly (if at all) with a 32-bit ODBC driver.
                    It it's 32-bit Linux then a 32-bit driver will be okay.
                    For the NLS issues what we need to see is the value of the entry NLS_CHARACTERSET from the query -

                    select * from NLS_DATABASE_PARAMETERS ;

                    You will have to check with the Sybase DBA about the equivalent on Sybase.

                    Regards,
                    Mike
                    • 7. Re: Problems with dg4odbc after 11gr2 upgrade
                      Richard Harrison .
                      Hi,
                      Just to clarify your 10.2 and 11.2 oracle db's are on the same windows box?

                      If not have you an odbc test tool to just see what data that sees on the 11.2 server - does that have problems too? Maybe the driver is a different version and can;t understand the sybase datatype rather than it being an oracle issue.

                      If they are on the same box then the odbc driver must be fine i guess.

                      Cheers,
                      Harry
                      • 8. Re: Problems with dg4odbc after 11gr2 upgrade
                        957289
                        Hi

                        Sorry for the late reply.

                        The platform is a 64bit windows 2003 server. the character set is WE8ISO8859P1. For sybase, i can't find the character set I'm sorry, we don't have a sybase dba and I don't have the administrators password to try the commands to find it out.

                        The 10.2 database that works is on a different box than the 11.2 database. they are the same spec though. the 11.2 db is basically a test version of the database that is on the 10.2 so we're testing upgrading to it to 11.2 by upgrading it on the test db server first.

                        Like i say this works fine on the 10.2 database, the driver is the same, it's pointing at the same sybase database the only difference is the fact that the 11.2 database doesn't work and the 10.2 database does work. And when i say it doesn't work, it is only dates that are a problem.

                        Thanks
                        Paul
                        • 9. Re: Problems with dg4odbc after 11gr2 upgrade
                          Richard Harrison .
                          Hi,
                          Have you traced the 10 and 11 odbc calls to see if they look the same?

                          The metalink note 252548.1 shows you what you need to do to see what dataypes it thinks it is getting back.

                          Cheers,
                          Harry
                          • 10. Re: Problems with dg4odbc after 11gr2 upgrade
                            957289
                            do you mean the trace in the hs/trace folder? I've only turned debug on for the test database as the other is live. i could try that on the live server if it would give us more info?

                            And i'm really sorry but where do access that metalink note? i can never seem to find them through the search engine

                            Thanks
                            Paul
                            • 11. Re: Problems with dg4odbc after 11gr2 upgrade
                              Richard Harrison .
                              Hi,
                              Sorry metalink is the old name for oracle support (still can't shake it off).

                              Just go to support.oracle.com and search for that id inside the support tool.

                              Cheers,
                              Harry
                              • 12. Re: Problems with dg4odbc after 11gr2 upgrade
                                957289
                                ah right. Unfortunately I don't have access to this website because our oracle licence's are through our software provider and they are the only one's who have access to oracle support so I can't log into there.

                                Cheers anyway
                                Paul
                                • 13. Re: Problems with dg4odbc after 11gr2 upgrade
                                  957289
                                  I've compared the test server to the live server and noticed this in the trace files.

                                  i've ran exactly the same query on both Oracle databases and the 10g says this in the trace:

                                  SQLGetInfo returns 128 for SQL_MAX_SCHEMA_NAME_LEN
                                  SQLGetInfo returns 128 for SQL_MAX_TABLE_NAME_LEN
                                  SQLGetInfo returns 128 for SQL_MAX_PROCEDURE_NAME_LEN
                                  SQLGetInfo returns " (0x22) for SQL_IDENTIFIER_QUOTE_CHAR
                                  SQLGetInfo returns Y for SQL_COLUMN_ALIAS
                                  15 instance capabilities will be uploaded
                                  capno:1989, context:0x00000000, add-info: 0
                                  capno:1991, context:0x0001ffff, add-info: 0
                                  capno:1992, context:0x0001ffff, add-info: 0
                                  capno:3042, context:0x00000000, add-info: 0, translation:"42"
                                  capno:3047, context:0x00000000, add-info: 0, translation:"57"
                                  capno:3049, context:0x00000000, add-info: 0, translation:"59"
                                  capno:3050, context:0x00000000, add-info: 0, translation:"60"
                                  capno:3066, context:0x00000000, add-info: 0
                                  capno:3067, context:0x00000000, add-info: 0
                                  capno:3068, context:0x00000000, add-info: 0
                                  capno:3069, context:0x00000000, add-info: 0
                                  capno:3500, context:0x00000001, add-info: 91, translation:"42"
                                  capno:3501, context:0x00000001, add-info: 93, translation:"57"
                                  capno:3502, context:0x00000001, add-info: 107, translation:"59"
                                  capno:3503, context:0x00000001, add-info: 110, translation:"60"
                                  Exiting hgoulcp, rc=0 at 2012/08/29-08:40:13

                                  and on the 11g database it only finds the top three before any translations? :

                                  SQLGetInfo returns 128 for SQL_MAX_SCHEMA_NAME_LEN
                                  SQLGetInfo returns 128 for SQL_MAX_TABLE_NAME_LEN
                                  SQLGetInfo returns 128 for SQL_MAX_PROCEDURE_NAME_LEN
                                  SQLGetInfo returns " (0x22) for SQL_IDENTIFIER_QUOTE_CHAR
                                  SQLGetInfo returns Y for SQL_COLUMN_ALIAS
                                  3 instance capabilities will be uploaded
                                  capno:1989, context:0x00000000, add-info: 0
                                  capno:1991, context:0x0001ffff, add-info: 0
                                  capno:1992, context:0x0001ffff, add-info: 0
                                  Exiting hgoulcp, rc=0 at 2012/08/29-08:59:39
                                  • 14. Re: Problems with dg4odbc after 11gr2 upgrade
                                    Richard Harrison .
                                    Hmmm looks like its behaving differently with 11g - can you try a 'newer' version of the sybase driver (if there is one - don't know what versions of sybase are available) - perhaps that might help?
                                    1 2 Previous Next