plsql – 如何使用每日预定作业调用oracle中的存储过程？

我是oracle工作脚本的新手.我写了一些清除程序来清理所有旧数据并保留最近3个月的数据…程序执行成功.我也在手动调用时工作.程序如下：

CREATE OR REPLACE PROCEDURE Archive 
IS

       v_query varchar2(2048);
       v_tablename VARCHAR2(50);
       v_condition varchar2(50);
       TYPE cur_typ IS REF CURSOR;
       c cur_typ;
BEGIN 
    OPEN c for 'select tablename,columnname from pseb.purge_tables';
        FETCH c INTO v_tablename,v_condition;
        LOOP
           EXIT WHEN c%NOTFOUND;
           if(v_tablename ='cfw.DCTBLPERFCUMULATIVEMASTER') then
               v_query:='delete from cfw.DCTBLPERFDCUMULATIVEB3MAINREG where cumulativeid in (select cumulativeid FROM '|| v_tablename || ' WHERE ' || v_condition||' < sysdate-90';
               execute immediate v_query;
               v_query:='delete from cfw.DCTBLPERFDCUMULATIVEB4TODENERG where cumulativeid in (select cumulativeid FROM '|| v_tablename || ' WHERE ' || v_condition||' < sysdate-90';
               execute immediate v_query;
               v_query:='delete from cfw.DCTBLPERDFCUMULATIVEB5MAXDEMAN where cumulativeid in (select cumulativeid FROM '|| v_tablename || ' WHERE ' || v_condition||' < sysdate-90';
               execute immediate v_query;
               v_query:='delete from cfw.DCTBLPERFDCUMULATIVEB6TODREG where cumulativeid in (select cumulativeid FROM '|| v_tablename || ' WHERE ' || v_condition||' < sysdate-90';
               execute immediate v_query;
               v_query:='delete from cfw.DCTBLPERFDCUMULATIVEB7MAXDEMAN where cumulativeid in (select cumulativeid FROM '|| v_tablename || ' WHERE ' || v_condition||' < sysdate-90';
               execute immediate v_query;
               v_query:='delete from cfw.DCTBLPERFDCUMULATIVEB8MAXDEMAN where cumulativeid in (select cumulativeid FROM '|| v_tablename || ' WHERE ' || v_condition||' < sysdate-90';
               execute immediate v_query;
               v_query:='delete FROM '|| v_tablename || ' WHERE ' || v_condition||' < sysdate-90';
               execute immediate v_query;
           else
           begin
               v_query:='delete FROM '|| v_tablename || ' WHERE ' || v_condition||' < sysdate-90';
               execute immediate v_query;
           end;
           end if;
       FETCH c INTO v_tablename,v_condition;
        end LOOP;
        close c;
END; --Procedure

我的JOb脚本如下：

begin
  DBMS_SCHEDULER.CREATE_JOB (
     job_name           =>  'purgeproc_automation',job_type           =>  'STORED_PROCEDURE',job_action         =>  'call pseb.archive();',repeat_interval    =>  'FREQ=DAILY;INTERVAL=2',/* every other day */
     auto_drop          => false,enabled            => true,comments           =>  'My new job');
end;
/

作业已成功创建,但作业状态失败,但未成功.它背后的原因是什么？它返回以下错误：

ORA-06550: line 1,column 728:
PLS-00103: Encountered the symbol "PSEB" when expecting one of the following:

   := . ( @ % ;
The symbol ":=" was substituted for "PSEB" to continue.

请指导我解决这个问题……

解决方法

哦,你的代码看起来很复杂.首先考虑这种简化：

CREATE OR REPLACE PROCEDURE Archive 
IS
   v_query varchar2(2048);
BEGIN 
    FOR REC IN (select tablename,columnname condition from pseb.purge_tables)
    LOOP
       if(rec.tablename ='cfw.DCTBLPERFCUMULATIVEMASTER') then
           v_query:='delete from cfw.DCTBLPERFDCUMULATIVEB3MAINREG where cumulativeid in (select cumulativeid FROM '|| rec.tablename || ' WHERE ' || rec.condition||' < sysdate-90';
           execute immediate v_query;
           v_query:='delete from cfw.DCTBLPERFDCUMULATIVEB4TODENERG where cumulativeid in (select cumulativeid FROM '|| rec.tablename || ' WHERE ' || rec.condition||' < sysdate-90';
           execute immediate v_query;
           v_query:='delete from cfw.DCTBLPERDFCUMULATIVEB5MAXDEMAN where cumulativeid in (select cumulativeid FROM '|| rec.tablename || ' WHERE ' || rec.condition||' < sysdate-90';
           execute immediate v_query;
           v_query:='delete from cfw.DCTBLPERFDCUMULATIVEB6TODREG where cumulativeid in (select cumulativeid FROM '|| rec.tablename || ' WHERE ' || rec.condition||' < sysdate-90';
           execute immediate v_query;
           v_query:='delete from cfw.DCTBLPERFDCUMULATIVEB7MAXDEMAN where cumulativeid in (select cumulativeid FROM '|| rec.tablename || ' WHERE ' || rec.condition||' < sysdate-90';
           execute immediate v_query;
           v_query:='delete from cfw.DCTBLPERFDCUMULATIVEB8MAXDEMAN where cumulativeid in (select cumulativeid FROM '|| rec.tablename || ' WHERE ' || rec.condition||' < sysdate-90';
           execute immediate v_query;
           v_query:='delete FROM '|| rec.tablename || ' WHERE ' || rec.condition||' < sysdate-90';
           execute immediate v_query;
       else
           v_query:='delete FROM '|| rec.tablename || ' WHERE ' || rec.condition||' < sysdate-90';
           execute immediate v_query;
       end if;
    END LOOP;
END; --Procedure

dbms_job.submit的替代作业定义：

declare 
 jid number;
begin
dbms_job.submit(
    JOB => jid,WHAT => 'pseb.archive;',NEXT_DATE => SYSDATE,INTERVAL  => 'sysdate +2');
end;
/
commit; -- <<--added commit here

一种检查工作的方法：

select * from user_jobs;