ORACLE10g创建SCHEDULER_JOB

原帖地址

 

BEGIN

  SYS.DBMS_SCHEDULER.CREATE_JOB

    (

       job_name        => 'JOB_SP_INSERT_VEH_ALM_REC' --全部改成你自己的job名称

      ,start_date      => TO_TIMESTAMP_TZ('2012/08/03 16:00:00.000000 +08:00','yyyy/mm/dd hh24:mi:ss.ff tzh:tzm')

      ,repeat_interval => 'FREQ=SECONDLY;INTERVAL=5'

      ,end_date        => NULL

      ,job_class       => 'DEFAULT_JOB_CLASS'

      ,job_type        => 'PLSQL_BLOCK'

      ,job_action      => 'sp_insert_veh_alm_rec;' --调用的这个存储过程名称后有个分号

      ,comments        => NULL

    );

  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE

    ( name      => 'JOB_SP_INSERT_VEH_ALM_REC'

     ,attribute => 'RESTARTABLE'

     ,value     => FALSE);

  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE

    ( name      => 'JOB_SP_INSERT_VEH_ALM_REC'

     ,attribute => 'LOGGING_LEVEL'

     ,value     => SYS.DBMS_SCHEDULER.LOGGING_OFF);

  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL

    ( name      => 'JOB_SP_INSERT_VEH_ALM_REC'

     ,attribute => 'MAX_FAILURES');

  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL

    ( name      => 'JOB_SP_INSERT_VEH_ALM_REC'

     ,attribute => 'MAX_RUNS');

  BEGIN

    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE

      ( name      => 'JOB_SP_INSERT_VEH_ALM_REC'

       ,attribute => 'STOP_ON_WINDOW_CLOSE'

       ,value     => FALSE);

  EXCEPTION

    -- could fail if program is of type EXECUTABLE...

    WHEN OTHERS THEN

      NULL;

  END;

  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE

    ( name      => 'JOB_SP_INSERT_VEH_ALM_REC'

     ,attribute => 'JOB_PRIORITY'

     ,value     => 3);

  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL

    ( name      => 'JOB_SP_INSERT_VEH_ALM_REC'

     ,attribute => 'SCHEDULE_LIMIT');

  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE

    ( name      => 'JOB_SP_INSERT_VEH_ALM_REC'

     ,attribute => 'AUTO_DROP'

     ,value     => TRUE);

  SYS.DBMS_SCHEDULER.ENABLE

    (name                  => 'JOB_SP_INSERT_VEH_ALM_REC');

END;

/

--copy过去 用toad8.5以上的版本执行， pl/sql(7.1.4.1390)不支持！