Hello my ODC friends ,
In the process of simulating a fast refresh MView on schema TEST on Linux of Oracle 11.2.0.4.0 I encounter this weird error PLS-00103.
The following is my entire code,
CONN / as sysdba;
GRANT create job TO test;
GRANT create materialized view TO test;
GRANT drop any materialized view TO test;
GRANT on commit refresh TO test;
CONN test/test;
SET linesize 200
SET pagesize 50
create sequence t_id
start with 1
increment by 1
nocache
nocycle
;
create table t (id number primary key, name varchar2(30));
insert into t values (t_id.nextval, 'Quanwen Zhao');
insert into t values (t_id.nextval, 'Zlatko Sirotic');
insert into t values (t_id.nextval, 'Sven Weller');
insert into t values (t_id.nextval, 'L Fernigrini');
insert into t values (t_id.nextval, 'Cookie Monster');
insert into t values (t_id.nextval, 'Jara Mill');
insert into t values (t_id.nextval, 'Paul Zip');
commit;
exec dbms_stats.gather_table_stats(ownname=>'TEST', tabname=>'T');
CREATE MATERIALIZED VIEW LOG ON t
WITH PRIMARY KEY
INCLUDING NEW VALUES
;
CREATE MATERIALIZED VIEW mv_t
REFRESH FAST ON COMMIT
AS
SELECT id
, name
FROM t
ORDER BY id
;
set linesize 200
set pagesize 50
col log_owner for a8
col master for a8
col log_table for a8
col primary_key for a11
col include_new_values for a18
select log_owner, master, log_table, primary_key, include_new_values from user_mview_logs;
LOG_OWNE MASTER LOG_TABL PRIMARY_KEY INCLUDE_NEW_VALUES
-------- -------- -------- ----------- ------------------
TEST T MLOG$_T YES YES
TEST@xxxx> CREATE OR REPLACE PROCEDURE refresh_t_scheduler
2 IS
3 BEGIN
4 DBMS_SCHEDULER.create_job (
5 job_name => 'RTS_JOB', -- rts is the first letter abbreviation of procedure name "refresh_t_scheduler".
6 job_type => 'PLSQL_BLOCK',
7 job_action => 'begin dbms_mview.refresh('mv_t','f'); end;',
8 start_date => SYSDATE,
9 repeat_interval => 'FREQ=MINUTELY; INTERVAL=5;',
10 end_date => SYSDATE+25/24/60,
11 auto_drop => false,
12 enabled => true,
13 job_class => 'DEFAULT_JOB_CLASS',
14 comments => 'Regularly refreshing MView mv_t');
15 END;
16 /
Warning: Procedure created with compilation errors.
TEST@xxxx> show errors
Errors for PROCEDURE REFRESH_T_SCHEDULER:
LINE/COL ERROR
-------- -----------------------------------------------------------------
7/54 PLS-00103: Encountered the symbol "MV_T" when expecting one of
the following:
) , * & = - + < / > at in is mod remainder not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like like2
like4 likec between || multiset member submultiset
Could you help me find out where is this error? Thanks in advance.
Best Regards
Quanwen Zhao