ITPub博客

首页 > 数据库 > Oracle > ORACLE通过透明网关建dblink连接Postgresql的几个问题

ORACLE通过透明网关建dblink连接Postgresql的几个问题

原创 Oracle 作者:darren__chan 时间:2021-02-09 00:19:48 0 删除 编辑

问题 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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论
一思尚存,此志不懈

注册时间:2014-09-09

  • 博文量
    174
  • 访问量
    1124193