CODEWITHSUNDEEP
oracle-databaseoracle11goracle-sqldeveloper
E Alexis T
E Alexis T

Reputation: 100

My DBMS_SCHEDULER.CREATE_JOB not running?

I am creating a job in oracle 11g express and it is not updating values with the executed job every 5 minutes, and when I check with this to see how many jobs are running, I get 0:

select count(*) from dba_jobs_running;

I tried using a commit but didn't work either. This is my query:

 CREATE TABLE Individuo
(
  id_ind int primary key,
  nom_ind Varchar(255),
  ape_ind Varchar(255),
  sal_ind int
)

DROP table Individuo;

insert into Individuo values(1, 'Carlos', 'Guzman', 100);
insert into Individuo values(2, 'Hidalgo', 'Machado', 200);
insert into Individuo values(3, 'Guillermo', 'Pinto', 500);
insert into Individuo values(4, 'Katia', 'Barba', 300);
insert into Individuo values(5, 'Fernando', 'Parra', 500);
insert into Individuo values(6, 'Celesta', 'Valdes', 100);
insert into Individuo values(7, 'Carola', 'Vallez', 700);
insert into Individuo values(8, 'Catalina', 'Riveron', 800);
insert into Individuo values(9, 'Pepe', 'Najarro', 400);
insert into Individuo values(10, 'Paz', 'Cuenca', 200);

BEGIN
DBMS_SCHEDULER.CREATE_JOB 
(
   job_name             => 'MI_TRABAJO',
   job_type             => 'PLSQL_BLOCK',
   job_action           => 'UPDATE Individuo SET sal_ind = sal_ind+1 where id_ind = "8";',
   start_date           =>  SYSDATE,
   repeat_interval      => 'FREQ=MINUTELY', 
   comments             => 'La insercion en la tabla Individuo tuvo exito'
);
END;

select * from Individuo;

BEGIN
  DBMS_SCHEDULER.ENABLE('MI_TRABAJO');
END;

BEGIN
  DBMS_SCHEDULER.RUN_JOB('MI_TRABAJO', True);
END;

SELECT * FROM dba_scheduler_jobs WHERE job_name = 'MI_TRABAJO';

SELECT * FROM dba_scheduler_jobs WHERE job_name = 'MI_TRABAJO'; 

EXEC DBMS_SCHEDULER.DROP_JOB('MI_TRABAJO'); 

commit;

select count(*) from dba_jobs_running;

Upvotes: 0

Views: 2350

Answers (2)

Md. Shamim Al Mamun
Md. Shamim Al Mamun

Reputation: 386

Your job execution failed with this error:

ORA-06550: line 1, column 807:
PL/SQL: ORA-00904: "8": invalid identifier
ORA-06550: line 1, column 750:
PL/SQL: SQL Statement ignored

You have to modified the update statement of DBMS_SCHEDULER.CREATE_JOB in job action section.

Replace 

job_action           => 'UPDATE Individuo SET sal_ind = sal_ind+1 where id_ind = "8";',

with

job_action           => 'UPDATE Individuo SET sal_ind = sal_ind+1 where id_ind = 8;',

Just remove the double quotation from where clause(where id_ind = "8";',).After execution of the job, you can get the details information from this query.

select * from DBA_SCHEDULER_JOB_RUN_DETAILS where job_name = 'MI_TRABAJO';

Upvotes: 2

stee1rat
stee1rat

Reputation: 730

You are creating a scheduler job, not a regular job. You can find the information about the currently running scheduler jobs in the DBA_SCHEDULER_RUNNING_JOBS view and the history of running for all scheduler jobs in DBA_SCHEDULER_JOB_RUN_DETAILS. So just check the status and error# fields in the output of this query:

select * from DBA_SCHEDULER_JOB_RUN_DETAILS where job_name = 'MI_TRABAJO';

In the DBA_JOBS_RUNNING view you can only see the current running jobs, created with DBMS_JOBS package. However, even for them, it would be better to check DBA_JOBS.LAST_DATE, because jobs can finish their work very fast.

Upvotes: 0

Related Questions