Call stored procedure from PL/SQL Job

Question

In sqlplus I created the procedure, whitch fill my table GeneratedData with int values...

create procedure fillGeneratedData (x in int) as
begin
for i in 1..x loop
insert into GeneratedData values (i);
end loop;
end;
/

I want to create job, whitch call this procedure, but it throws errors and dont call the procedure...

BEGIN
sys.dbms_scheduler.create_job( 
job_name => 'job1',
job_type => 'PLSQL_BLOCK',
job_action => 'begin exec fillGeneratedData(50000); end;',
repeat_interval => 'FREQ=MINUTELY;INTERVAL=2',
start_date => systimestamp at time zone 'Europe/Belgrade',
auto_drop => FALSE,
enabled => TRUE);
END;

sqlplus says PL/SQL procedure successfully completed, but when i look to alert log, it throw error:

Tue Apr 01 00:50:45 2014 Errors in file c:\app\adbsuser\diag\rdbms\orcl\orcl\trace\orcl_j000_7516.trc: ORA-12012: error on auto execute of job 74677 ORA-06550: line 1, column 734: PLS-00103: Encountered the symbol "" when expecting one of the following:

:= . ( @ % ; The symbol ";" was substituted for "" to continue. Errors in file c:\app\adbsuser\diag\rdbms\orcl\orcl\trace\orcl_j000_7516.trc: ORA-12012: error on auto execute of job 74679 ORA-06550: line 1, column 734: PLS-00103: Encountered the symbol "FILLGENERATEDDATA" when expecting one of the following:

:= . ( @ % ; The symbol ":=" was substituted for "FILLGENERATEDDATA" to continue.

Can somebody help me?

Thanks a lot.

Justin Cave Justin Cave · Accepted Answer · 2014-03-31T22:56:21

To start with, you PL/SQL block is not valid. If you tried to run just this

begin 
  exec fillGeneratedData(50000); 
end;

you'd get an error. You don't use exec in a PL/SQL block-- that's a SQL*Plus command. Your PL/SQL block would just be

begin 
  fillGeneratedData(50000); 
end;

Call stored procedure from PL/SQL Job

1 Answers