一個ORA-604錯誤的分析

同事碰到一個ORA-604錯誤，分析了一下發覺還比較有趣，簡單記錄一下。

 

 

出錯的SQL大致如下：

SQL> CREATE TABLE T_604 AS
  2  SELECT * FROM
  3  (SELECT OBJECT_TYPE, TO_CHAR(AVG(OBJECT_ID), '999999.999') FROM DBA_OBJECTS
  4  GROUP BY OBJECT_TYPE
  5  ORDER BY 2 DESC)
  6  WHERE ROWNUM < 10;
(SELECT OBJECT_TYPE, TO_CHAR(AVG(OBJECT_ID), '999999.999') FROM DBA_OBJECTS
                                                                *
第 3 行出現錯誤:
ORA-00604: 遞迴 SQL 層 1 出現錯誤
ORA-01401: 插入的值對於列過大

由於同事並不是DBA，因此對這個錯誤比較困惑，他不清楚為什麼SELECT語句執行沒有任何的問題，而根據SELECT的查詢結果去建立表就發生了錯誤，因此同事任何可能是空間分配上出了問題。

SQL> SELECT * FROM
  2  (SELECT OBJECT_TYPE, TO_CHAR(AVG(OBJECT_ID), '999999.999') FROM DBA_OBJECTS
  3  GROUP BY OBJECT_TYPE
  4  ORDER BY 2 DESC)
  5  WHERE ROWNUM < 10;

OBJECT_TYPE        TO_CHAR(AVG
------------------ -----------
DATABASE LINK
MATERIALIZED VIEW    31618.000
RULE SET             31581.400
DIMENSION            31414.000
DIRECTORY            31207.667
EVALUATION CONTEXT   29200.091
XML SCHEMA           28358.700
TRIGGER              27552.375
INDEXTYPE            27381.750

已選擇9行。

一般來說，ORA-604錯誤很少直接出現在使用者呼叫的SQL中，對於這種情況，後面的那個錯誤資訊是真正引發錯誤的原因。

所以這裡引發錯誤的真正原因是後面的那個ORA-1401錯誤。這個錯誤不難理解，插入的值比列的定義要大。

不過CREATE TABLE AS SELECT無法為建立表的列指定資料型別和長度限制，資料型別和長度都由SELECT的查詢結果來確定。按照道理就不應該會出現這種錯誤。

其實問題很簡單，導致錯誤的真正原因是列名長度太長了，只需要將上面的CREATE TABLE語句改變一下寫法，就可以順利執行了：

SQL> CREATE TABLE T_604 (OBJECT_TYPE, AVG_OBJECT_ID) AS
  2  SELECT * FROM
  3  (SELECT OBJECT_TYPE, TO_CHAR(AVG(OBJECT_ID), '999999.999') FROM DBA_OBJECTS
  4  GROUP BY OBJECT_TYPE
  5  ORDER BY 2 DESC)
  6  WHERE ROWNUM < 10;

表已建立。

SQL> DROP TABLE T_604;

表已刪除。

SQL> CREATE TABLE T_604 AS
  2  SELECT * FROM
  3  (SELECT OBJECT_TYPE, TO_CHAR(AVG(OBJECT_ID), '999999.999') AVG_OBJECT_ID
  4  FROM DBA_OBJECTS
  5  GROUP BY OBJECT_TYPE
  6  ORDER BY 2 DESC)
  7  WHERE ROWNUM < 10;

表已建立。

當使用者執行DDL操作時，Oracle透過大量的遞迴呼叫來維護資料字典。比如這個CREATE TABLE語句，Oracle就會更新TAB$、COL$等表。這些操作都是遞迴呼叫操作，而在遞迴呼叫過程中出現的錯誤，就會報錯ORA-604。

由於沒有指定別名，Oracle試圖將TO_CHAR(AVG(OBJECT_ID), '999999.999')作為列名，而這個的長度顯然超過了列長度30的限制，因此Oracle在插入資料字典表的時候報錯ORA-1401錯誤。

這個錯誤的產生還有一定的條件，如果是TO_CHAR(AVG(OBJECT_ID), '999999.999')直接出現在SELECT的外層，在CREATE TABLE的時候，Oracle會明確要求使用者提供別名：

SQL> CREATE TABLE T_604 AS
  2  SELECT OBJECT_TYPE, TO_CHAR(AVG(OBJECT_ID), '999999.999')
  3  FROM DBA_OBJECTS
  4  GROUP BY OBJECT_TYPE
  5  ORDER BY 2 DESC;
SELECT OBJECT_TYPE, TO_CHAR(AVG(OBJECT_ID), '999999.999')
                    *
第 2 行出現錯誤:
ORA-00998: 必須使用列別名命名此表示式