The next example calls a stored procedure, which passes three parameters with values of maintenance, 1000, and Friday and will always occur every Friday night at 10:00 p.m. This procedure will launch several jobs for maintenance and then back up the system. Review this procedure in Listing 19.2.

Listing 19.2. A more complex approach using SUBMIT.

1: DECLARE
2:      v_JobNum  BINARY_INTEGER;
3: BEGIN
4:      DBMS_JOB.SUBMIT(v_JobNum,'WEEKLY(`'maintenance'',1000,
5:           `'Friday'');',SYSDATE,
6:           `NEXT_DAY(TRUNC(SYSDATE),''FRIDAY'') + 22/24');
7: END;

ANALYSIS This process submits a stored procedure called WEEKLY in line 4, which requires three parameters.

Another different parameter is the interval specified in line 6. These settings guarantee that the job will always run on Friday at precisely 22/24, or 10:00 p.m.

Submitting Jobs to the Job Queue with ISUBMIT

With SUBMIT, the job number is assigned automatically by Oracle, which keeps track of the last job number assigned and increments this number by one. However, what if you want to assign a specific job number to a process? You can safely do this with ISUBMIT, as long as the job number has not been used!

The Syntax for the ISUBMIT Procedure
The format for ISUBMIT is almost identical to that for SUBMIT:

PROCEDURE ISUBMIT(job_number_specified OUT BINARY_INTEGER,
     job_to_submit IN VARCHAR2,
     next_run IN DATE DEFAULT SYSDATE,
     interval IN VARCHAR2 DEFAULT NULL,
     job_parsing IN BOOLEAN DEFAULT false);

The only difference is that you specify the job number when calling ISUBMIT. See Listing 19.3 for an example of ISUBMIT.

INPUTListing 19.3. Working with ISUBMIT.

1: BEGIN
2:      DBMS_JOB.ISUBMIT(110,'HELLO;',SYSDATE,'SYSDATE + (1/(24*60*60))');
3: END;

ANALYSIS In this example, the job number is hard-coded to 110 in line 2. You can also set this parameter to a variable for use in manipulation with PL/SQL code, as shown in Listing 19.4.

Listing 19.4. Using ISUBMIT with variables.

1: DECLARE
2:      v_jobnum BINARY_INTEGER := 109;
3: BEGIN
4:      DBMS_JOB.ISUBMIT(v_jobnum,'HELLO;',SYSDATE,
5:           `SYSDATE + (1/(24*60*60))');
6: END;

ANALYSIS The only difference here is declaring the variable v_jobnum, assigning to it a value of 109 in line 2, and then passing this parameter with ISUBMIT in line 4.

Using RUN to Execute Jobs Immediately

You can execute jobs immediately after they have been sent to the job queue by using the RUN procedure.

The Syntax for the RUN Procedure
The format for RUN is

PROCEDURE RUN(job_number_specified IN BINARY_INTEGER);

In order to process this, you must know the job number assigned to the job you want to execute. When the process executes, the next date for the job is reset. The time interval will occur after the run date and time when the job was executed. Again, if you had initially run the job on Friday with a SYSDATE + 7 time interval and you expect the job to run every Friday, and if you now immediately execute this job on Thursday, the job will run every Thursday. Listing 19.5 shows the original submission of the job, and Listing 19.6 shows an example of using RUN.

INPUTListing 19.5. Using DBMS_OUTPUT to see the job number assigned.

1: DECLARE
2:      v_jobnum BINARY_INTEGER;
3: BEGIN
4:      DBMS_JOB.SUBMIT(v_jobnum,'HELLO;',SYSDATE,
5:           `SYSDATE + (1/(24*60*60))');
6:      DBMS_OUTPUT.ENABLE;
7:      DBMS_OUTPUT.PUT_LINE(`Your Job Number assigned is: ` || v_jobnum);
8: END;

The two lines added allow you to see what job number is assigned to the process you just submitted for execution. In this case, the sample output is

OUTPUT
      Your Job Number assigned is: 13

You can now run the job from Listing 19.6.

INPUTListing 19.6. Using RUN to execute the job in the queue immediately.

1: BEGIN
2:      DBMS_JOB.RUN(13);
3: END;

The code in Listing 19.6 will immediately execute job 13.

The Job Environment

When you submit a job, the following variables are stored within Oracle:

• The current user.
• The user submitting or altering the job.

• Current job schemas, such as the job number assigned, and so on. This is reviewed in the section "Viewing Jobs."
• MAC privileges (optional).

In addition, the NLS parameters stored in Oracle include

• NLS_LANGUAGE
• NLS_CURRENCY
• NLS_ISO_CURRENCY
• NLS_NUMERIC_CHARACTERS
• NLS_DATE_FORMAT
• NLS_DATE_LANGUAGE
• NLS_SORT

When the job is executed, the NLS parameters are restored. You can change these characteristics with the ALTER procedure discussed later in this chapter in the section "Altering a Job."

The Job Owner
As soon as a job is submitted, Oracle records and assigns ownership of the job to the user who submitted the job. Only the owner can change the job, execute the job upon demand, and remove the job from the queue.

The Job Numbers
As previously discussed, Oracle assigns the next sequential job number from the stored value SYS.JOBSEQ. This job number can't be changed or assigned to a different process until the job is removed. You can always specify your own job number with ISUBMIT, but if the job number already exists, your job will not execute. The error you will receive if you attempt to use the same job number is

OUTPUT
ERROR at line 1:
ORA-00001: unique constraint (SYS.I_JOB_JOB) violated
ORA-06512: at "SYS.DBMS_JOB", line 105
ORA-06512: at line 2

The Job Definition
The job definition is the identifier of the PL/SQL code to execute. This is usually, but not always, a stored procedure. Any parameters that must have the single quote with normal
PL/SQL parameters must now be enclosed by two single quotes; otherwise, when Oracle removes the single quotes when processing the job, you will get an invalid parameter. Table 19.3 lists some additional special parameters recognized by Oracle.