ORACLE通過透明閘道器建dblink連線Postgresql的幾個問題
問題 1:where條件查詢失敗 ORA-00997:
select "big","small" from "test_bigint"@dblink_b_bak where "big"='1';
select "big","small" from "test_bigint"@dblink_b_bak where "big"='1' *
ERROR at line 1:
ORA-00997: illegal use of LONG datatype
分析:
以上報出 ORA-00997: illegal use of LONG datatype 錯誤,是因為當在oracle上通過dblink 查詢postgresql時 ,where 條件欄位在oracle中被解析成了LONG型別欄位,而LONG 型別欄位不支援在where中使用。
在 POSTGRESQL資料庫中查詢存在普通定義的schema以及public的schema的表:
ming=# \d
List of relations
Schema | Name | Type | Owner
--------+--------------+-------+----------
hcming | hcming_order | table | hcming
hcming | test007 | table | postgres
hcming | test_m | table | ming
public | test008 | table | ming
public | test_bigint1 | table | ming
發現當在 oracle中通過dblink 查詢 PG庫中public schema下的表,例如 test_bigint1,此時解析轉化後的欄位型別是正確。
postgresql 端: ming=# \d test_bigint1 Table "public.test_bigint1" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- id | integer | | | ß ----int型別 big | bigint | | | ß ----int型別
oracle 端: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> desc "test_bigint1"@dblink_b; Name Null? Type ----------------------------------------- -------- ---------------------------- id NUMBER(10) ß ----正常轉換成number型別 big NUMBER(20) ß ----正常轉換成number型別
|
但是,當在 oracle中通過dblink 查詢 PG庫中普通 schema下的表,例如 hcming.test007,此時解析轉化後的所有欄位型別 都為LONG型別。
ming-# \d test007 Table "hcming.test007" Column | Type | Collation | Nullable | Default --------+----------+-----------+----------+--------- id | integer | | | ß ----int型別 id2 | bigint | | | ß ----int型別 id3 | smallint | | | ß ----int型別
SQL> desc "test007"@dblink_b; Name Null? Type ----------------------------------------- -------- ---------------------------- id LONG ß ----錯誤轉換成LONG型別 id2 LONG ß ----錯誤轉換成LONG型別 id3 LONG ß ----錯誤轉換成LONG型別 |
分析 oracle 透明閘道器的trace日誌:
發現透明閘道器在獲取 public的表時能夠正常得到正確的欄位型別以及長度。而在針對普通的schema的表時得不到正確的欄位型別,統一定義為VARCHAR型別,之後轉化成為LONGVARCHAR 。
Entered hgodtab at 2020/11/03-17:32:28 count:1 table: test_bigint1 Allocate hoada[0] @ 0x154b9f8 Free hoada[0] @ 0x154b9f8 SQL text from hgodtab, id=0, len=28 ... 00: 73656C65 6374202A 2066726F 6D202274 [select * from "t] 10: 6573745F 62696769 6E743122 [est_bigint1"] Entered hgodscr_process_sellist_description at 2020/11/03-17:32:28 Entered hgopcda at 2020/11/03-17:32:28 Column:1(id): dtype:4 (INTEGER ), prc/scl:10/0, nullbl:1, octet:0, sign:1, radix:0 Exiting hgopcda, rc=0 at 2020/11/03-17:32:28 Entered hgopcda at 2020/11/03-17:32:28 Column:2(big): dtype:-5 (BIGINT), prc/scl:19/0, nullbl:1, octet:0, sign:1, radix:0 Exiting hgopcda, rc=0 at 2020/11/03-17:32:28 The hoada for table test_bigint1 follows... hgodtab, line 1073: Printing hoada @ 0x154b9f8 MAX:2, ACTUAL:2, BRC:100, WHT=5 (SELECT_LIST) hoadaMOD bit-values found (0x20:NEGATIVE_HOADADTY) DTY NULL-OK LEN MAXBUFLEN PR/SC CST IND MOD NAME 4 INTEGER Y 4 4 0/ 0 0 0 0 id -5 BIGINT Y 8 8 0/ 0 0 0 20 big Exiting hgodtab, rc=0 at 2020/11/03-17:32:28 hostmstr: 0: HOA After hoadtab hostmstr: 0: HOA Before hoadafr Entered hgodafr, cursor id 0 at 2020/11/03-17:32:28 Free hoada @ 0x154b9f8 Exiting hgodafr, rc=0 at 2020/11/03-17:32:28 hostmstr: 0: HOA After hoadafr hostmstr: 0: RPC After Describe Table hostmstr: 0: RPC Before Describe Table hostmstr: 0: HOA Before hoadtab Entered hgodtab at 2020/11/03-17:41:24 count:1 table: test007 Allocate hoada[0] @ 0x154b9f8 Entered hgopcda at 2020/11/03-17:41:24 Column:1(id): dtype:12 (VARCHAR), prc/scl:255/0, nullbl:1, octet:-1, sign:1, radix:0 Exiting hgopcda, rc=0 at 2020/11/03-17:41:24 Entered hgopcda at 2020/11/03-17:41:24 Column:2(id2): dtype:12 (VARCHAR ), prc/scl:255/0, nullbl:1, octet:-1, sign:1, radix:0 Exiting hgopcda, rc=0 at 2020/11/03-17:41:24 Entered hgopcda at 2020/11/03-17:41:24 Column:3(id3): dtype:12 (VARCHAR), prc/scl:255/0, nullbl:1, octet:-1, sign:1, radix:0 Exiting hgopcda, rc=0 at 2020/11/03-17:41:24 The hoada for table test007 follows... hgodtab, line 1073: Printing hoada @ 0x154b9f8 MAX:3, ACTUAL:3, BRC:1, WHT=6 (TABLE_DESCRIBE) hoadaMOD bit-values found (0x20:NEGATIVE_HOADADTY,0x200:TREAT_AS_CHAR) DTY NULL-OK LEN MAXBUFLEN PR/SC CST IND MOD NAME -1 LONGVARCHAR Y -1 -1 0/ 0 0 0 220 id -1 LONGVARCHAR Y -1 -1 0/ 0 0 0 220 id2 -1 LONGVARCHAR Y -1 -1 0/ 0 0 0 220 id3 |
期間在透明閘道器層嘗試增加 HS_KEEP_REMOTE_COLUMN_SIZE=ALL 引數,發現可以統一將欄位型別轉化為VARCHAR2(255) ,雖然這滿足解決where的方法,當依然是解析轉換有問題。
SQL> desc "test007"@dblink_b; Name Null? Type ----------------------------------------- -------- ---------------------------- id VARCHAR2(255) id2 VARCHAR2(255) id3 VARCHAR2(255)
|
分析 ODBC日誌資訊:
發現查詢 public 下表會比普通schema 多執行一個 select * from "test_bigint1"語句後能獲取更詳細的表的每個欄位的資訊。
[ODBC][11927][1604454923.802967][SQLColumns.c][215] Entry: Statement = 0x19e3000 Catalog Name = [ming][length = 4] Schema Name = [hcming][length = 6] Table Name = [test_bigint1][length = 12] Column Name = [NULL] [ODBC][11927][1604454923.807262][SQLColumns.c][426] Exit:[SQL_SUCCESS] [ODBC][11927][1604454923.807365][SQLFetch.c][162] Entry: Statement = 0x19e3000 [ODBC][11927][1604454923.807418][SQLFetch.c][352] Exit:[SQL_NO_DATA] [ODBC][11927][1604454923.807457][SQLFreeStmt.c][144] Entry: Statement = 0x19e3000 Option = 0 [ODBC][11927][1604454923.807541][SQLFreeStmt.c][266] Exit:[SQL_SUCCESS] [ODBC][11927][1604454923.807572][SQLFreeStmt.c][144] Entry: Statement = 0x19e3000 Option = 2 [ODBC][11927][1604454923.807699][SQLFreeStmt.c][266] Exit:[SQL_SUCCESS] [ODBC][11927][1604454923.807831][SQLFreeHandle.c][387] Entry: Handle Type = 3 Input Handle = 0x19e3000 [ODBC][11927][1604454923.807999][SQLFreeHandle.c][490] Exit:[SQL_SUCCESS] [ODBC][11927][1604454923.808029][SQLAllocHandle.c][540] Entry: Handle Type = 3 Input Handle = 0x19c9460 [ODBC][11927][1604454923.808106][SQLAllocHandle.c][1085] Exit:[SQL_SUCCESS] Output Handle = 0x19e3000 [ODBC][11927][1604454923.808152][SQLPrepare.c][196] Entry: Statement = 0x19e3000 SQL = [select * from "test_bigint1"][length = 28] [ODBC][11927][1604454923.808211][SQLPrepare.c][377] Exit:[SQL_SUCCESS] [ODBC][11927][1604454923.808245][SQLNumResultCols.c][156] Entry: Statement = 0x19e3000 Column Count = 0x19981c0 [ODBC][11927][1604454923.809734][SQLNumResultCols.c][251] Exit:[SQL_SUCCESS] Count = 0x19981c0 -> 2 [ODBC][11927][1604454923.809959][SQLDescribeCol.c][247] Entry: Statement = 0x19e3000 Column Number = 1 Column Name = 0x7ffdae1c0ea0 Buffer Length = 31 Name Length = 0x7ffdae1c0fc8 Data Type = 0x7ffdae1c0fc4 Column Size = 0x7ffdae1c0f78 Decimal Digits = 0x7ffdae1c0fcc Nullable = 0x7ffdae1c0fd0 [ODBC][11927][1604454923.822636][SQLDescribeCol.c][504] Exit:[SQL_SUCCESS] Column Name = [id] Data Type = 0x7ffdae1c0fc4 -> 4 Column Size = 0x7ffdae1c0f78 -> 10 Decimal Digits = 0x7ffdae1c0fcc -> 0 Nullable = 0x7ffdae1c0fd0 -> 1 [ODBC][11927][1604454923.823065][SQLColAttribute.c][294] Entry: Statement = 0x19e3000 Column Number = 1 Field Identifier = SQL_DESC_UNSIGNED Character Attr = (nil) Buffer Length = 0 String Length = (nil) Numeric Attribute = 0x7ffdae1c0f40 [ODBC][11927][1604454923.823139][SQLColAttribute.c][709] Exit:[SQL_SUCCESS] [ODBC][11927][1604454923.823293][SQLDescribeCol.c][247] Entry: Statement = 0x19e3000 Column Number = 2 Column Name = 0x7ffdae1c0ea0 Buffer Length = 31 Name Length = 0x7ffdae1c0fc8 Data Type = 0x7ffdae1c0fc4 Column Size = 0x7ffdae1c0f78 Decimal Digits = 0x7ffdae1c0fcc Nullable = 0x7ffdae1c0fd0 。。。。 |
檢查 postgresql資料庫許可權等配置並無特殊設定。
在其我自身的測試環境中並不存在這個問題,經過對比發現 odbc版本不同,同時根據以上分析可能還是在odbc層行為出現異常,當前Unixodbc版本是2.3.7,psqlodbc版本是psqlodbc-12.02.0000,嘗試將版本降為Unixodbc版本是2.3.1,psqlodbc版本是psqlodbc-9.02.0000後欄位型別轉換正常。
問題 2:函式查詢失敗
SQL> select "increment"@dblink_b_bak(2);
select "increment"@dblink_b_bak(2)
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
目前通過 DG4ODBC是不支援直接用以上方式呼叫pg的函式,可以考慮使用 DBMS_HS_PASSTHROUGH來實現呼叫。
參考: https://docs.oracle.com/cd/E18283_01/appdev.112/e16760/d_hspass.htm#BEIHICII
呼叫需要返回資料的函式:
這種方式經過測試,對於 pg中一些特殊的格式,例如pg_current_wal_lsn不支援,對text格式返回的值不全。
set serveroutput on declare val varchar2(1000); c number; n number; begin c:=dbms_hs_passthrough.open_cursor@postgresql; , 'select * from dbuser.fun_get();'); loop n := ; exit when n = 0; ; ----其中的5是指返回第5列資料,並賦給val變數 dbms_output.put_line(val); end loop; dbms_hs_passthrough.close_cursor@postgresql(c); end; /
|
呼叫無需返回資料的函式:
這種方式可以應用於執行 ddl,dml,執行函式等無需返回資料的操作,其僅會返回操作是否成功的結果,例如insert多少行。
declare num_rows number; begin num_rows := dbms_hs_passthrough.execute_immediate@postgresql ('SELECT dbuser.fun_get_job()'); dbms_output.put_line(num_rows); end; / |
問題 3:b列資料型別是integer,c列資料型別是serial,多了值為0的行。
SQL> select * from "test_num"@dblink_b_bak;
a b c d e f g h i j ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 1 1 1 1 1 1 1 1 1 1 2 0 2 2 2 2 2 2 0 2 3 2 3 3 3 3 3 3 2 3 4 0 4 4 4 4 4 4 0 4
ming=> select * from ming.test_num; a | b | c | d | e | f | g | h | i | j ---+---+---+---+---+---+---+---+---+--- 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 (4 rows)
|
經過測試,該問題與以上問題 1同樣是因為ODBC驅動版本導致。
問題 4:中文字符集亂碼顯示問題
pg端為utf8,oracle 端為gbk
建議逐一修改:
1.終端顯示字符集
2.linux 環境變數 export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
3.透明閘道器 init<sid>.ora: HS_LANGUAGE=AMERICAN_AMERICA.ZHS16GBK
4.odbc.ini :ConnSettings = set client_encoding to gbk
問題 5:關於postgresql 不支援0x00字元問題:
0x00是ascii碼的0值:NUL ,其在oracle中對這種字元進行了相容,oracle可接受中間帶'\0'的字串進行儲存,並在各種介面顯示內容時會自動截斷後面的內容,而postgresql目前並不相容,同時也在網上找到很多案例從其他資料例如mysql遷移到postgresql也存在同樣的問題。
其產生的方式可能有很多,因為這主要受到應用程式在處理資料時的行為,程式如果存在設計不合理情況都有可能導致產生 0x00字元並存入到資料庫中。另外也可能受驅動如jdbc的bug,或者資料庫某些函式的bug等的影響導致存入0x00字元。
以下是模擬插入 0x00字元:
SQL> insert into t_null values(UNISTR('\0000'));<-----用UNISTR函式也可以轉 1 row created. SQL> insert into t_null values(chr(0)); <-----指的是chr(0) 1 row created. SQL> select col1, rawtohex(col1) from t_null; 《-----轉成16進位制都是00,即0x00
COL1 RAWTOHEX(COL1) ---------- -------------------- 00 00 SQL> select col1, ascii(col1) from t_null; <-----對應的ascii編碼就是0 COL1 ASCII(COL1) ---------- ----------- 0 0 |
找到 11g存在這樣的bug,例如對NVARCHAR型別欄位使用 LISTAGG時導致出現0x00字元。
《 Bug 19461687 LISTAGG return value contains ASCII 0x00 bytes for NVARCHAR with no delimiter specified》(該bug已在12c修復)
以下測試示例:
SQL> create table listagg_test (id number, user_id nvarchar2(10)); Table created. SQL> insert into listagg_test values (1,'user1'); insert into listagg_test values (1,'user2'); commit; 1 row created. SQL> 1 row created. SQL> Commit complete.
SQL> col lis_agg for a35 select id, listagg (user_id) within group (order by user_id) lis_agg from listagg_test group by id; SQL> 2 3
ID LIS_AGG ---------- ----------------------------------- 1 u s e r 1 u s e r 2 《《《《《中間存在的就是0x00
如果程式中有去儲存這樣的值也可能導致儲存了 0x00的值。 insert into t_null select listagg (user_id) within group (order by user_id) lis_agg from listagg_test group by id; 1* select col1, rawtohex(col1) from t_null SQL> / COL1 RAWTOHEX(COL1) ------------------------------ -------------------------------------------------- 00 00 u s e r 1 u s e r 2 0075007300650072003100750073006500720032
|
使用 ogg從oracle同步pg時,可以使用 CHARMAP選項來將0x00 to \x20,該功能在低版本ogg可能存在bug,建議使用19.x以上版本的ogg。 參考:《 Charmap not working in Postgresql- OGG-02132 Invalid character mapping definition (Doc ID 2561427.1) 》
OGG同步時轉換示例:
replicat rpost1 targetdb postgres userid postgres, password postgres CHARMAP ./dirsql/char.map gettruncates discardfile ./dirrpt/rpost2.dsc, purge map aditya.sample,target public.sample;
vi ./dirsql/char.map ---新增以下內容 SOURCECHARSET UTF-8 TARGETCHARSET UTF-8 \x00 \x20
|
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29863023/viewspace-2756881/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- oracle通過透明閘道器連線mysql的配置OracleMySql
- ORACLE通過透明閘道器連線DB2OracleDB2
- Oracle 通過透明閘道器訪問mysqlOracleMySql
- Oracle 透明閘道器連線MySQLOracleMySql
- Oracle10g中通過透明閘道器、DBLink訪問MySQL資料OracleMySql
- oracle連線teradata透明閘道器配置Oracle
- Oracle 通過透明閘道器訪問mysql配置步驟OracleMySql
- Oracle 透過透明閘道器 訪問 mysqlOracleMySql
- oracle透過透明閘道器訪問sql serverOracleSQLServer
- 配置Oracle Gateways透明閘道器連線多個MSSQL資料庫OracleGatewaySQL資料庫
- ORACLE 透明閘道器訪問 MYSQL 表OracleMySql
- Oracle透明閘道器訪問Mysql—luckyfriendsOracleMySql
- ORACLE透明閘道器的配置Oracle
- Oracle Gateways透明閘道器訪問SQL ServerOracleGatewaySQLServer
- Oracle 11.2.0.4 透過透明閘道器訪問mysql 8.0.16OracleMySql
- oracle通過透明閘道器(Oracle Transparent Geteways),訪問ms sql server和其他資料庫OracleSQLServer資料庫
- Oracle透明閘道器訪問SQLServer資料庫OracleSQLServer資料庫
- Oracle透明閘道器訪問MySQL資料庫OracleMySql資料庫
- oracle透明閘道器之異構資料庫的訪問Oracle資料庫
- Oracle配置透明閘道器訪問sql2000OracleSQL
- linux 作業系統下ORACLE資料庫使用透明閘道器連線MYSQLLinux作業系統Oracle資料庫MySql
- 10g透明閘道器訪問sqlserverSQLServer
- ORACLE9i 的透明閘道器的配置 (轉)Oracle
- oracle9i透明閘道器到sqlserver的配置OracleSQLServer
- transparent gateway 透明閘道器配置Gateway
- sql server透明閘道器dblink在alert裡報recover登入失敗SQLServer
- [Oracle-> MySQL] Oracle通過dblink連線MySQL--Oracle 19c連線到MySQL 5.7OracleMySql
- 變頻器透過Modbus轉Profinet閘道器連線電機與PLC通訊
- 透明閘道器 oracle 10g for sqlserver 和sybase的配置Oracle 10gSQLServer
- Oracle DBLink連線數過多的問題(Ora-02020)Oracle
- ORACLE 9i 建立透明閘道器訪問SQL Server 2008OracleSQLServer
- Modbus轉Profinet閘道器連線LED大屏與PLC通訊
- Mysql6.0連線中的幾個問題MySql
- IIS連線ORACLE的一個問題Oracle
- 通過幾個問題深入淺出VueVue
- PLC透過Modbus轉Profinet閘道器連線壓力計的配置方法
- SQL Server通過dblink訪問Oracle資料SQLServerOracle
- 透明閘道器一些特殊場景的配置