Hi
Oracle version 10.2.0.4
I am unable to refresh a materialized view.
When data is inserted into Parent table A which a trigger will be fired to refresh a materialized view via a procedure.
I have read triggers are bad and so forth...but I am not sure how I can resolve thsi.
I need once a record is inserted/updated in TABLE_A it has to refresh subsequent materialized view.
Thsi is in order for teh application to cache heavy data.
Trigger on table A
<pre>
create or replace TRIGGER A_TRG
AFTER INSERT OR UPDATE OR DELETE ON A
DECLARE
m number;
begin
SYS.DBMS_JOB.SUBMIT
( JOB => m
,what => 'execute CAB_PROC;' ) ;
end;
</pre>
Procedure of refreshing materialized view CAB
<pre>
create or replace
procedure CAB_PROC
is
BEGIN
dbms_mview.REFRESH('CAB','C');
commit;
END;
</pre>
When
<pre>
Insert into A(ID)
values ('X');
Error starting at line 1 in command:
INSERT INTO A(ID)
values ('X')
Error report:
SQL Error: ORA-06550: line 1, column 101:
PLS-00103: Encountered the symbol "CAB_PROC" when expecting one of the following:
:= . ( @ % ; immediate
The symbol ":=" was substituted for "CAB_PROC" to continue.
ORA-06512: at "SYS.DBMS_JOB", line 82
ORA-06512: at "SYS.DBMS_JOB", line 139
ORA-06512: at "OWNER.A_TRG", line 4
ORA-04088: error during execution of trigger 'OWNER.A_TRG
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
</pre>
Edited by: CrackerJack on May 10, 2012 8:51 PM