博客

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>