问题已经解决,感谢itpub用户abstractcyj的博文《 Oracle dbms_scheduler创建带输入参数的JOB》http://blog.itpub.net/8520577/viewspace-1154003/
通过dbms_scheduler.create_job直接创建job后,在调用run_job的时候不能直接传入参数,先得创建program、声明输入参数,然后在创建Job,最后设置入参,结束。具体代码如下:
--create program
DBMS_SCHEDULER.CREATE_PROGRAM(PROGRAM_NAME => 'jp_analyze_program',
PROGRAM_TYPE => 'STORED_PROCEDURE',
PROGRAM_ACTION => 'jp_analyze.doAppDaily',
NUMBER_OF_ARGUMENTS => 1,
ENABLED => FALSE,
COMMENTS => 'create analyze jp_job_context program');
--define argument
DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT(PROGRAM_NAME => 'jp_analyze_program',
ARGUMENT_NAME => 'v_date',
ARGUMENT_POSITION => 1,
ARGUMENT_TYPE => 'date',
DEFAULT_VALUE => NULL);
DBMS_SCHEDULER.ENABLE(NAME => 'jp_analyze_program');
-- create job
DBMS_SCHEDULER.CREATE_JOB(JOB_NAME => 'jp_analyze_job1',
PROGRAM_NAME => 'jp_analyze_program',
START_DATE => SYSDATE, --job开始执行时间
REPEAT_INTERVAL => 'FREQ=DAILY; BYHOUR=' ||
T_HOUR ||
';BYMINUTE=0;BYSECOND=0', --job执行频率
END_DATE => NULL, --job结束执行时间
ENABLED => TRUE,
COMMENTS => 'start analyze jp_job_context'); --job描述
--set argument
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(JOB_NAME => 'jp_analyze_job1',
ARGUMENT_POSITION => 1, --参数序号
ARGUMENT_VALUE => NULL);
补充:
删除job和program语句
--drop job
SELECT COUNT(*)
INTO T_COUNT
FROM DBA_SCHEDULER_JOBS
WHERE JOB_NAME = UPPER('jp_analyze_job1');
IF (T_COUNT > 0) THEN
DBMS_SCHEDULER.DROP_JOB('jp_analyze_job1');
END IF;
-- drop user program USER_SCHEDULER_PROGRAMS
SELECT COUNT(*)
INTO T_COUNT_USER_PRO
FROM USER_SCHEDULER_PROGRAMS
WHERE PROGRAM_NAME = UPPER('jp_analyze_program');
IF (T_COUNT_USER_PRO > 0) THEN
DBMS_SCHEDULER.DROP_PROGRAM('jp_analyze_program');
END IF;