【YashanDB知識庫】oracle dblink varchar型別查詢報錯記錄

YashanDB發表於2024-07-17
Oracle型別

問題單：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

相關文章