Skip to Main Content
  • Questions
  • DBMS_SCHEDULER Job only inserting rows once

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Siddhesh.

Asked: March 01, 2017 - 7:58 am UTC

Last updated: March 03, 2017 - 3:14 pm UTC

Version: 12.1.0.2.0

Viewed 1000+ times

You Asked

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.

and Chris said...

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)?

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Issue Resolved

Siddhesh Bakkar, March 03, 2017 - 2:19 pm UTC

Hi Chris,

Thanks for the quick help.

The issue has been resolved.

We were testing the queries in an incorrect way.

First, we updated our procedure as follows :

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;
commit;
end;


Ran our scheduled job using this procedure.
It inserted all the records in the mentioned columns from the remote database into our database without checking whether it is already present or not.
So no issue in scheduler observed with this procedure.

Next modified the procedure to this :

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 emplid NOT IN
    (SELECT emplid 
     FROM psft_gal_data);
end;


Ran this procedure in our job. It updated the values as per our requirement for the first time.

Then, we did :
delete from psft_gal_data where emplid=12345;


And then waited for the job to execute for the next time.

Observed our table did not get updated.

Later on, we tried this :
delete from psft_gal_data where emplid=12345
commit;


Observed that the required values are updated in our table.

So to conclude, we did not execute "commit" after deleting a record which caused this issue.

Thanks again for the help Chris because if you wouldn't have pointed out the issue with the "NOT IN", we wouldn't have been able to figure out the solution so early.
Chris Saxon
March 03, 2017 - 3:14 pm UTC

Thanks for getting back to us! Glad we could help.

More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.