VARIABLE jobno number;
begin
DBMS_JOB.SUBMIT(:jobno, 'LOG_PRO;', trunc(sysdate+1) + 0/24, 'trunc(sysdate+1) + 1');
commit;
end;
/我写了一个存储过程,单独执行是可以的,但是为什么启动定时任务过后一次都没执行成功?
begin
DBMS_JOB.SUBMIT(:jobno, 'LOG_PRO;', trunc(sysdate+1) + 0/24, 'trunc(sysdate+1) + 1');
commit;
end;
/我写了一个存储过程,单独执行是可以的,但是为什么启动定时任务过后一次都没执行成功?
create or replace procedure LOG_PRO
as
v_str varchar2(1000); --临时字段
v_file_handle utl_file.file_type;
v_filename Varchar2(500);
v_str_sql Varchar2(500);
begin
v_filename := '/home/oracle/config/dblinks.properties';
v_file_handle:=utl_file.fopen('DBLINKS',v_filename,'R');
LOOP
utl_file.get_line(v_file_handle,v_str);
/*v_str_sql:= 'insert into TA_MONITOR_EXTRALOG (
select * from TA_MONITOR_EXTRALOG' || '@' || v_str || ' t where t.MOEX_ID in (select distinct MOEX_ID from TA_ACTION_LOG@ORCL182 t
where t.aclo_operate_time between TRUNC((sysdate-1))+1/86400 and
TRUNC((sysdate-1))+1-1/86400)
)';
execute immediate v_str_sql;*/
v_str_sql:= 'insert into TA_ACTION_LOG
(select * from TA_ACTION_LOG' || '@' || v_str || ' t
where t.aclo_operate_time between TRUNC((sysdate-1))+1/86400 and
TRUNC((sysdate-1))+1-1/86400 and aclo_id = 111111111111553)';
execute immediate v_str_sql;
END LOOP
commit;
end LOG_PRO;