1

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?

6

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;
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"',
1
  • You realize that there's an accepted answer to this question that's more than two years old. It's unlikely that the OP is looking for another solution at this point, and thus is unlikely to try this and post back results.
    – RDFozz
    Mar 15 '18 at 19:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.