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

分手后的思念是犯贱 2022-08-20 15:20 141阅读 0赞

在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"(+);