Oracle Gateway connection to Sybase not working after 11g Database upgrade

Posted on 2012-08-21
Last Modified: 2012-08-30

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

On our Oracle database this works fine, eg:

2991      23-JAN-12       04-APR-11

on the 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 database

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

Question by:WPHIT

    Author Comment

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

    SQL*Plus: Release - 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 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    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 it shows them as varchar:

    SQL*Plus: Release 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 - 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)

    Accepted Solution


    Author Closing Comment

    sorted on other forum

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now