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 220.127.116.11.0 Oracle database i get this:
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 18.104.22.168.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.
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> desc dba.View_Asset_ServiceStats@promaster
Name Null? Type
----------------------------------------- -------- ----------------------------
And in the test database which is 22.214.171.124.0 it shows them as varchar:
SQL*Plus: Release 126.96.36.199.0 Production on Wed Aug 22 08:44:46 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Oracle Database 11g Enterprise Edition Release 188.8.131.52.0 - 64bit Production
- 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
ODBC Driver = SQL Anywhere 11 version 11.00.01.2044
[ODBC 32 bit Data Sources]
PRO_ODBC=SQL Anywhere 11 (32 bit)
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
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.
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.
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.
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.