如何使用DBMS_SCHEDULER.CREATE_JOB过程执行过程 [英] How to execute a procedure with DBMS_SCHEDULER.CREATE_JOB procedure
本文介绍了如何使用DBMS_SCHEDULER.CREATE_JOB过程执行过程的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想创建一个作业,该作业将在给定日期删除数据库对象.作业创建正确,但未执行该过程.试图单独执行该程序,它可以工作.
I want to create a job that would drop a database object at a given date. The job created all right but the procedure is not executed. Tried executing the procedure alone and it works.
这是创建作业的代码
v_jobnam := v_objnam;
v_jobnam := DBMS_SCHEDULER.generate_job_name (v_jobnam);
v_startdate := to_timestamp(v_startdate);
select sysdate + (v_delhrs/1440)
into v_startdate
from dual;
DBMS_SCHEDULER.CREATE_JOB(job_name => v_jobnam, job_type => 'PLSQL_BLOCK', JOB_ACTION => 'BEGIN DROP_OBJ1(' || v_objnam|| ', ' || v_objtyp || ', '|| v_schema || ',' || v_objid ||'); END;', start_date => SYSTIMESTAMP, repeat_interval => 'freq=secondly; bysecond=0', end_date => NULL, enabled => TRUE, comments => 'Calls PLSQL once');
其中v_delhrs是一个数字. 这是该过程的代码:
where v_delhrs is a number. Here's the code for the procedure:
PROCEDURE DROP_OBJ1
(
p_objnam IN CHAR,
p_objtyp IN CHAR,
p_copyto IN ALL_OBJECTS.OWNER%TYPE,
p_objid IN NUMBER
)
IS
v_objnam VARCHAR2 (30);
v_objtyp VARCHAR2 (30);
v_copyto VARCHAR2 (30);
v_objid NUMBER (3);
BEGIN
v_objnam := UPPER (p_objnam);
v_objtyp := UPPER (p_objtyp);
v_copyto := UPPER (p_copyto);
v_objid := p_objid;
--v_copyby := UPPER (p_copyby);
EXECUTE IMMEDIATE ( ' DROP '
|| v_objtyp
|| ' '
|| v_copyto
|| '.'
|| v_objnam
);
EXECUTE IMMEDIATE ( ' DELETE FROM COPY_OBJ_DET WHERE OBJ_ID = '
|| v_objid
);
COMMIT;
END;
我知道这是一个非常小的问题. 预先感谢大师.
I know its a very minor problem. Thanks in advance gurus.
干杯!
推荐答案
对我来说,您在DBMS_JOBS设置调用中错过了一些引号.
It apears to me that you have missed some quote marks in the DBMS_JOBS setup call.
尝试一下:
DBMS_SCHEDULER.CREATE_JOB(job_name => v_jobnam,
job_type => 'PLSQL_BLOCK',
JOB_ACTION => 'BEGIN DROP_OBJ1(''' || v_objnam
|| ''', ''' || v_objtyp || ''', '''
|| v_schema || ''',' || v_objid
|| '); END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=secondly; bysecond=0',
end_date => NULL,
enabled => TRUE,
comments => 'Calls PLSQL once');
如果这不是解决方案,我将在数据库上设置您的代码,然后自己尝试.
If this is not the solution I will setup your code on a database and try it myself.
这篇关于如何使用DBMS_SCHEDULER.CREATE_JOB过程执行过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文