同事碰到一个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: 必须使用列别名命名此表达式

看来对经过函数处理的列加上一个别名是一个很好的习惯！
否则在程序中也只能通过 index 来访问，而不是 name 了。

加别名是好习惯，不过通过NAME是可以访问的，加上引号就可以。