How to call a stored procedure in an Oracle Scheduler job

Question

I want to call a stored procedure in an Oracle Scheduler job. My procedure and job are shown below:

Stored procedure

create or replace PROCEDURE emp_test                                              
IS
begin

delete from  emp;
insert into emp(empid,empname)
select empid1,empname1 from emp_temp;
end;

Job

BEGIN
dbms_scheduler.create_job (
job_name => 'emp_test_JOB',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN
CALL emp_test();
END;',
start_date => SYSTIMESTAMP,
enabled => true,
repeat_interval => 'FREQ=DAILY');
END;

I am able to call procedure like CALL emp_test() with out using a job.

When I am using same thing in a job like above, the job was created successfully but the procedure is not running, rows are not inserted. I need run this job and rows should be inserted; can any one help me please?

Answer 1

You don't need CALL when creating a job (and parenthesis for a procedure that takes no arguments ). I'd also specify INTERVAL=1 explicitly.

BEGIN 
   dbms_scheduler.create_job ( 
    job_name => 'emp_test_JOB', 
    job_type => 'PLSQL_BLOCK', 
    job_action => 'emp_test;', 
    start_date => SYSTIMESTAMP, 
    enabled => true, 
    repeat_interval => 'FREQ=DAILY;INTERVAL=1'
   ); 
END;

Answer 2

Easiest way to execute a stored procedure from scheduler job is by changing the job_type.

Rest you can use your own values. Try this and post back results.

Example:

job_type             => 'STORED_PROCEDURE',
job_action           => '"OWNER"."PROCEDURE_NAME"',