|
amigo te envio informacion de pruebas que yo he realizado creando trabajos, quedaría que coloques el codigo que te interece en una funcion o procedimiento.
create table prueba ( p_sysdate date
,p_session_id number)
declare
v_session number;
begin
/*/ select userenv('SESSIONID')
into v_session
from dual;*/
prueba123(sysdate,userenv('SESSIONID'));
end;
create procedure prueba123(p_sysdate date,p_session_id number)
is
begin
insert
into prueba
values(p_sysdate,p_session_id);
commit;
end prueba123;
--CREAR JOB
DECLARE
W_JOB_NUMBER NUMBER;
BEGIN
DBMS_JOB.SUBMIT(W_JOB_NUMBER
,'prueba123(SYSDATE,userenv(''SESSIONID''));'
,SYSDATE
,'SYSDATE+(1/24/60)' --'last_day(sysdate)'--'SYSDATE+(1/24/60)' --CADA FIN DE MES 'last_day(sysdate)'
,FALSE
);
COMMIT;
DBMS_OUTPUT.PUT_LINE('NRO.JOB: '||TO_CHAR(W_JOB_NUMBER));
END;
/
--ELIMINAR JOB
DECLARE
W_JOB_NUMBER NUMBER;
BEGIN
/* SELECT JOB
INTO W_JOB_NUMBER
FROM DBA_JOBS
WHERE WHAT LIKE 'PROCEDURE(PARAMETRO_1,PARAMETRO_2,...,PARAMETRO_N)';*/
DBMS_JOB.REMOVE(:W_JOB_NUMBER);
COMMIT;
END;
/
BEGIN
DBMS_JOB.RUN(:W_JOB_NUMBER);
END;
select *
from prueba
last_day(sysdate)
SELECT SYSDATE+(1/24/60),SYSDATE
FROM DUAL
select *
from DBA_JOBS
Note 61730.1 Using the DBMS_JOB Package details how to use this package when scheduling a job.
Note 1068369.6 Example: Using DBMS_JOB.SUBMIT to Execute Jobs at Regular Intervals details examples of using the DBMS_JOB.SUBMIT.
Note 163992.1 How to Schedule Jobs to Re-run at Exactly Quarter of an Hour Intervals? This note will tell you how to schedule in quarter hours.
Note 228059.1
Note.191575.1 : How to send email using utl_smtp without getting special characters stripped
Note.106513.1 : Basics on How to use UTL_SMTP
Bug:328164
Note:1026586.6
Note:1068369.6 Using DBMS_JOB
Note:113372.1 - Jobs Do Not Automatically Start When Using the DBMS_JOB.SUBMIT Procedure
Note:61730.1 - Using the DBMS_JOB Package
select userenv('SESSIONID')
FROM DUAL
select *
from prueba
SELECT *
FROM SYS.JOB$
SELECT *
FROM DBA_OBJECTS
WHERE OBJECT_NAME LIKE '%JOB%'
AND OBJECT_TYPE IN ('TABLE')
DELETE
from prueba
COMMIT
SELECT JOB,WHAT,TO_CHAR(LAST_DATE,'SYYYY/MM/DD HH24:MI:SS'),
TO_CHAR(NEXT_DATE,'SYYYY/MM/DD HH24:MI:SS') FROM DBA_JOBS
|
