Monday 6 December 2010

DDL for Scheduler Jobs

DBMS_METADATA is great for getting any object DDL, but with scheduler jobs it's not so intuitive.
Rather than JOB, or whatever, it takes PROCOBJ:

Example:  
fakeuser@dev01>BEGIN
  2  DBMS_SCHEDULER.create_job (
  3  job_name        => 'my_fake_job',
  4  job_type        => 'PLSQL_BLOCK',
  5  job_action      => 'BEGIN DBMS_STATS.gather_schema_stats(''SCOTT''); END;',
  6  start_date      => SYSTIMESTAMP,
  7  repeat_interval => 'freq=hourly; byminute=0',
  8  end_date        => NULL,
  9  enabled         => TRUE,
 10  comments        => 'My fake job.');
 11  end;
 12  /

PL/SQL procedure successfully completed.

fakeuser@dev01>select dbms_metadata.get_ddl('PROCOBJ',JOB_NAME) FROM USER_SCHEDULER_JOBS;

DBMS_METADATA.GET_DDL('PROCOBJ',JOB_NAME)
--------------------------------------------------------------------------------


BEGIN
dbms_scheduler.create_job('"MY_FAKE_JOB"',
job_type=>'PLSQL_BLOCK', job_action=>
'BEGIN DBMS_STATS.gather_schema_stats(''SCOTT''); END;'
, number_of_arguments=>0,
start_date=>TO_TIMESTAMP_TZ('06-DEC-2010 01.26.37.186707000 PM +00:00','DD-MON-R
RRR HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english'), repeat_interval=>
'freq=hourly; byminute=0'
, end_date=>NULL,
job_class=>'"DEFAULT_JOB_CLASS"', enabled=>FALSE, auto_drop=>TRUE,comments=>
'My fake job.'
);
dbms_scheduler.enable('"MY_FAKE_JOB"');
COMMIT;
END;

1 comment: