Basically I have a Materialised View which needs to be refreshed once a month when a particular table, HISTORY_TABLE is updated i.e. HISTORY_TABLE is only inserted into c. once a month. The materialised view holds no data related to HISTORY_TABLE so I cannot refresh on commit (to the extent of my knowledge).
From what I have been reading, one cannot refresh a Mview inside a trigger because of the implicit commit when refreshing. Any ideas on how I can go about this?
DROP TRIGGER SYSADM.COMPLETE_NOTIF_SMS;
CREATE OR REPLACE TRIGGER SYSADM.COMPLETE_NOTIF_SMS
AFTER INSERT
ON SYSADM.HISTORY_TABLE
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
V_STATUS NUMBER;
V_NOTIFICATION_TEXT VARCHAR2(100);
V_CHECK_CATEGORY VARCHAR2(100);
BEGIN
insert into some_table values (v_check_category, v_notification_text,sysdate);
-- I want to refresh the Mview/snapshot here
DBMS_SNAPSHOT.REFRESH('mview_to_refresh');
EXCEPTION
WHEN OTHERS
THEN
-- Some variables are set to send an email.
RAISE;
END NOTIF_SMS;
/
CodePudding user response：
I am not sure if i got it right, but i refresh my view using global parameters like this :
- i created a package like this to set the value i want to use :
create or replace package dwh.DWH_GLOBAL_PARAMS_MANAGER is
-- Author : ALI.FIDANLI
-- Created : 21.03.2019 10:33:25
-- Purpose : kelepelik
PROCEDURE SET_ACC_DATE_CTX(PDATE DATE);
end DWH_GLOBAL_PARAMS_MANAGER;
CREATE OR REPLACE PACKAGE BODY DWH.DWH_GLOBAL_PARAMS_MANAGER IS
-- Author : ALI.FIDANLI
-- Created : 21.03.2019 10:33:25
-- Purpose : kelepelik
PROCEDURE SET_ACC_DATE_CTX(PDATE DATE)
AS
BEGIN
dbms_session.set_context('DWH_PARAMS','REPORT_DATE',TO_CHAR(PDATE,'dd.mm.yyyy'));
dbms_session.set_context('DWH_PARAMS','REPORT_DATE-1',TO_CHAR(PDATE-1,'dd.mm.yyyy'));
END;
END DWH_GLOBAL_PARAMS_MANAGER;
- I used these parameters inside my view with desired parameters :
-- Author : ALI.FIDANLI
-- Created : 21.03.2019 10:33:25
-- Purpose : kelepelik
CREATE OR REPLACE VIEW DWH.V_F_ACCOUNT AS
SELECT TO_DATE (SYS_CONTEXT ('DWH_PARAMS', 'REPORT_DATE-1'),'dd.mm.yyyy') REPORT_DATE,
C.ACC_NO,
C.ACC_CURRENCY,
pkg.func(C.ACC_NO, TO_DATE (SYS_CONTEXT ('DWH_PARAMS', 'REPORT_DATE-1'),'dd.mm.yyyy') ,C.ACC_GL_CODE) ACC_GL_CODE,
FROM xxxx.tablename C) ACC
so i set the date using
begin
-- Call the procedure
dwh.dwh_global_params_manager.set_acc_date_ctx(pdate => :pdate);
end;
and use my view ....
Let me know if it works for you, if not i will delete my reply
CodePudding user response：
Right; you can't refresh the materialized view directly from the trigger because you can't commit within a trigger. Actually, you can, but the trigger then has to be an autonomous transaction but that - unfortunately - won't work because it is then another session so it doesn't see newly added rows into the table on which the trigger fires.
A workaround could be
- a stored procedure (autonomous transaction) as it has to commit
- why? Because it'll schedule a job which refreshes the materialized view, and
dbms_job.submitrequires a commit. As it propagates back to the caller (i.e. the trigger) and - remember - you can't commit within a trigger, then procedure has to be an autonomous transaction. - Job will run a few minutes after the initial insert, presuming that you'll commit the main transaction until then.
Here's an example.
Your some_table, the original table contents and a materialized view:
SQL> create table some_table (cat varchar2(10), text varchar2(10), datum date);
Table created.
SQL> create materialized view mv_dept as select * from dept;
Materialized view created.
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select * from mv_dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL>
Procedure (autonomous transaction, schedules a job which refreshes the materialized view):
SQL> create or replace procedure p_submit as
2 pragma autonomous_transaction;
3 i number;
4 begin
5 dbms_job.submit(i,
6 'begin DBMS_SNAPSHOT.REFRESH(''mv_dept''); end;',
7 sysdate 1/(24*60),
8 'null'
9 );
10 commit;
11 end;
12 /
Procedure created.
Trigger (which calls the procedure):
SQL> create or replace trigger complete_notif_sms
2 after insert on dept
3 for each row
4 declare
5 v_status number;
6 v_notification_text varchar2(100);
7 v_check_category varchar2(100);
8 begin
9 insert into some_table values (v_check_category, v_notification_text,sysdate);
10
11 -- I want to refresh the Mview/snapshot here
12 p_submit;
13 end notif_sms;
14 /
Trigger created.
SQL>
Just setting date format; you don't have to do that:
SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';
Session altered.
Let's try it: insert a row into a table which is source for the materialized view:
SQL> insert into dept (deptno, dname, loc) values (1, 'a', 'b');
1 row created.
Newly added row is in the table ...
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
1 a b
... but not in the materialized view because job didn't start yet:
SQL> select * from mv_dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Job info:
SQL> select job, last_date, next_date from user_jobs;
JOB LAST_DATE NEXT_DATE
---------- ------------------- -------------------
8 12.10.2021 20:34:11
SQL> commit;
Commit complete.
SQL>
A minute later:
SQL> select sysdate from dual;
SYSDATE
-------------------
12.10.2021 20:36:08
Job has finished; as it was one-time-only job, it's gone from user_jobs:
SQL> select job, last_date, next_date from user_jobs;
no rows selected
We'd expect a new row in the materialized view as well:
SQL> select * from mv_dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
1 a b
SQL>
Right, it's here.