CODEWITHSUNDEEP
sqloracle-databaseoracle12cjob-schedulingdbms-scheduler
Nuñito Calzada
Nuñito Calzada

Reputation: 1950

Oracle: execute a Job dbms_scheduler

I Want to execute a Scheduler in Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production I have this package:

create or replace PACKAGE  "S_IN_TDK" is


procedure parseMsg;

end;

and this job

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
   job_name         =>  'parseMsg',
   program_name     =>  'S_IN_TDK.parseMsg',
   repeat_interval  =>  'FREQ=SECONDLY;INTERVAL=10',
   --job_style        => 'LIGHTWEIGHT',
   comments         => 'Job that polls device n2 every 10 seconds');
END;

but when I run the job I got this error:

Fallo al procesar el comando SQL
- ORA-27476: "S_IN_TDK.PARSEMSG" does not exist
ORA-06512: at "SYS.DBMS_ISCHED", line 185
ORA-06512: at "SYS.DBMS_SCHEDULER", line 486
ORA-06512: at line 2

I also tried

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
   job_name         =>  'parseMsg',
   job_action       =>  'begin S_IN_TDK.parseMsg; end;',
   repeat_interval  =>  'FREQ=SECONDLY;INTERVAL=10',
   --job_style        => 'LIGHTWEIGHT',
   comments         => 'Job that polls device n2 every 10 seconds');
END;

but then I got this error:

 Informe de error -
ORA-06550: line 2, column 3:
PLS-00306: wrong number or types of arguments in call to 'CREATE_JOB'
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Upvotes: 0

Views: 7557

Answers (2)

Jon Heller
Jon Heller

Reputation: 36922

DBMS_SCHEDULER.CREATE_JOB has three required parameters: job_name, job_type, and job_action. Add job_type => 'PLSQL_BLOCK', and also add enabled => true, to make the job run immediately.

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
   job_name         =>  'parseMsg',
   job_type         =>  'PLSQL_BLOCK',
   job_action       =>  'begin S_IN_TDK.parseMsg; end;',
   repeat_interval  =>  'FREQ=SECONDLY;INTERVAL=10',
   --job_style        => 'LIGHTWEIGHT',
   enabled          =>  true,
   comments         => 'Job that polls device n2 every 10 seconds');
END;
/

Use this query to check the job status:

select *
from dba_scheduler_job_run_details
where job_name = 'PARSEMSG'
order by log_date desc;

Upvotes: 2

Tony Andrews
Tony Andrews

Reputation: 132690

Parameter program_name expects the name of a scheduler PROGRAM object. If you want to run an inline program then do use job_action parameter instead:

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
   job_name         =>  'parseMsg',
   job_action       =>  'begin S_IN_TDK.parseMsg; end;',
   repeat_interval  =>  'FREQ=SECONDLY;INTERVAL=10',
   --job_style        => 'LIGHTWEIGHT',
   comments         => 'Job that polls device n2 every 10 seconds');
END;

Note that job_action expects a complete PL/SQL block as input.

Upvotes: 3

Related Questions