如何使用每日调度作业调用oracle中的存储过程？

Question

我是第一次接触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.

请指导我解决这个问题。

Answer 1

天哪，你的代码看起来太复杂了。首先考虑这个简化：

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;

Answer 2

从调度程序作业执行存储过程的最简单方法是更改job_type。

Rest您可以使用自己的值。试试这个，然后发回结果。

示例：

job_type             => 'STORED_PROCEDURE',

job_action           => '"OWNER"."PROCEDURE_NAME"',

Answer 3

您似乎混合了使用create_job和create program的两种不同方式。请将您的脚本更改为如下所示：

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