建立Materialed View (物化视图)时候报错ORA-01723

最新推荐文章于 2025-11-04 15:34:51 发布

转载最新推荐文章于 2025-11-04 15:34:51 发布 · 3.2k 阅读

本文介绍了在Oracle数据库中创建视图时遇到不允许长度为0的列的问题及解决方法。通过使用CAST将NULL转换为固定长度的VARCHAR2类型，成功解决了因NULL值引发的错误，并完成了视图及基于视图的新表的创建。

CREATE MATERIALIZED VIEW ach.CARD_INFO
BUILD IMMEDIATE
REFRESH fast
NEXT sysdate + 5/86400
AS
select card_no , card_seq_no, '1111' CARD_PASSWORD, '' USER_NAME from lcas.CARD_INFO@link_lcas

ORA-01723: 不允许长度为 0 的列

查询发现是 '' 长度为0 导致。

修改为 cast ('' as varchar2(100)) USERNAME 即可。

----------------------------------------------------------

再比如：

Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.

O:\>sqlplus "greatfinish/finish"

SQL*Plus: Release 9.2.0.1.0 - Production on 星期二 5月 23 15:46:03 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

连接到:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

greatfinish@ORA920> create table a
  2  (a  int,
  3 b  int)
  4  /

表已创建。

greatfinish@ORA920> CREATE OR REPLACE VIEW VW_A AS
  2  SELECT A.A AS COL1,A.B AS COL2,NULL AS COL3 FROM A
  3  /

视图已建立。

greatfinish@ORA920> CREATE TABLE TMP AS
  2  SELECT * FROM VW_A
  3  /
SELECT * FROM VW_A
   *
ERROR 位于第 2 行:
ORA-01723: 不允许长度为 0 的列

greatfinish@ORA920> create or replace view vw_a as
  2  select a.a as col1,a.b as col2,
  3             cast(null as varchar2(10)) as col3
  4 from a
  5  /

视图已建立。

greatfinish@ORA920> CREATE TABLE TMP AS
  2  SELECT * FROM VW_A
  3  /

表已创建。

greatfinish@ORA920>