RE:Tareas Programadas en Oracle
Existe un Package en ORACLE que hace lo que necesitas ...
DBMS_JOB
Un ejemplo ...
-------------------------------------------------------
select job ID,
failures FAILURES,
substr(to_char(next_date,'dd-mm-yy hh24:mi:ss'),1,20) NEXT_DATE,
SUBSTR(what,1,25) PROCESO,
NEXT_SEC,
THIS_DATE
from user_jobs;
-- Revisar que procesos se ejecutaron...
select distinct nombre_proceso, trunc(fecha_proceso)
from cou_procesos_log
/
JOB FAILURES SUBSTR(TO_CHAR(NEXT_ WHAT
---------- ---------- -------------------- -------------------
194 0 09-05-01 00:06 COUAP066;
191 0 09-05-01 05:19 COUAP067;
198 0 08-05-01 22:13 couap065('TODOS');
-- Programar Job...
VAR JOBNO NUMBER;
BEGIN
DBMS_JOB.SUBMIT(:JOBNO,'COUAP065(''TODOS'');',SYSDATE+11/24,'SYSDATE+1');
COMMIT;
END;
VAR JOBNO NUMBER;
BEGIN
DBMS_JOB.SUBMIT(:JOBNO,'COUAP066;',SYSDATE+4/24,'SYSDATE+1');
COMMIT;
END;
VAR JOBNO NUMBER;
BEGIN
DBMS_JOB.SUBMIT(:JOBNO,'COUAP067;',SYSDATE+16/24,'SYSDATE+1');
COMMIT;
END;
VAR JOBNO NUMBER;
BEGIN
DBMS_JOB.SUBMIT(:JOBNO,'COUAP049;',SYSDATE+4/24,'SYSDATE+1');
COMMIT;
END;
VAR JOBNO NUMBER;
BEGIN
DBMS_JOB.SUBMIT(:JOBNO,'COU_CVP_NO_EN_COU;',SYSDATE+19/24,'SYSDATE+1');
COMMIT;
END;
VAR JOBNO NUMBER;
BEGIN
DBMS_JOB.SUBMIT(:JOBNO,'COU_NAVISCORE_SAU_CVP;',SYSDATE+19/24,'SYSDATE+1');
COMMIT;
END;
-- Ejecutar Job...
EXEC DBMS_JOB.RUN(207);
-- Eliminar Job de la lista...
EXEC DBMS_JOB.REMOVE(50);
------------------Probar este job-----------------
EXEC DBMS_JOB.RUN(50);
VAR JOBNO NUMBER;
BEGIN
dbms_job.submit ( :jobno, 'sp_coberturas;', sysdate,
'sysdate+0.025/24');
commit;
END;
-- execute dbms_job.submit (:j,'aaa;',sysdate,'sysdate+120/86400');
create or replace procedure sp_coberturas is
begin
insert into cou_coberturas(
NBR ,
SITIO ,
DESCRIPCION ,
CENTRAL ,
CANTIDAD ,
E1PMP ,
E1CANAL ,
SP_USER1 ,
SP_SORT ,
UNQOD_LN ,
UNQO_GEN_CAUX##1 ,
STATUS ,
FECHA_CREACION )
SELECT
UNQOD_NBR NBR,
UNQOD_SITIO SITIO,
UNSI_NAME DESCRIPCION,
UNQOD_CENTRAL CENTRAL,
UNQOD_QTY_ORD * UNQOD_FR_RANURA CANTIDAD,
UNQOD_E1PMP E1PMP,
UNQOD_E1CANAL E1CANAL,
SP_USER1,
SP_SORT,
UNQOD_LN,
NVL(TRIM(UNQO_GEN_CAUX##1),'VACIO') UNQO_GEN_CAUX##1,
1,
SYSDATE
FROM
SP_MSTR,
UNSI_MSTR,
UNQOD_DET,
UNQO_MSTR
WHERE UNQO_VENDEDOR = SP_ADDR
AND UNQOD_DET.UNQOD_QTY_ORD * UNQOD_FR_RANURA > (
SELECT NVL(SUM(CAN.NUM_SERVICIOS),0)
FROM COU_APROVIS_NSIF CAN
WHERE CAN.UNQOD_SITIO = UNQOD_DET.UNQOD_SITIO
AND CAN.UNQOD_NBR = UNQOD_DET.UNQOD_NBR
AND CAN.UNQOD_LN = UNQOD_DET.UNQOD_LN )
AND UNQOD_NBR = UNQO_NBR
AND UNQOD_SITIO = UNSI_SITIO
AND UNQOD_FR_SWITCH <> 'NODO_CENTRAL'
AND UNQOD_PART LIKE 'UNC%';
commit;
END;
/