Re: "create or replace" for dbms_job

On 12 Aug 2006 10:08:42 -0700, "dn.perl_at_gmail.com" <dn.perl_at_gmail.com> wrote:

>
>My aa.sql file contains code on the lines of ...
>--------------------
>create or replace procedure proc_aa as
>begin -- {
> statements
>end -- }
>/
>
>VARIABLE v_JobNum NUMBER
>BEGIN
> DBMS_JOB.SUBMIT(:v_JobNum, 'proc_aa ;', trunc(sysdate) + 6/24,
> 'trunc(sysdate + (1/(24)),''HH'')');
>END;
>/
>
>PRINT v_JobNum
>
>COMMIT;
>--------------------
>
>I run : @aa.sql and start a dbms_job .
>Then I modify the procedure a bit, and run @aa.sql again.
>This starts one more dbms_job which runs the procedure
>on the hour every hour. Is there any simple way of running
>a 'create or replace dmbs_job' like construct, or do I have
>to do it the slightly harder way of finding out the job number
>first for the old version of the procedure, and then deleting it.
>

jobs can be modified. Look in the doco for the dbms_job package in the Oracle documentation.

>Query # 2 : Correct me if I am wrong but the dbms_job has
>an air of 'DDL' about it; why should it be necessary to commit
>the creation or deletion of a dbms_job?
>
>Query # 3 : Which book (preferably O'Reilly's) or online
>documentation is the best
>way of finding out what parameters dbms_job.submit takes
>or the meaning of the fields which make up 'dba_jobs' view
>and suchlike matters?

The Oracle documentation at http://tahiti.oracle.com will do just fine.

>
>Thanks in advance.

--
Sybrand Bakker, Senior Oracle DBA