ORA-01730: 指定的列名数无效

1、发现视图V_XG_JXJXM错误

SQL> select * from v_xg_jxjxm;
select * from v_xg_jxjxm
ORA-04063: view "SZHXY.V_XG_JXJXM" 有错误

2、重新编译视图

SQL> alter view v_xg_jxjxm compile;
Warning: View altered with compilation errors

3、单独执行视图中的内容

select c.*,v.nr from xg_lc_xm c
left join v_code_item  v on v.lbh = 'XGXTZJ' and v.dm = c.xmtype;

可以正常执行

4、查询视图ddl语句（记录用于后续分析原因）

select dbms_metadata.get_ddl('VIEW', 'V_XG_JXJXM', 'SZHXY') from dual;

CREATE OR REPLACE FORCE VIEW "SZHXY"."V_XG_JXJXM" ("XMBH", "XMMC", "XMTYPE", "FLOWID", "MS", "FFTYPE", "FFFW", "SLDW", "ZJLY", "NR")

5、重建视图

create or replace view v_xg_jxjxm as
select c.*,v.nr from xg_lc_xm c
left join v_code_item  v on v.lbh = 'XGXTZJ' and v.dm = c.xmtype;

6、查询新视图ddl语句

CREATE OR REPLACE FORCE VIEW "SZHXY"."V_XG_JXJXM" ("XMBH", "XMMC", "XMTYPE", "FLOWID", "MS", "FFTYPE", "FFFW", "SLDW", "ZJLY", "XMJB", "NR")

7、对比4和6

发现新视图比原先视图多一个XMJB字段

总结：由于视图中使用了*且基础表增加字段引起，在开发中应禁用使用*

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

转载于:http://blog.itpub.net/21799400/viewspace-2124401/