ora_604错误解决

个人QQ 149196034 欢迎朋友加入

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;

create table t_604 as
select * from
(select object_type,to_char(avg(object_id),'999999.999') from

dba_objects
group by object_type
order by 2 desc)
where rownum<10

ORA-00604: 递归 SQL 层 1 出现错误
ORA-01401: 插入的值对于列过大

分析错误：
oralce 将to_char(avg(object_id),'999999.999') 作为列名，而这个列名的

长度超过了列长度30个字符的限制。

加一个别名avg_object_id

create table t_604 as
select * from
(select object_type,to_char(avg(object_id),'999999.999') avg_object_id

from dba_objects
group by object_type
order by 2 desc)
where rownum<10

SQL> create table t_604 as
2 select * from
3 (select object_type,to_char(avg(object_id),'999999.999')

avg_object_id from dba_objects
4 group by object_type
5 order by 2 desc)
6 where rownum<10
7 ;

Table created

或者指定列名

create table t_604(object_type,avg_object_id) as
select * from
(select object_type,to_char(avg(object_id),'999999.999') avg_object_id

from dba_objects
group by object_type
order by 2 desc)
where rownum<10

SQL> drop table t_604;

Table dropped

SQL>
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')

avg_object_id from dba_objects
4 group by object_type
5 order by 2 desc)
6 where rownum<10
7 ;

Table created

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22934571/viewspace-1040818/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22934571/viewspace-1040818/