plsql - procedimientos - ¿Cómo llamar al procedimiento almacenado en Oracle con los trabajos diarios programados?

procedimientos y funciones oracle pl/sql

Question

Soy nuevo en los guiones de trabajo de Oracle. Escribí un procedimiento de purga para limpiar todos los datos antiguos y conservar los datos de los últimos 3 meses ... el procedimiento se ejecuta con éxito. Está funcionando cuando estoy llamando también manualmente. el procedimiento es el siguiente:

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

mi script JOb es el siguiente:

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;
/

El trabajo se creó con éxito, pero el estado del trabajo falló, no se realizó correctamente. ¿Cuál es la razón detrás de esto? devuelve el siguiente error:

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.

por favor, guíame para resolver esto ...

Answer 1

Parece que estás mezclando dos formas diferentes de usar create_job con create program. Por favor, cambie su script a algo como esto:

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;
/

Answer 2

Omg, tu código parece muy complicado. Considere esta simplificación primero:

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

Definición de trabajo alternativo por 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

Una forma de verificar el trabajo:

select * from user_jobs;