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

简介: 这篇文章主要介绍了 Oracle DBLINK 查询崖山 DB 报错的相关内容,包括 ODBC 安装配置、数据源配置、dblink 环境配置、问题原因分析及规避方法。问题原因是 dblink 连接其他数据库时 varchar 类型转换导致的,还介绍了 long 类型限制、char 等类型区别,规避方法是修改参数 MAX_STRING_SIZE 支持 32K。

问题单: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中使用。

问题复现

组图1

mysql中的表类型:

oracle中的表类型:

查询正常:

组图2

mysql中的表类型:

oracle中的表类型:

查询报错:

oracle long类型限制

  • LONG数据类型中存储的是可变长字符串,最大长度限制是2GB。

  • 对于超出一定长度的文本,基本只能用LONG类型来存储,数据字典中很多对象的定义就是用LONG来存储的。

  • LONG类型主要用于不需要作字符串搜索的长串数据,如果要进行字符搜索就要用varchar2类型。

  • 很多工具,包括SQLPlus,处理LONG 数据类型都是很困难的。

  • LONG数据类型的使用中,要受限于磁盘的大小。

能够操作LONG的SQL语句:

  • Select语句

  • Update语句中的SET语句

  • Insert语句中的VALUES语句

限制:

  • 一个表中只能包含一个 LONG 类型的列。

  • 不能索引LONG类型列。

  • 不能将含有LONG类型列的表作聚簇。

  • 不能在SQLPlus中将LONG类型列的数值插入到另一个表格中,如insert into …select。

  • 不能在SQL*Plus中通过查询其他表的方式来创建LONG类型列,如create table as select。

  • 不能对LONG类型列加约束条件(NULL、NOT NULL、DEFAULT除外),如:关键字列(PRIMARY KEY)不能是 LONG 数据类型。

  • LONG类型列不能用在Select的以下子句中:where、group by、order by,以及带有distinct的select语句中。

  • LONG类型列不能用于分布查询。

  • PL/SQL过程块的变量不能定义为LONG类型。

  • LONG类型列不能被SQL函数所改变,如:substr、instr。 因为long类型的数值不能用insert into … select的方法插入,故我们要分两步走,先插入其它字段,最后再插入long类型字段,这可以通过过程来实现.

参考:oracle里long类型的总结_oracle long-CSDN博客

char类型区别

char

1、char的长度是固定的。比如说,你定义了char(20),即使你你插入abc,不足二十个字节,数据库也会在abc后面自动加上17个空格,以补足二十个字节;

2、char是区分中英文的。中文在char中占两个字节,而英文占一个,所以char(20)你只能存20个字母或10个汉字。

char适用于长度比较固定的,一般不含中文的情况。

varchar/varchar2

1、varchar是长度不固定的。比如说,你定义了varchar(20),当你插入abc,则在数据库中只占3个字节。

2、varchar同样区分中英文。这点同char。

3、varchar2基本上等同于varchar。它是oracle自己定义的一个非工业标准varchar,不同在于,varchar2用null代替varchar的空字符串。

varchar/varchar2适用于长度不固定的,一般不含中文的情况。

nvarchar/nvarchar2

1、nvarchar和nvarchar2是长度不固定的;

2、nvarchar不区分中英文。比如说:你定义了nvarchar(20),你可以存入20个英文字母/汉字或中英文组合,这个20定义的是字符数而不是字节数;

3、nvarchar2基本上等同于nvarchar,不同在于nvarchar2中存的英文字母也占两个字节。

大多数情况下,ORACLE中的国家字符集是默认的,以下结论成立:

如果要省存储空间,建表时,字段内容里如果中文占了大多数,就用nvarchar2类型;如果内容是英文和数字为主的字符串,就用varchar2类型。

注意:

  • ORACLE中的国家字符集,只有在数据类型为NCHAR/NVARCHAR/NCLOB时才会被使用,也就是前面带N(national)的字符类型;

  • ORACLE默认国家字符集为AL16UTF16,完全对应UNICODE的UTF-16标准(有别于数据库字符集)

  • ORACLE中的UTF-8,不是标准的UTF-8,而是CESU-8。

参考:Oracle字段类型char、varchar2、nvarchar2的区别与使用_oracle varchar2-CSDN博客

规避方法

varchar最大支持长度为4000字节,通过参数MAX_STRING_SIZE控制 MAX_STRING_SIZE默认为STANDARD,修改成EXTENDED支持32K

修改步骤:

sql> shutdown immediate;
sql> startup upgrade;
sql> alter system set max_string_size=EXTENDED scope=both;
sql> @?/rdbms/admin/utl32k.sql
sql> startup;
sql> @?/rdbms/admin/utlrp.sql
相关文章
|
26天前
|
Oracle 关系型数据库 数据库
【YashanDB知识库】YashanDB与Oracle数据类型对齐
【YashanDB知识库】YashanDB与Oracle数据类型对齐
【YashanDB知识库】YashanDB与Oracle数据类型对齐
|
26天前
|
Linux 数据库
【YashanDB知识库】YashanDB查询时间与实际时间默认少8小时
【YashanDB知识库】YashanDB查询时间与实际时间默认少8小时
|
24天前
|
Oracle 关系型数据库 Java
【YashanDB知识库】Flink CDC实时同步Oracle数据到崖山
本文介绍通过Flink CDC实现Oracle数据实时同步至崖山数据库(YashanDB)的方法,支持全量与增量同步,并涵盖新增、修改和删除的DML操作。内容包括环境准备(如JDK、Flink版本等)、Oracle日志归档启用、用户权限配置、增量日志记录设置、元数据迁移、Flink安装与配置、生成Flink SQL文件、Streampark部署,以及创建和启动实时同步任务的具体步骤。适合需要跨数据库实时同步方案的技术人员参考。
【YashanDB知识库】Flink CDC实时同步Oracle数据到崖山
|
21天前
|
Oracle 关系型数据库 数据库
【赵渝强老师】Oracle的闪回版本查询
本文介绍了Oracle数据库的闪回版本查询(Flashback Version Query)功能,通过示例详细讲解了其使用方法。闪回版本查询可获取指定时间区间内行的不同版本,利用`versions between`子句实现。文中包含视频讲解,并通过创建测试表、插入数据及执行查询等步骤,演示如何获取历史版本信息和伪列详情,帮助用户深入了解该功能的实际应用。
50 13
|
16天前
|
SQL Oracle 关系型数据库
【赵渝强老师】Oracle的闪回事务查询
Oracle数据库的闪回事务查询(Flashback Transaction Query)是闪回版本查询的扩充,可用于审计或撤销已提交的事务。通过`flashback_transaction_query`视图,可生成还原特定事务的SQL语句。本文介绍了其基本概念,并通过实战演示如何使用该功能:从授权、开启UNDO数据增强,到创建测试表和事务,最后利用闪回查询撤销已提交的事务,验证数据恢复效果。附带视频讲解,帮助深入理解。
|
26天前
|
存储 Oracle 关系型数据库
【YashanDB 知识库】YMP 校验从 yashandb 同步到 oracle 的数据时,字段 timestamp(0) 出现不一致
【YashanDB 知识库】YMP 校验从 yashandb 同步到 oracle 的数据时,字段 timestamp(0) 出现不一致
|
22天前
|
SQL Oracle 关系型数据库
【YashanDB知识库】共享利用Python脚本解决Oracle的SQL脚本@@用法
【YashanDB知识库】共享利用Python脚本解决Oracle的SQL脚本@@用法
|
24天前
|
Oracle 关系型数据库
【YashanDB知识库】YMP从oracle到yashan迁移评估报OCI相关错误
本文来自YashanDB官网,主要解决在使用YMP(yashan-migrate-platform)进行迁移评估时出现的OCI版本检查错误问题。错误原因为操作系统重装后,OCI所需依赖缺失(如`libnsl.so.1`、`libnnz19.so`等)。文章提供了排查步骤和解决方法:一是检查并配置环境变量`LD_LIBRARY_PATH`,二是若依赖仍缺失,则需手动下载并安装对应依赖文件。适用于使用YMP迁移平台的用户遇到OCI相关问题时参考。
|
24天前
|
SQL 存储 Oracle
【YashanDB知识库】对比Oracle和YashanDB对象不一致的方法
本文来自YashanDB官网,介绍从Oracle迁移到YashanDB后的数据校验方法。通过YMP工具迁移数据后,可使用其内置校验功能,或采用本文提供的复杂对比思路:将Oracle的DBA_*数据字典视图保存为用户表,借助YMP同步至YashanDB,再利用SQL查询两数据库间的差异。具体包括视图、存储过程、索引和约束等对象的对比方法,提供详细SQL示例以供参考和修改。
|
22天前
|
SQL Oracle 关系型数据库

推荐镜像

更多