oracle 11g dblink 查询的bug --10053用处

最新推荐文章于 2024-08-05 10:56:00 发布

原创最新推荐文章于 2024-08-05 10:56:00 发布 · 2.1k 阅读

0 ·

CC 4.0 BY-SA版权

SQL语句学习专栏收录该内容

48 篇文章

订阅专栏

解决Oracle 11g中外网通过DBLink访问内网数据库时出现的SQL查询问题。原始SQL在外网无法正常获取数据，通过调整SQL结构并使用子查询的方式解决了这一问题。调整后的SQL在外网成功执行。

在oracle11g上，外网建的dblink访问内网，好奇怪的SQL，问题SQL在内网是可以的，在外网查不出数据。做为实验，把内网的数据同步到外网，然后查询，是可以的。最后通过调整SQL写法解决了问题。

问题SQL:

SELECT T.P_TYPE_CODE,

T.TYPE_CODE TYPE_ID,
T.TYPE_NAME,
TYPE_CODE,
0 STANDARD_CENT,
'' REMARK,
'' STD_COUNT,
N.IS_CHECK
FROM gg_SAFE_C_TYPE_NW T, gg_C_STANDARD_NW N
WHERE T.TYPE_CODE = N.CHECK_NUMBER(+)
START WITH T.TYPE_ID = '0'
CONNECT BY PRIOR T.TYPE_CODE = T.P_TYPE_CODE

AND T.PROJECT_TYPE IN (1, 3);

调整后：

select TT.P_TYPE_CODE,
TT.TYPE_CODE TYPE_ID,
TT.TYPE_NAME,
TT.TYPE_CODE TYPE_CODE,
0 STANDARD_CENT,
'' REMARK,
'' STD_COUNT,
N.IS_CHECK
from (SELECT *
FROM gg_SAFE_C_TYPE_NW T
START WITH T.TYPE_ID = '0'
CONNECT BY PRIOR T.TYPE_CODE = T.P_TYPE_CODE
and PROJECT_TYPE IN (1, 3)) TT,
gg_C_STANDARD_NWN

WHERE TT.TYPE_CODE = N.CHECK_NUMBER(+);

在外网的机器上做了10053，发现问题SQL经过查询转换后是错误的。

问题SQL查询转换后的结果，执行报错

SELECT "A1"."P_TYPE_CODE" "P_TYPE_CODE",
       "A1"."TYPE_CODE" "TYPE_ID",
       "A1"."TYPE_NAME" "TYPE_NAME",
       "A1"."TYPE_CODE" "TYPE_CODE",
       0 "STANDARD_CENT",
       '' "REMARK",
       '' "STD_COUNT",
       "A1"."IS_CHECK" "IS_CHECK"
  FROM ((SELECT "A4"."PROJECT_TYPE" "T_$$_PROJECT_TYPE",
                "A4"."P_TYPE_CODE"  "T_$$_P_TYPE_CODE",
                "A4"."TYPE_CODE"    "T_$$_TYPE_CODE",
                "A3"."CHECK_NUMBER" "N_$$_CHECK_NUMBER",
                "A3"."IS_CHECK"     "N_$$_IS_CHECK",
                "A4"."TYPE_CODE"    "TYPE_CODE",
                "A4"."TYPE_NAME"    "T_$$_TYPE_NAME"
           FROM "gg_c_TYPE_NW"@TO_aaa     "A4",
                "gg__STANDARD_NW"@TO_aaa "A3"
          WHERE "A4"."TYPE_CODE" = "A3"."CHECK_NUMBER"(+)
            AND "A4"."TYPE_ID" = '0') UNION ALL
        (SELECT "A6"."PROJECT_TYPE" "T_$$_PROJECT_TYPE",
                "A6"."P_TYPE_CODE"  "T_$$_P_TYPE_CODE",
                "A6"."TYPE_CODE"    "T_$$_TYPE_CODE",
                "A5"."CHECK_NUMBER" "N_$$_CHECK_NUMBER",
                "A5"."IS_CHECK"     "N_$$_IS_CHECK",
                "A6"."TYPE_CODE"    "TYPE_CODE",
                "A6"."TYPE_NAME"    "T_$$_TYPE_NAME"
           FROM "LCAMTEST"."connect$_by$_pump$_003"               "A7",
                "gg_C_TYPE_NW"@TO_aaa     "A6",
                "gg_C_STANDARD_NW"@TO_aaa "A5"
          WHERE "A6"."TYPE_CODE" = "A5"."CHECK_NUMBER"(+)
            AND "A7"."PRIOR T.TYPE_CODE " = "A6"."P_TYPE_CODE"
            AND ("A6"."PROJECT_TYPE" = 1 OR "A6"."PROJECT_TYPE" = 3))) "A1";

调整后的SQL，结果正确

SELECT "A2"."P_TYPE_CODE" "P_TYPE_CODE",
       "A2"."TYPE_CODE" "TYPE_ID",
       "A2"."TYPE_NAME" "TYPE_NAME",
       "A2"."TYPE_CODE" "TYPE_CODE",
       0 "STANDARD_CENT",
       '' "REMARK",
       '' "STD_COUNT",
       "A1"."IS_CHECK" "IS_CHECK"
  FROM (SELECT "A8"."P_TYPE_CODE" "P_TYPE_CODE",
               "A8"."TYPE_CODE"   "TYPE_CODE",
               "A8"."TYPE_NAME"   "TYPE_NAME"
          FROM "PS_CSG_SAFE_CHECK_TYPE_NW"@TO_LCAM_ZCDEVELOP "A8"
         START WITH "A8"."TYPE_ID" = '0'
        CONNECT BY "A8"."P_TYPE_CODE" = PRIOR "A8"."TYPE_CODE"
               AND ("A8"."PROJECT_TYPE" = 1 OR "A8"."PROJECT_TYPE" = 3)) "A2",
       "PS_CSG_SAFE_CHECK_STANDARD_NW"@TO_LCAM_ZCDEVELOP "A1"
 WHERE "A2"."TYPE_CODE" = "A1"."CHECK_NUMBER"(+);