This discussion is archived
1 2 Previous Next 21 Replies Latest reply: Aug 30, 2012 4:33 AM by kgronau RSS

Problems with dg4odbc after 11gr2 upgrade

957289 Newbie
Currently Being Moderated
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 Oracle ACE Director
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 . Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 . Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 . Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 . Expert
    Currently Being Moderated
    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

Legend

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