Oracle_Materialized View欄位長度不足

tags: oracle materialized view

說明

最近一個Materialized View(後以mv簡稱)在排程更新的時候發出欄位長度不足的異常訊息，一直到現在我才知道，Oracle mv的欄位也會有長度不足的問題。

主要是，系統會參照你在執行mv的當下的該欄位長度，上stackoverflow找到了相關的訊息。

範例取自上面stackoverflow

--Create simple table and materialized view
create table test1(a varchar2(1 char));
create materialized view mv_test1 as select a from test1;

--Increase column width of column in the table
alter table test1 modify (a varchar2(2 char));

--Insert new value that uses full size
insert into test1 values('12');

--Try to compile and refresh the materialized view
alter materialized view mv_test1 compile;
begin
    dbms_mview.refresh(user||'.MV_TEST1');
end;
/

ORA-12008: error in materialized view refresh path
ORA-12899: value too large for column "JHELLER"."MV_TEST1"."A" (actual: 2, maximum: 1)
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2563
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2776
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2745
ORA-06512: at line 3

--Increase column width of column in the materialized view and refresh
alter materialized view mv_test1 modify (a varchar2(2 char));
begin
    dbms_mview.refresh(user||'.MV_TEST1');
end;
/
select * from mv_test1;
A
--
12

範例中可以看的到：

1. 第2、3行，在建立test1的當下，a欄位的長度是1，而這時候建立了mv所參照的欄位長度也會是1
2. 第6行，變更test1的a欄位的長度為2
3. 第9行，寫入長度是2的資料當然可以成功
4. 第12~15行，refresh mv，這時候會產生異常，因為mv所參照的長度是1，但資料表內的資料長度為2
5. 第26~29行，在refresh之後，重新調整長度即可

後續跟著範例調整欄位長度，也總算是正常了，雖然在執行perl的時候還是有出現異常訊息DBD::Oracle::st execute failed: ORA-00911: invalid character，不過這只要拿掉字串內的結尾符號『;』就正常了。