sql数据类型转换oracle,sql server 与 oracle 数据类型转换

【摘录】

Oracle transparent gateway不能访问SQL Server bigint类型

Oracle环境：

oracle9.2.0.4、solaris9(64)

SQL Server环境

SQL Server2000、window2003

transparent gateway已经配置好了可以访问到SQL Server2000，但是在oracle这端看不到SQL Server2000的bigint和uniqueidentifier类型的数据，bigint都显示成0，uniqueidentifier成null，后来在metalink上查了一下，发现这是一个bug，正文如下：

QUOTE:

--------------------------------------------------------------------------------

The information in this document applies to:

Oracle Transparent Gateway for Microsoft SQL Server - Version: 9.2 to 10.1

Information in this document applies to any platform.

Goal

Can the MSQL datatypes BIGINT and UNIQUEIDENTIFIER be selected using the TG4MSQL ?

Solution

The TG4MSQL 10.1 supports both datatypes but this is not in the documentation. It will be included in the documentation for TG4MSQL 10.2 when it is released.

The datatypes are translated as follows -

MS SQL Server Data Type Oracle Data Type

---------------------------------------------------------------

bigint NUMBER(19)

uniqueidentifier CHAR(36)

For TG4MSQL 9.2 it is necessary to apply a patch for UNIQUEIDENTIFIER support. To get this patch follow these steps -

- login to Metalink

- choose 'Patches and Updates' from the menu on the left.

- choose 'Simple Search'

- enter patch number 2477384 and platform 'Microsoft Windows (32-bit)'

- download and apply patch

There is no support for these datatypes in v4 or v8 versions of TG4MSQL.

--------------------------------------------------------------------------------

这个补丁只是解决了uniqueidentifier，但没有解决bigint问题，打上补丁后确实如此。

既然说了10.2版后已经解决这个问题了，那就下个10.2的gateway吧，装完改完配置，再做查询发现，oracle的sql一直不能执行完成，后发现hsV$_*表被一直锁定，杀session都不行，只能杀系统进程才行。

目前metalink帐号过期了，没法从metalink上查资料了，在google里没有查到相关问题，好像大家都没遇到过bigint问题！？

各位，谁有遇到过相同问题并解决的，烦劳告诉一声，先谢谢了！！！