1

I have to create a job periodically.

But firstly I have create a simply job to learn how to create jobs in oracle, because is the first time I use a job.

It runs at systimestamp, but the job doesn't execute.

create or replace procedure job_test
is
begin
update table_a set value_user = 'JOB_EXECUTED' where id = 1; 
commit;
end;
/

Then the scheduler job

begin 
dbms_scheduler.create_job(
job_name => 'test_job_A',
job_type => 'stored_procedure',
job_action => 'job_test',
start_date = SYSTIMESTAMP,
enabled => true
);
end;
/

Then I consult the column value_user and it hasn't been updated.

 select * from table_A where id = 1;

Can anyone explain me what I am missing.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
proera
  • 123
  • 1
  • 3
  • 14
  • 1
    I have created a test table "table_a" with only "value_user" and "id" columns. I have then inserted one row of values (id = 1, value_user = 'test'). I have then created your procedure and your job. I have done this in SQL developer and all is ok. When I run query "select * from table_A where id = 1;" I get (id = 1, value_user = JOB_EXECUTED). Do you have a row in your table that has id = 1 ? I have to ask :) – VBoka Oct 27 '19 at 19:44

2 Answers2

2
create table user_count (
number_of_users NUMBER(4),
time_of_day   TIMESTAMP
);

CREATE OR REPLACE PROCEDURE insert_user_count AS
 v_user_count NUMBER(4);
BEGIN
 SELECT count(*)
  INTO v_user_count
 FROM v$session
 WHERE username IS NOT NULL;
 INSERT INTO user_count
 VALUES (v_user_count, systimestamp);
 commit;
 END insert_user_count;

BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM (
 program_name      => 'PROG_INSERT_USER_COUNT',
 program_action     => 'INSERT_USER_COUNT',
 program_type      => 'STORED_PROCEDURE');
END; 

BEGIN
 DBMS_SCHEDULER.CREATE_SCHEDULE (
 schedule_name   => 'my_weekend_5min_schedule',
 start_date    => SYSTIMESTAMP,
 repeat_interval  => 'FREQ=MINUTELY; INTERVAL=5; BYDAY=SAT,SUN',
 end_date     => SYSTIMESTAMP + INTERVAL '30' day,
 comments     => 'Every 5 minutes');
END;

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
  job_name     => 'my_user_count_job',
  program_name   => 'prog_insert_user_count',
  schedule_name   => 'my_weekend_5min_schedule');
END;

exec dbms_scheduler.enable('my_user_count_job’)

select * from user_count;

select job_name, status, run_duration, cpu_used
from USER_SCHEDULER_JOB_RUN_DETAILS
where job_name = ‘MY_USER_COUNT_JOB’;
0

Indeed your case should work but once(provided there already exists a row with id value equals to 1). Suppose you have an insert statement rather than an update, but doesn't repeat to populate the table with new incremented ID values.

For this, important point is to add repeat_interval argument such as below :

begin 
  dbms_scheduler.create_job(
                            job_name => 'test_job_A',
                            job_type => 'stored_procedure',
                            job_action => 'job_test',
                            start_date => SYSTIMESTAMP,
                            enabled => true,
                            repeat_interval => 'FREQ=MINUTELY;INTERVAL=5',
                            auto_drop => false,
                            comments => 'Inserts new records' 
                           );
end;
/

which inserts new records for each five minutes.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55