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

SQL> select col1, ascii(col1) from t_null; <-----對應的ascii編碼就是0

COL1 ASCII(COL1)

---------- -----------

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

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

ORACLE通過透明閘道器建dblink連線Postgresql的幾個問題

相關文章