oracle 如何使用 DBMS_SCHEDULER.CREATE_JOB 过程执行过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1769906/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to execute a procedure with DBMS_SCHEDULER.CREATE_JOB procedure
提问by Tunde
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.
我想创建一个在给定日期删除数据库对象的作业。作业创建正常，但程序未执行。尝试单独执行该过程并且它有效。
Here's the code for the create job
这是创建作业的代码
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');
where v_delhrs is a number. Here's the code for the procedure:
其中 v_delhrs 是一个数字。这是该过程的代码：
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.
我知道这是一个非常小的问题。在此先感谢大师。
Cheers!
干杯!
回答by Philip Schlump
It apears to me that you have missed some quote marks in the DBMS_JOBS setup call.
在我看来，您在 DBMS_JOBS 设置调用中遗漏了一些引号。
Try this:
尝试这个：
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.
如果这不是解决方案，我将在数据库上设置您的代码并自己尝试。
