"create or replace" for dbms

From: <dn.perl_at_gmail.com>
Date: 12 Aug 2006 10:08:42 -0700
Message-ID: <1155402522.094600.265490@b28g2000cwb.googlegroups.com>

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.

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?

Thanks in advance. Received on Sat Aug 12 2006 - 12:08:42 CDT

"create or replace" for dbms_job