sql - 自动化 PL/SQL 过程

Question

我在 pl/sql 程序下创建了。我想安排它每天执行。我正在使用 dbms.job 来安排它在每天的特定时间运行。程序编译成功。但是虽然我安排了它并没有自动执行。

CREATE OR REPLACE PROCEDURE Schema.Payment_Updation_Process
AS
BEGIN
dbms_output.put_line ('The payments updation process started at '|| rpad(' ',0,' ')||SYSDATE); 

EXECUTE IMMEDIATE 'drop table account_new_arr_1'
;
update tab_dld
set dr_date = sysdate
where table_name = 'account_new_arr_1'
and dr_date is null
;
commit
;
insert into tab_dld
values ('account_new_arr_1',sysdate,null,null,null,tab_dld_seq.nextval)
;
commit
;
EXECUTE IMMEDIATE 'create table account_new_arr_1
tablespace TOOLS
storage (initial 1M next 1M)
as (
select t1.customer_segment,t2.account_num,t2.last_bill_seq,
round((t2.total_billed_tot - t2.total_paid_tot);1000,2) new_arr
from accountattributes t1, account t2
where t1.customer_segment in (''01'',''05'',''31'',''32'',''36'',''37'')
and t2.account_num = t1.account_num
and t2.invoicing_co_id = 1
and to_char(t2.last_bill_dtm,''YYYYMM'') > ''201204''
and t2.currency_code = ''LKR''
)'
;

update tab_dld
set en_date = sysdate,
cc = (select count(*)
from account_new_arr_1)
where table_name = 'account_new_arr_1'
and en_date is null
and dr_date is null
;
commit
;
EXECUTE IMMEDIATE 'create index i_account_new_arr_1
on account_new_arr_1(account_num)
tablespace TOOLS
storage (initial 1M next 1M)'
;
END;
/

这是我试图安排的方式。

declare 
jid number;
begin
dbms_job.submit(
    JOB => jid,
    WHAT => 'Payment_Updation_Process;', 
    NEXT_DATE =>  to_timestamp_tz('09-JULY-21 08.28.00 Asia/Calcutta',
  'DD-MON-RR HH24:MI:SS TZR'),
    INTERVAL  => 'sysdate +1');
end;
/
commit;

当使用 ' select * from user_jobs;' 检查状态时，它会显示失败。有谁知道原因？

标签： sqloracleplsqlscheduled-tasks