對遠端表使用NVL2的bug

今天發現一個奇怪的現在，在10g中對於遠端表使用NVL2函式會報錯。

錯誤現象如下：

SQL> SELECT NVL2(TNAME, 2, 3) FROM TAB@TEST2;
SELECT NVL2(TNAME, 2, 3) FROM TAB@TEST2
*第 1 行出現錯誤:
ORA-22992: 無法使用從遠端表選擇的 LOB 定位器

資料庫的版本為：

SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

同樣的問題在9204上沒有出現：

SQL> select nvl2(tname, 1, 2) from tab@testdata;

NVL2(TNAME,1,2)
---------------
1
1
.
.
.
1
1

27 rows selected.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

再次觀察錯誤，會發現很多奇怪的問題。

首先，這個SQL跟LOB型別應該一點都不沾邊，奇怪的是居然報了一個和LOB相關的錯誤。

第二，在METALINK上居然找不到任何相關的資訊。

第三，Oracle似乎沒有執行就直接報錯。

SQL> SELECT NVL2(TNAME, 2, 3) FROM TAB@TEST2;
SELECT NVL2(TNAME, 2, 3) FROM TAB@TEST2
*第 1 行出現錯誤:
ORA-22992: 無法使用從遠端表選擇的 LOB 定位器

SQL> SELECT NVL(TNAME, 2) FROM TAB@TEST2;
SELECT NVL(TNAME, 2) FROM TAB@TEST2
*第 1 行出現錯誤:
ORA-02019: 未找到遠端資料庫的連線說明

從上面就可以看到，Oracle還沒有驗證TEST2資料庫鏈是否存在就已經報錯了。透過SQL_TRACE也可以清晰的看到這一點：

SQL> ALTER SESSION SET SQL_TRACE = TRUE;

會話已更改。

SQL> SELECT NVL2(TNAME, 2, 3) FROM TAB@TEST;
SELECT NVL2(TNAME, 2, 3) FROM TAB@TEST
*第 1 行出現錯誤:
ORA-22992: 無法使用從遠端表選擇的 LOB 定位器

SQL> ALTER SESSION SET SQL_TRACE = FALSE;

會話已更改。

檢查TRACE檔案，發現錯誤發生在PARSE階段：

PARSING IN CURSOR #7 len=34 dep=0 uid=56 oct=42 lid=56 tim=3415881096 hv=3913151867 ad='34632404'
ALTER SESSION SET SQL_TRACE = TRUE
END OF STMT
EXEC #7:c=0,e=533,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=3415881091
=====================
PARSE ERROR #8:len=38 dep=0 uid=56 oct=3 lid=56 tim=3423307316 err=22992
SELECT NVL2(TNAME, 2, 3) FROM TAB@TES
*** 2007-08-24 16:41:50.765
=====================
PARSING IN CURSOR #2 len=35 dep=0 uid=56 oct=42 lid=56 tim=3427128854 hv=4067503723 ad='30f9484c'
ALTER SESSION SET SQL_TRACE = FALSE
END OF STMT
PARSE #2:c=0,e=544,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=3427128849
EXEC #2:c=0,e=571,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=3427130535

問題雖然奇怪，變通的解決方法到是很簡單，使用DECODE來代替NVL2就可以了：

SQL> SELECT NVL(TNAME, 2) FROM TAB@TESTRAC.US.ORACLE.COM;

NVL(TNAME,2)
------------------------------
T
BIN$MXYcjz+AZwTgRAADujhFZw==$0

SQL> SELECT NVL2(TNAME, 2, 3) FROM TAB@TESTRAC.US.ORACLE.COM;
SELECT NVL2(TNAME, 2, 3) FROM TAB@TESTRAC.US.ORACLE.COM
*第 1 行出現錯誤:
ORA-22992: 無法使用從遠端表選擇的 LOB 定位器

SQL> SELECT DECODE(TNAME, NULL, 3, 2) FROM TAB@TESTRAC.US.ORACLE.COM;

DECODE(TNAME,NULL,3,2)
----------------------
2
2

最後，這個錯誤在10.2.0.3和11.1.0.6中仍然存在。

SQL> conn yangtk/yangtk@ora11g已連線。
SQL> SELECT NVL2(TNAME, 2, 3) FROM TAB@TEST;
SELECT NVL2(TNAME, 2, 3) FROM TAB@TEST
*第 1 行出現錯誤:
ORA-22992: 無法使用從遠端表選擇的 LOB 定位器

SQL> SELECT * FROM V$VERSION;

BANNER
-----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for Linux: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

感覺Oracle還沒有發現這個bug。