Problema con variable al crear un JOB
Estoy intentando hacer una función que cree jobs y no soy capaz:
/* Representa el identificar de un procedimiento almacenado. */
SUBTYPE t_id_proc IS VARCHAR2(32);
/* Representa el identificar de un job. */
SUBTYPE t_id_job IS NUMBER;
/* Procedimiento para registrar procesos almacenados como jobs. */
PROCEDURE anadir_job (
i_id_job IN t_id_job,
i_id_proc IN t_id_proc
) IS
BEGIN
dbms_job.submit(:i_id_job, i_id_proc || ';', sysdate, 'sysdate + 1/48');
END;
Al compilar me da error la variable I_ID_JOB. ¿Qué hago mal?
/* Representa el identificar de un procedimiento almacenado. */
SUBTYPE t_id_proc IS VARCHAR2(32);
/* Representa el identificar de un job. */
SUBTYPE t_id_job IS NUMBER;
/* Procedimiento para registrar procesos almacenados como jobs. */
PROCEDURE anadir_job (
i_id_job IN t_id_job,
i_id_proc IN t_id_proc
) IS
BEGIN
dbms_job.submit(:i_id_job, i_id_proc || ';', sysdate, 'sysdate + 1/48');
END;
Al compilar me da error la variable I_ID_JOB. ¿Qué hago mal?
1 respuesta
Respuesta de thetaurus
1
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
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
Si no tienes ningun otra duda.
