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;