Home » SQL & PL/SQL » SQL & PL/SQL » Oracle Job not running properly:
Oracle Job not running properly: [message #297931] Mon, 04 February 2008 05:05 Go to next message
ShridharV
Messages: 29
Registered: January 2008
Junior Member
Oracle Job not running properly:


Hi,
My current job is like the one below:


BEGIN

declare
jobno number;
begin
dbms_job.submit(jobno, 'refresh_data.refresh_Mview_group;', sysdate, 'trunc(add_months(sysdate, 1), ''month'') + 1 + 1/24*2');
-- To ensure no execution while in test.
dbms_job.broken(jobno, true);
end;

/

Within the 'refresh_data.refresh_Mview_group' I am running a
dbms_refresh.refresh('MVIEW_VALUE_GROUP'); command.

The job is supposed to call the procedure on the 2nd of every month. But when I fetch the records from the individual Mviews, they are not matching with the ones present in the database tables. i.e., the refresh is not complete. Only a part of the data is getting reflected in Mview. In the procedure 'refresh_data.refresh_Mview_group' this statement is also written:-

insert into refresh_timings
(refresh_group_name
,refresh_start_date
,refresh_stop_date)
values ('MVIEW_VALUE_GROUP'
,start_dt
,sysdate);
commit;
But I am not seeing any entries in the table.

Please suggest what is going wrong in the refresh part?

The body of the MVIEW_VALUE_GROUP is

DECLARE
RCArray SYS.DBMS_UTILITY.UNCL_ARRAY;
BEGIN
RCArray(1) := 'MVIEW_1';
RCArray(2) := 'MVIEW_2';
RCArray(3) := NULL;
SYS.DBMS_REFRESH.MAKE (
name => 'MVIEW_VALUE_GROUP'
,tab => RCArray
,next_date => TO_DATE('01/01/4000 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
,interval => 'sysdate + 1000'
,implicit_destroy => FALSE
,lax => false
,job => 0
,rollback_seg => NULL
,push_deferred_rpc => FALSE
,refresh_after_errors => TRUE
,purge_option => 0
,parallelism => 0
,heap_size => 0
);
Commit;
END;
/

Regards,
Shridhar
Re: Oracle Job not running properly: [message #297934 is a reply to message #297931] Mon, 04 February 2008 05:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code.
Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

In the end, also post a feedback when you get the solution (see your previous topics).

Regards
Michel
Re: Oracle Job not running properly: [message #297945 is a reply to message #297934] Mon, 04 February 2008 05:27 Go to previous messageGo to next message
ShridharV
Messages: 29
Registered: January 2008
Junior Member
Hi,

Sure. Will follow the format.
Re: Oracle Job not running properly: [message #297954 is a reply to message #297945] Mon, 04 February 2008 05:42 Go to previous message
ShridharV
Messages: 29
Registered: January 2008
Junior Member
Hi,
My current job is like the one below:

BEGIN

declare
  jobno number;
 begin
   dbms_job.submit(jobno, 'refresh_data.refresh_Mview_group;',     sysdate, 'trunc  (add_months(sysdate, 1), ''month'') + 1 + 1/24*2');
-- To ensure no execution while in test.
dbms_job.broken(jobno, true);
end;

/

Within the 'refresh_data.refresh_Mview_group' I am running a
dbms_refresh.refresh('MVIEW_VALUE_GROUP'); command.

The job is supposed to call the procedure on the 2nd of every month. But when I fetch the records from the individual Mviews, they are not matching with the ones present in the database tables. i.e., the refresh is not complete. Only a part of the data is getting reflected in Mview. In the procedure 'refresh_data.refresh_Mview_group' this statement is also written:-

insert into refresh_timings (refresh_group_name,refresh_start_date,refresh_stop_date)
values ('MVIEW_VALUE_GROUP',start_dt,sysdate); 
commit;

But I am not seeing any entries in the refresh_timings.

Please suggest what is going wrong in the refresh part?

[Mod-edit: Frank added code-tags]

[Updated on: Mon, 04 February 2008 09:33] by Moderator

Report message to a moderator

Previous Topic: Exists and In operator
Next Topic: utl_file not as expected
Goto Forum:
  


Current Time: Wed Dec 07 18:27:15 CST 2016

Total time taken to generate the page: 0.15663 seconds