CREATE OR REPLACE PROCEDURE WM53.DEL_HIS_BIZDOCCONTENT(RES OUT VARCHAR2)
AS
BEGIN
RES:='' ;
delete from wm53.bizdoccontent where
docid in
( select docid from wm53.bizdoc
where doctimestamp < sysdate -40
and doctimestamp >= sysdate -41) ;
--commit ;
delete from wm53.bizdoccontent where
docid in
( select docid from wm53.bizdoc
where doctimestamp < sysdate -41
and doctimestamp >= sysdate -42) ;
---commit ;
-- p_status := SQLCODE ;
--p_status := 'SUCCESS' ;
RES:='SUCCESS' ;
RETURN ;
EXCEPTION
WHEN OTHERS
THEN
--p_status := SQLCODE || 'EXCEPTION OCCURED IN WM53.DEL_HIS_BIZDOCCONTENT PROCEDURE' || SUBSTR(SQLERRM,1,128);
-- DBMS_OUTPUT.PUT('EXCEPTION OCCURED IN WM53.DEL_HIS_BIZDOCCONTENT' || SQLCODE || SUBSTR(SQLERRM,1,400));
---p_status := 'FAILED' ;
NULL ;
END;
/
存储过程单独运行正常 。
begin
dbms_scheduler.create_job(
job_name => 'DEL53_HIS_BIZDOCCONTENT_JOB',
job_action=> 'WM53.DEL_HIS_BIZDOCCONTENT' ,
start_date => sysdate ,
repeat_interval => 'FREQ=DAILY; BYHOUR=15,16 ; BYMINUTE=0,30',
job_type=>'STORED_PROCEDURE',
enabled=>TRUE);
end;
/
在Toad中查看脚本 job_name => 'SYS.DEL53_HIS_BIZDOCCONTENT_JOB' , 是建立在sys用户下 .
在scheduler 的Log中看到的 err log 如下 ( scheduler 运行还是正常的, 但是就是failed ):
ORA-06553: PLS-ORA-06553: PLS-306: 呼叫 'DEL_HIS_BIZDOCCONTENT' 時使用的引数数目或引数类型错误 :
存储过程在Toad中运行正常 (可以删除对应的值) 。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/35489/viewspace-712493/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/35489/viewspace-712493/