ORACLE得schduleJob 创建和详情sql

CREATE OR REPLACE PROCEDURE  syncProvOrderResult

AS 

BEGIN 
   
   INSERT INTO BSNL_LC_FRANCH_TASK_TEST@LINK_CLARITY
(SERVICE_TYPE, CRM_ORDER_ID, TASK_NAME, TASK_STATUS, TASK_COMMENTS, TASK_COMPLETION_DATE, RESPONSE_CODE, RESPONSE_MSG, DOCS_COLLECTION_STATUS, ROOD, RETENTION_FLAG, ENDA_MDF_PILLAR_IN1, ENDA_MDF_PILLAR_IN2, ENDA_MDF_PILLAR_OUT1, ENDA_MDF_PILLAR_OUT2, OD_DP_TAG_NO_AEND, OD_MDF_VERTICAL_AEND, ENDB_MDF_PILLAR_IN1, ENDB_MDF_PILLAR_IN2, ENDB_MDF_PILLAR_OUT1, ENDB_MDF_PILLAR_OUT2, OD_DP_TAG_NO_BEND, OD_MDF_VERTICAL_BEND, CANCELLATION_CHARGES, CANCEL_REQUEST, REMARKS, LAST_MILE, EQUIPMENT_OWNER, OLT_OWNER, EXCH_CODE_ENDA, EXCH_CODE_ENDB, FEEDBACK, CLARITY_RESPONSE_MSG, TELEPHONE_NO, "ZONE", STATUS, ASSIGNED_TO, USERNAME, UPDATED_FROM_WEB_APP, CIRCLE, SSA, HALT_REASON, FMS_TO_CLARITY_UPD_DATE, CLARITY_TO_FMS_UPD_DATE, OFC_PROVIDER, ENDA_OFT, ENDB_OFT, OLT_IP, ONT_PORT, ONT_ID, ONT_SERIAL_NO_MAC_ID, SERVICE_VLAN, END_TYPE, BB_AEND_MODEM1_COLLECTED, FRANCHISEE_FIBRE_LENGTH, ENDA_AERIAL_DISTANCE, ENDB_AERIAL_DISTANCE, ENDB_FRANCHISEE_FIBRE_LENGTH)
SELECT SERVICE_TYPE, CRM_ORDER_ID, TASK_NAME, TASK_STATUS, TASK_COMMENTS, TASK_COMPLETION_DATE, RESPONSE_CODE, RESPONSE_MSG, DOCS_COLLECTION_STATUS, ROOD, RETENTION_FLAG, ENDA_MDF_PILLAR_IN1, ENDA_MDF_PILLAR_IN2, ENDA_MDF_PILLAR_OUT1, ENDA_MDF_PILLAR_OUT2, OD_DP_TAG_NO_AEND, OD_MDF_VERTICAL_AEND, ENDB_MDF_PILLAR_IN1, ENDB_MDF_PILLAR_IN2, ENDB_MDF_PILLAR_OUT1, ENDB_MDF_PILLAR_OUT2, OD_DP_TAG_NO_BEND, OD_MDF_VERTICAL_BEND, CANCELLATION_CHARGES, CANCEL_REQUEST, REMARKS, LAST_MILE, EQUIPMENT_OWNER, OLT_OWNER, EXCH_CODE_ENDA, EXCH_CODE_ENDB, FEEDBACK, CLARITY_RESPONSE_MSG, TELEPHONE_NO, "ZONE", STATUS, ASSIGNED_TO, USERNAME, UPDATED_FROM_WEB_APP, CIRCLE, SSA, HALT_REASON, FMS_TO_CLARITY_UPD_DATE, CLARITY_TO_FMS_UPD_DATE, OFC_PROVIDER, ENDA_OFT, ENDB_OFT, OLT_IP, ONT_PORT, ONT_ID, ONT_SERIAL_NO_MAC_ID, SERVICE_VLAN, END_TYPE, BB_AEND_MODEM1_COLLECTED, FRANCHISEE_FIBRE_LENGTH, ENDA_AERIAL_DISTANCE, ENDB_AERIAL_DISTANCE, ENDB_FRANCHISEE_FIBRE_LENGTH
FROM FMS_NEW_LC_CLOSURE T2 WHERE NOT EXISTS (SELECT CRM_ORDER_ID FROM BSNL_LC_FRANCH_TASK_TEST@LINK_CLARITY T1 WHERE T2.CRM_ORDER_ID = T1.CRM_ORDER_ID);


END;

DECLARE
  num number;
BEGIN
  SELECT count(1) INTO num FROM user_scheduler_jobs WHERE job_name = 'SYSPROVORDERDATA';
  if num > 0 then
     dbms_scheduler.drop_job(job_name => 'SYSPROVORDERDATA');    --如果已存在先drop再create
  end if;

DBMS_SCHEDULER.CREATE_JOB(

job_name =>'SYSPROVORDERDATA',

job_type =>'STORED_PROCEDURE',

job_action =>'SYNCPROVORDERDETAIL',

start_date => sysdate,

repeat_interval =>'FREQ=DAILY;INTERVAL=1;BYHOUR=8;BYMINUTE=0;BYSECOND=0',

enabled => true,

comments => 'sync data from other system ');

END;

SELECT * FROM dba_jobs;

SELECT * FROM dba_SCHEDULER_JOBS WHERE job_Name IN ('SYSPROVORDERDATA','SYSPROVORDERDATARESULT','SYSFAULTORDERDATA','SYSFAULTORDERDATARESULT');

SELECT * FROM dba_scheduler_job_log WHERE job_Name IN ('SYSPROVORDERDATA','SYSPROVORDERDATARESULT','SYSFAULTORDERDATA','SYSFAULTORDERDATARESULT');
SELECT * FROM dba_scheduler_job_run_details WHERE job_Name IN ('SYSPROVORDERDATA','SYSPROVORDERDATARESULT','SYSFAULTORDERDATA','SYSFAULTORDERDATARESULT');

BEGIN 
	dbms_scheduler.run_job('SYSFAULTORDERDATA', TRUE);
END;