如何用dbms_job创建job

DBMS_JOB.SUBMIT( 
   job       OUT    BINARY_INTEGER,
   what      IN     VARCHAR2, NEXT_DATE IN DATE DEFAULTSYSDATE, 
   interval  IN     VARCHAR2 DEFAULT 'NULL',
   no_parse  IN     BOOLEAN DEFAULT FALSE,
   instance  IN     BINARY_INTEGER DEFAULT ANY_INSTANCE,
   force     IN     BOOLEAN DEFAULT FALSE);

Use the parameters instance and force to control job and instance affinity. The default value of instance is 0 (zero) to indicate that any instance can execute the job. To run the job on a certain instance, specify the instance value. Oracle displays error ORA-23319 if the instance value is a negative number or NULL.

The force parameter defaults to false. If force is TRUE, any positive integer is acceptable as the job instance. If force is FALSE, the specified instance must be running, or Oracle displays error number ORA-23428.

 

 

SQL> col what format a10;

SQL> select j.JOB,j.LAST_DATE,j.NEXT_DATE ,what from user_jobs j;

       JOB LAST_DATE   NEXT_DATE   WHAT
---------- ----------- ----------- ----------

SQL>
SQL> variable jobno number;
SQL> begin
  2  sys.dbms_job.submit(job => :jobno,
  3                      what => 'HELLOW;',
  4                      next_date => to_date('28-09-2010', 'dd-mm-yyyy'),
  5                      interval => 'TRUNC(SYSDATE+1)');
  6    commit;
  7  end;
  8  /

PL/SQL procedure successfully completed
jobno
---------
43

SQL> select j.JOB,j.LAST_DATE,j.NEXT_DATE ,what from user_jobs j;

       JOB LAST_DATE   NEXT_DATE   WHAT
---------- ---------------   ----------- ----------
        43                       2010-9-28   HELLOW;

SQL>