建立Materialed View (物化檢視)時候報錯ORA-01723

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>