I have created a job using dbms_scheduler. Following is my code to create the schedule, program & job :
begin
dbms_scheduler.create_schedule (
Schedule_Name => 'test_schedule1',
Repeat_interval => 'FREQ=MINUTELY; INTERVAL=2',
Comments => 'This is test schedule which will run every 2 minutes'
);
end;
begin
dbms_scheduler.create_program
(program_name => 'test_program1',
Program_type => 'STORED_PROCEDURE',
Program_action => 'update_table',
Enabled => TRUE,
Comments => 'This creates a program.');
end;
begin
dbms_scheduler.create_job
(Job_name => 'test_job1',
Program_name => 'test_program1',
Schedule_name => 'test_schedule1',
Enabled => TRUE,
Comments => 'This test job');
end;
Below is my 'update_table' procedure :
create or replace procedure update_table
is
begin
insert into psft_gal_data(emplid, first_name, last_name, created_dt, flag, updated_dt, update_flag)
SELECT emplid, first_name, last_name, created_dt, flag, updated_dt, update_flag FROM psft_gal_data@mssql_dblink
WHERE updated_dt NOT IN
(SELECT updated_dt
FROM psft_gal_data);
end;
In this procedure, I am inserting a new record in my table 'psft_gal_data' from another table in the remote database 'psft_gal_data@mssql_dblink' where mssql_dblink is a heterogeneous database link which we have set up. When the remote database gets updated with new records, the additional records are being copied in my table using this procedure.
If I run this procedure, it does its desired task of inserting new records in my table.
When I run this job, the job gets updated & the new records are added into our table only for the first time.
After 2 minutes as per schedule, the job is run Successfully but new records are not added in my table. Checked the remote database & observed that new records are present in it.
The job runs every 2 minutes but the new records are not added.
Verified whether the job is running successfully or not :
select * from ALL_SCHEDULER_JOB_RUN_DETAILS where job_name='TEST_JOB1';
LOG_ID LOG_DATE OWNER JOB_NAME JOB_SUBNAME STATUS ERROR# REQ_START_DATE ACTUAL_START_DATE RUN_DURATION INSTANCE_ID SESSION_ID SLAVE_PID CPU_USED CREDENTIAL_OWNER CREDENTIAL_NAME DESTINATION_OWNER DESTINATION ADDITIONAL_INFO ERRORS OUTPUT BINARY_ERRORS BINARY_OUTPUT
21550 01-MAR-17 11.29.30.497000000 AM +05:30 WANOC1 TEST_JOB1 SUCCEEDED 0 01-MAR-17 11.29.30.103000000 AM ASIA/CALCUTTA 01-MAR-17 11.29.30.180000000 AM ASIA/CALCUTTA 0 0:0:0.0 1 138,38398 14360 0 0:0:0.0
21554 01-MAR-17 11.31.34.283000000 AM +05:30 WANOC1 TEST_JOB1 SUCCEEDED 0 01-MAR-17 11.31.30.103000000 AM ASIA/CALCUTTA 01-MAR-17 11.31.34.087000000 AM ASIA/CALCUTTA 0 0:0:0.0 1 138,36115 18952 0 0:0:0.02
21556 01-MAR-17 11.33.30.308000000 AM +05:30 WANOC1 TEST_JOB1 SUCCEEDED 0 01-MAR-17 11.33.30.000000000 AM ASIA/CALCUTTA 01-MAR-17 11.33.30.082000000 AM ASIA/CALCUTTA 0 0:0:0.0 1 138,41199 15692 0 0:0:0.02
21560 01-MAR-17 11.35.30.420000000 AM +05:30 WANOC1 TEST_JOB1 SUCCEEDED 0 01-MAR-17 11.35.30.000000000 AM ASIA/CALCUTTA 01-MAR-17 11.35.30.081000000 AM ASIA/CALCUTTA 0 0:0:0.0 1 138,40382 28076 0 0:0:0.02
Kindly check & suggest why the table is not getting updated with new records even though the job gets executed successfully.
Thanks You.
Your query has a NOT IN, which opens up a couple of possibilites:
SELECT emplid, first_name, last_name, created_dt, flag, updated_dt, update_flag FROM psft_gal_data@mssql_dblink
WHERE updated_dt NOT IN
(SELECT updated_dt
FROM psft_gal_data)
- Is update_dt NULL for any of the rows?
If so, you'll get no results:
https://asktom.oracle.com/pls/apex/f?p=100:11:0::NO::P11_QUESTION_ID:442029737684 - If no, are you sure that you haven't already got values for updated_dt in the table? i.e. you're storing datetimes including hours, minutes and seconds. Not midnight (00:00:00)?