oracle很奇怪的報ora-00904錯誤的討論

尛樣兒發表於2010-01-20

今天同事提了一個問題,同樣的SQL語句在有些地方執行是成功的,有些地方是失敗的。
SQL如下:
SELECT (SELECT MIN(POST.post_name)
          FROM org_post POST,
               emp_station_change schange,
               (SELECT basicinfo.organ_id, basicinfo.ppid
                  FROM ls_basicinfo basicinfo
                 WHERE basicinfo.emp_id = ls_employee.emp_id) emp
         WHERE emp.organ_id = POST.dept_ppid
           AND schange.emp_id = emp.ppid
           AND schange.station_id = POST.post_id)
  FROM ls_employee ls_employee
 WHERE b0123 = '1'
   AND b0126 = '1'
   AND a0118 IN ('1', '2')
   AND b5004 = '11'
   AND ABS(MONTHS_BETWEEN(b5007, TO_DATE('2009-12-31', 'yyyy-mm-dd'))) < 12
   AND (TO_DATE('2009-12-31', 'yyyy-mm-dd') - b5007) >= 0
   AND b0126 = '1'
   AND report_date = '2009-12'

黑體部分就是報ora-00904錯的地方。

我們討論了一下:
首先資料庫版本都是10.2.0.1.0,平臺有windows和aix,資料庫有32位的和64位的。
最後經過驗證找到了規律(不一定準確):
安裝的是32位database的資料庫是執行正常的,凡是64位的database都有報這個錯。AIX平臺都是64位的database,所以AIX都有這個錯誤。這個錯誤某些版本上是個bug需要對資料庫進行升級。
there are some versions of Oracle 10.2.0.1 in which the parser does allow correlated
inline views to be parsed. However, this can lead to other problems further down the
line and should therefore not be used. In all patchsets 10.2.0.2 and higher this should
have been resolved and correlated inline views should once again return a ORA-904 error.

//搜尋關鍵字:ora-00904 bug

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/23135684/viewspace-625635/,如需轉載,請註明出處,否則將追究法律責任。

相關文章