ORACLE通过透明网关建dblink连接Postgresql的几个问题
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/,如需转载,请注明出处,否则将追究法律责任。