这篇碰到的问题并不是一个bug，但是却有可能成为一个隐藏很深，很难检查到的问题。

在第三期修改SQL的过程中，有一次碰到了这样一个错误：

SQL> WITH ROUTE_D AS /* get full routes */
  2  (
  3     SELECT CITY1 R, CITY2 T, DISTANCE DIS
  4     FROM ROUTES
  5     UNION ALL
  6     SELECT CITY2, CITY1, DISTANCE
  7     FROM ROUTES
  8  ),
  9  ROUTE_ALL_D (C1, C2, LINES, DISTANCE) AS /* get every route of any two cities */
 10  (
 11     SELECT R, T, CAST('"' || R || '"' || T || '"' AS VARCHAR2(4000)), DIS /* avoid ora-1489 error */
 12     FROM ROUTE_D
 13     UNION ALL
 14     SELECT A.C1, T, LINES || T || '"', DIS + DISTANCE /* '"' for avoid city name contains other city name */
 15     FROM ROUTE_D R, ROUTE_ALL_D A
 16     WHERE A.C2 = R.R
 17     AND INSTR(LINES, '"' || T || '"', 1, 1) = 0 /* avoit duplicate city */
 18     AND DISTANCE + DIS <= /* filter the distance longer than routes */
 19             NVL
 20             (
 21                     (
 22                             SELECT DISTANCE
 23                             FROM ROUTES RS
 24                             WHERE (A.C1 = RS.CITY1
 25                                     AND R.T = RS.CITY2)
 26                             OR (A.C1 = RS.CITY2
 27                                     AND R.T = RS.CITY1)
 28                     ),
 29                     9.9E38
 30             )
 31  ),
 32  RESULT_HALF AS
 33  (
 34     SELECT C1 R, C2 T, MIN(DISTANCE) DIS
 35     FROM ROUTE_ALL_D
 36     WHERE C1 < C2
 37     GROUP BY C1, C2
 38  ),
 39  RESULT_ALL AS
 40  (
 41     SELECT R, T, DIS
 42     FROM RESULT_HALF
 43     UNION ALL
 44     SELECT T, R, DIS
 45     FROM RESULT_HALF
 46  ),
 47  RESULT AS
 48  (
 49     SELECT R, T,
 50             DISTANCE * 2 * C.MEMBERS COST,
 51             SUM(DISTANCE * 2 * C.MEMBERS) OVER(PARTITION BY R) COST_CITY
 52     FROM RESULT_ALL R, CITIES C
 53     WHERE R.T = C.CITY_NAME(+) /* any city in route can win even the city have no member */
 54  )
 55  SELECT R, NVL(T, 'TOTAL') T, NVL(SUM(COST), 0) COST
 56  FROM
 57  (
 58     SELECT R, T, COST, RANK() OVER(ORDER BY COST_CITY) RN
 59     FROM RESULT
 60  )
 61  WHERE RN = 1
 62  GROUP BY GROUPING SETS ((R, T), R)
 63  ORDER BY R, DECODE(T, 'TOTAL', CHR(0), T);
                SUM(DISTANCE * 2 * C.MEMBERS) OVER(PARTITION BY R) COST_CITY
                    *
第 51 行出现错误:
ORA-06553: PLS-306: 调用 'OGC_DISTANCE' 时参数个数或类型错误

这个错误很有意思，只是在优化调整SQL，怎么会突然出现一个PLS-306的错误，而且我也没有调用任何什么特别的函数。

仔细检查后发现，由于调整了WITH子句中列的别名，DISTANCE被改成了DIS，而查询这个WITH子句的SQL没有进行对应的修改，导致了这个错误。

从这一点上看，这个DISTANCE还应该是一个PUBLIC的同义词才对，检查数据字典：

SQL> SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
  2  FROM DBA_OBJECTS
  3  WHERE OBJECT_NAME = 'DISTANCE';

OWNER      OBJECT_NAME                    OBJECT_TYPE
---------- ------------------------------ -------------------
PUBLIC     DISTANCE                       SYNONYM

SQL> SELECT OWNER, SYNONYM_NAME, TABLE_OWNER, TABLE_NAME
  2  FROM DBA_SYNONYMS
  3  WHERE SYNONYM_NAME = 'DISTANCE';

OWNER      SYNONYM_NAME    TABLE_OWNER     TABLE_NAME
---------- --------------- --------------- ------------------------------
PUBLIC     DISTANCE        MDSYS           OGC_DISTANCE

SQL> SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
  2  FROM DBA_OBJECTS
  3  WHERE OBJECT_NAME = 'OGC_DISTANCE'
  4  AND WNER = 'MDSYS';

OWNER      OBJECT_NAME                    OBJECT_TYPE
---------- ------------------------------ -------------------
MDSYS      OGC_DISTANCE                   FUNCTION

到这里已经和错误信息对上了，由于指定的DISTANCE在当前用户的表中找不到，因此Oracle从PUBLIC对象中进行解析，找到一个同义词后，发现是一个函数，而由于这个函数需要参数才能返回结果，因此SQL运行报错。

分析到这里被吓了一跳，如果这个OGC_DISTANCE函数在没有参数的情况下也可以返回NUMBER类型的数值，那么就彻底杯具了。那么这个bug隐藏的极为隐蔽，很难被检测到。

看来通过表名的列名的组合来明确的指示一个列是有必要的，至少可以大大减少这种隐蔽错误产生的几率。