PROCEDURE create_job(
program_name in varchar2,
program_type in varchar2,
program_action in varchar2,
number_of_arguments in pls_integer default 0,
job_name in varchar2,
schedule_name in varchar2,
job_class in varchar2 default 'default_job_class',
enabled in boolean default false,
auto_drop in boolean default true,
start_date in timestamp with time zone default null,
repeat_interval in varchar2,
end_date in timestamp with time zone default null,
vo_msg out varchar2
)is
begin vo_msg := gc_ok;
-- Call the procedure
sys.dbms_scheduler.create_program(
program_name => 'emp_program',
program_type => 'stored_procedure',
program_action => 'pkg_test.insert_emp2',
number_of_arguments => 0,
enabled => true
);
-- Call the schedule
sys.dbms_scheduler.create_schedule(
schedule_name => 'emp_schcdule',
start_date => '6-8月 -08 10.00.00.000 上午',
repeat_interval => 'FREQ=MINUTELY; INTERVAL=1',
end_date => '10-8月 -08 12.00.00.000 上午'
);
-- Call the job
sys.dbms_scheduler.create_job(
job_name => 'emp2_job',
program_name => 'emp_program',
schedule_name => 'emp_schcdule',
job_class => 'DEFAULT_JOB_CLASS',
enabled => true,
auto_drop => true
); exception
when others then begin
vo_msg := sqlerrm;
end;
end; 小弟自己做练习pkg_test这个包里 insert_emp2这个存储过程是个插入的简单过程
现在问题是:
SELECT * FROM User_Scheduler_Schedules;
SELECT * FROM User_Scheduler_Jobs;
SELECT * FROM User_Scheduler_Programs;
执行这三个操作都可以看到对应的作业,时间表,过程都在运行,小弟定义的是每分钟插条记录
但是好象插入过程没有执行操作。
我用PLSQL Developer编辑,上述作业过程在
create or replace package body pkg_job_test is 这个包里,我是在过程上点右键进行 TEST操作的。
请大家看看哪有错误,或者说执行方式有问题。
3Q。
program_name in varchar2,
program_type in varchar2,
program_action in varchar2,
number_of_arguments in pls_integer default 0,
job_name in varchar2,
schedule_name in varchar2,
job_class in varchar2 default 'default_job_class',
enabled in boolean default false,
auto_drop in boolean default true,
start_date in timestamp with time zone default null,
repeat_interval in varchar2,
end_date in timestamp with time zone default null,
vo_msg out varchar2
)is
begin vo_msg := gc_ok;
-- Call the procedure
sys.dbms_scheduler.create_program(
program_name => 'emp_program',
program_type => 'stored_procedure',
program_action => 'pkg_test.insert_emp2',
number_of_arguments => 0,
enabled => true
);
-- Call the schedule
sys.dbms_scheduler.create_schedule(
schedule_name => 'emp_schcdule',
start_date => '6-8月 -08 10.00.00.000 上午',
repeat_interval => 'FREQ=MINUTELY; INTERVAL=1',
end_date => '10-8月 -08 12.00.00.000 上午'
);
-- Call the job
sys.dbms_scheduler.create_job(
job_name => 'emp2_job',
program_name => 'emp_program',
schedule_name => 'emp_schcdule',
job_class => 'DEFAULT_JOB_CLASS',
enabled => true,
auto_drop => true
); exception
when others then begin
vo_msg := sqlerrm;
end;
end; 小弟自己做练习pkg_test这个包里 insert_emp2这个存储过程是个插入的简单过程
现在问题是:
SELECT * FROM User_Scheduler_Schedules;
SELECT * FROM User_Scheduler_Jobs;
SELECT * FROM User_Scheduler_Programs;
执行这三个操作都可以看到对应的作业,时间表,过程都在运行,小弟定义的是每分钟插条记录
但是好象插入过程没有执行操作。
我用PLSQL Developer编辑,上述作业过程在
create or replace package body pkg_job_test is 这个包里,我是在过程上点右键进行 TEST操作的。
请大家看看哪有错误,或者说执行方式有问题。
3Q。
代码有3处错误,给你改了下,测试通过,自己试试吧。
DROP TABLE test;
CREATE TABLE test(n INT);
CREATE OR REPLACE PROCEDURE p_insert IS
BEGIN
INSERT INTO test VALUES(1);
END;
/CREATE OR REPLACE PROCEDURE CREATE_JOB(VO_MSG OUT VARCHAR2) IS
BEGIN
VO_MSG := 'GC_OK';
-- Call the procedure
SYS.DBMS_SCHEDULER.CREATE_PROGRAM(PROGRAM_NAME => 'emp_program',
PROGRAM_TYPE => 'stored_procedure',
PROGRAM_ACTION => 'p_insert',
NUMBER_OF_ARGUMENTS => 0,
ENABLED => TRUE);
-- Call the schedule
SYS.DBMS_SCHEDULER.CREATE_SCHEDULE(SCHEDULE_NAME => 'emp_schcdule',
START_DATE => SYSTIMESTAMP,
REPEAT_INTERVAL => 'FREQ=MINUTELY;INTERVAL=1',
END_DATE => SYSTIMESTAMP +
INTERVAL '8' HOUR);
-- Call the job
SYS.DBMS_SCHEDULER.CREATE_JOB(JOB_NAME => 'emp2_job',
PROGRAM_NAME => 'emp_program',
SCHEDULE_NAME => 'emp_schcdule',
JOB_CLASS => 'DEFAULT_JOB_CLASS',
ENABLED => TRUE,
AUTO_DROP => TRUE);EXCEPTION
WHEN OTHERS THEN
BEGIN
VO_MSG := SQLERRM;
END;
END;
/DECLARE
V_C VARCHAR2(1000);
BEGIN
CREATE_JOB(V_C);
DBMS_OUTPUT.PUT_LINE(V_C);
END;
/SELECT * FROM test;
输出:
1
1
1
1BEGIN
SYS.DBMS_SCHEDULER.DROP_JOB('emp2_job');
SYS.DBMS_SCHEDULER.DROP_SCHEDULE('emp_schcdule');
SYS.DBMS_SCHEDULER.DROP_PROGRAM('emp_program');
END;
/