时间戳

首页 > 解决方案 > ORA-00942: 实体化视图刷新时表或视图不存在

sql - ORA-00942: 实体化视图刷新时表或视图不存在

问题描述

每当我刷新我的物化视图时都会遇到错误，所以我创建了一个 PIPELINE 函数并将其加入到我的主表中，物化视图的创建运行顺利，但是当我尝试刷新时，出现了错误消息。

ORA-00942: 表或视图不存在

请，在这方面需要帮助。

create materialized view mv_test as 
SELECT
    id,
    id1,
    id2,
    id3,
    id4,
    id5,
    lpad(substr(ab.column_value, 3), 4, '0') ab
FROM
    tbl1 t1 left join 
            TABLE ( split_str_by_delim(t1.id1, '|')) ab on substr(ab.column_value,1,2) = 
'AB'
WHERE id2= sysdate;

刷新：

begin
    dbms_mview.refresh('MV_TEST','C');
end;
/

我也尝试了快照刷新，但仍然无法正常工作。

BEGIN 
    DBMS_SNAPSHOT.REFRESH( '"MV_TEST"','C'); 
end;

以下是我遇到的错误。

Materialized view MV_TEST created.


Error starting at line : 22 in command -
begin
    dbms_mview.refresh('MV_TEST','C');
end;
Error report -
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3020
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2432
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 88
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 253
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2413
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2976
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3263
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3295
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 16
ORA-06512: at line 2
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:

编辑：添加了我的函数 PIPELINE 和 TYPE

类型：

create or replace TYPE split_tbl as TABLE OF VARCHAR2(32767);

功能：

create or replace FUNCTION split_str_by_delim (p_list VARCHAR2, p_del 
VARCHAR2)
   RETURN split_tbl
   PIPELINED IS
   l_idx PLS_INTEGER;
   l_list VARCHAR2 (32767) := p_list;
   l_value VARCHAR2 (32767);
BEGIN
   LOOP
      l_idx := INSTR (l_list, p_del);

      IF l_idx > 0 THEN
         PIPE ROW (SUBSTR (l_list, 1, l_idx - 1));
         l_list := SUBSTR (l_list, l_idx + LENGTH (p_del));
      ELSE
         PIPE ROW (l_list);
         EXIT;
      END IF;
   END LOOP;

   RETURN;
END split_str_by_delim;

问候， 内尔兹基

标签： sql

解决方案

所以，它有效！我刚刚添加了这个额外的连接，它就像魔术一样工作。

left join dual on 1=1

物化视图刷新已成功。

完整脚本：

create materialized view mv_test as 
SELECT
    id,
    id1,
    id2,
    id3,
    id4,
    id5,
    lpad(substr(ab.column_value, 3), 4, '0') ab
FROM
    tbl1 t1 left join 
            TABLE ( split_str_by_delim(t1.id1, '|')) ab on substr(ab.column_value,1,2) = 'AB'
    left join dual on 1=1
WHERE id2 = sysdate;

内尔兹基

推荐阅读