【YashanDB知识库】oracle dblink varchar类型查询报错记录

问题单：Oracle DBLINK查询崖山DB报错

oracle服务器上ODBC安装

unixodbc安装：yum -y install unixODBC

mysql

配置安装对应版本的odbc：

myodbc-installer -d -a -n “MySQL8.0” -t “DRIVER=/home/oracle/tools/mysql-connector-odbc-8.0.20/lib/libmyodbc8w.so;SETUP=/home/oracle/tools/mysql-connector-odbc-8.0.20/lib/myodbc8S.so”

yashandb

安装yasdb-odbc

安装yasdb-client

配置odbc驱动：

sudo vi /etc/odbcinst.ini
[YashanDB] Description=ODBC for yashanDB
Driver=/home/oracle/xp/yashandb-odbc/libyas_odbc.so
Setup=/home/oracle/xp/yashandb-odbc/libyas_odbc.so
Driver64=/home/oracle/xp/yashandb-odbc/libyas_odbc.so
Setup64=/home/oracle/xp/yashandb-odbc/libyas_odbc.so FileUsage=1
 
[ODBC]
Trace=NO/YES #yes打开odbc日志
TraceFile=/home/oracle/xp/odbc.log

添加环境变量：

vi .bashrc
export LD_LIBRARY_PATH=LD_LIBRARY_PATH:/home/oracle/xp/yashandb-odbc

配置数据源

sudo vi /etc/odbc.ini
[YASDBODBC]
Description = YASODBC
Driver = YashanDB
SERVER = 192.168.18.207 PORT = 8688
USER = sys
PWD = yasdb_123
 
[MYSQLDB]
Description = MySQL ODBC 5.0 Unicode Driver
Driver = MySQL8.0
Server = 192.168.7.134
Database = zabbix
Port = 3306
USER = zabbix
Password = 123456
Option = 3
CHARSET = UTF8

配置oracle dblink环境

监听文件配置

/data/app/oracle/product/19.3/dbhome_1/network/admin

vi listener.ora

新增：

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = YASDBODBC)
(ORACLE_HOME = /data/app/oracle/product/19.3/dbhome_1)
(PROGRAM = dg4odbc)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = MYSQLDB)
(ORACLE_HOME = /data/app/oracle/product/19.3/dbhome_1)
(PROGRAM = dg4odbc)
)
)

坑1：

配置这个后，oracle的远程连接报错。

修改如下：

listener.ora有三个部分

详解各个属性：

1、LISTENER部分包含协议地址列表，每个实例一个入口，监听名称，可以配置多个监听，多个监听的端口号要区分开来

2、SID_LIST_LISTENER部分标识全局数据库名称、标识监听程序正在服务的每个实例的Oracle软件主目录以及实例或SID。

3、SID_LIST描述用于静态数据库注册、保持和以前的版本兼容性以及供Oracle Enterprise Manager使用，因为我本地有三个数据库

把SID_DESC 放到SID_LIST_LISTENER中

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = oracledb)
      (ORACLE_HOME = /data/app/oracle/product/19.3/dbhome_1)
      (SID_NAME = oracledb)
    )
     
    (SID_DESC =
      (SID_NAME = YASDBODBC)
      (ORACLE_HOME = /data/app/oracle/product/19.3/dbhome_1)
      (PROGRAM = dg4odbc)
    )
     
    (SID_DESC =
      (SID_NAME = MYSQLDB)
      (ORACLE_HOME = /data/app/oracle/product/19.3/dbhome_1)
      (PROGRAM = dg4odbc)
    )
  )

配置tns文件

vi tnsnames.ora

新增：

YASDBODBC =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.7.134)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = YASDBODBC)
)
(HS = OK)
)
MYSQLDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.7.134)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = MYSQLDB)
)
(HS = OK)
)

配置odbc监听

cd /data/app/oracle/product/19.3/dbhome_1/hs/admin

mysql

添加文件：

touch initMYSQLDB.ora
vim initMYSQLDB.ora
 
HS_FDS_CONNECT_INFO = MYSQLDB
HS_FDS_TRACE_LEVEL = debug
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
HS_LANGUAGE = AMERICAN_AMERICA.US7ASCII
HS_NLS_NCHAR = UCS2
#HS_FDS_SUPPORT_STATISTICS = FALSE
 
set ODBCINI= /etc/odbc.ini

yashandb

touch initYASDBODBC.ora
vim initYASDBODBC.ora
 
HS_FDS_CONNECT_INFO = YASDBODBC
HS_FDS_TRACE_LEVEL = debug
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
HS_LANGUAGE = AMERICAN_AMERICA.US7ASCII
HS_NLS_NCHAR = UCS2
#HS_FDS_SUPPORT_STATISTICS = FALSE
 
set ODBCINI= /etc/odbc.ini

重启监听

lsnrctl stop
lsnrctl start
lsnrctl status

创建链接异构数据库连接

在yashandb和mysql上建表如下：

drop table T1;
create table T1(c1 varchar(2001) ,c2 int,c3 double);
insert into T1 values('book1',51,1),('book2',22,2),('book3',66,3),(NULL,71,4),('book4',NULL,5);

mysql

drop database link TEST_MYSQL;
create database link TEST_MYSQL connect to "zabbix" IDENTIFIED by "123456" using 'MYSQLDB' ;
 
SELECT * FROM T1@TEST_MYSQL；
 
SELECT * FROM T1@TEST_MYSQL where "c3"=1 ;
SELECT * FROM T1@TEST_MYSQL where "c2"=51 ;
SELECT * FROM T1@TEST_MYSQL where cast("c1" as varchar(200))='book1';

yashandb

drop database link YASDBODBC_TEST;
create database link YASDBODBC_TEST connect to "sys" identified by "yasdb_123" using 'YASDBODBC';
 
SELECT * FROM T1@YASDBODBC_TEST；
 
SELECT * FROM T1@YASDBODBC_TEST where c1='book1';
SELECT * FROM T1@YASDBODBC_TEST where c2=51 ;
SELECT * FROM T1@YASDBODBC_TEST where c3=1 ;

问题原因

dblink使用odbc连接其它数据库，如yashandb、mysql等

● 把其它数据库的varchar类型会变成oralce中的nvarchar2类型，如下组图1。

● nvarchar2类型2个字节存储一个字符，超过varchar(2000)的字段类型因为超过了4000个字节，在oracle中变成了long类型，如下组图2。

● long类型不能在where、group by、order by中使用。