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

概述我是oracle工作脚本的新手.我写了一些清除程序来清理所有旧数据并保留最近3个月的数据…程序执行成功.我也在手动调用时工作.程序如下： CREATE OR REPLACE PROCEDURE Archive IS v_query varchar2(2048); v_tablename VARCHAR2(50); v_condition varchar 我是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;begindbms_job.submit(    JOB => jID,WHAT => 'pseb.archive;',NEXT_DATE => SYSDATE,INTERVAL  => 'sysdate +2');end;/commit; -- <<--added commit here

一种检查工作的方法：

select * from user_jobs;

总结

以上是内存溢出为你收集整理的plsql – 如何使用每日预定作业调用oracle中的存储过程？全部内容，希望文章能够帮你解决plsql – 如何使用每日预定作业调用oracle中的存储过程？所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。