The third option uses the NEXT_DATE procedure:

PROCEDURE NEXT_DATE(job_number IN BINARY_INTEGER,
                             next_run IN DATE);

The fourth option uses the INTERVAL procedure:

PROCEDURE INTERVAL(job_number IN BINARY_INTEGER,
                             interval IN VARCHAR2 );

The procedure CHANGE alters all the job parameters, whereas WHAT, NEXT_DATE, and INTERVAL alter only those specific parameters.

Importing and Exporting Jobs

A nice feature that is provided in the DBMS_JOB package is the ability to import and export jobs from one database to another. However, the job number assigned from the source database will become the job number in the destination database. This is a problem only if the destination database already has a job with the same job number. If there is a conflict with job numbers, simply resubmit the job in the source database with ISUBMIT and assign it a job number not used in the destination database, export the job, and delete the extra job in the source database.

The Syntax for the USER_EXPORT Procedure
The format for USER_EXPORT is

PROCEDURE USER_EXPORT(job_number IN BINARY_INTEGER,
                             Destination_database OUT VARCHAR2);

Handling Broken Jobs

A broken job is a job that has failed to execute 16 times in a row. Oracle marks this job with a flag in the BROKEN column and stores the value true in the column. The only way this job will execute is if you

• Use DBMS_JOB.RUN to execute the job.
• Change the flag in the BROKEN column to a status of fixed, where BROKEN is equal to false.

The section "Using RUN to Execute Jobs Immediately" has already covered how to run a job.

The Syntax for the BROKEN Procedure
To mark a job as fixed with the BROKEN procedure, the syntax is

PROCEDURE BROKEN(job_number IN BINARY_INTEGER,
                              broken_status IN BOOLEAN,
                              next_date IN DATE DEFAULT SYSDATE);

Listing 19.10 shows how to start a broken job running.

INPUTListing 19.10. Starting a broken job.

1: BEGIN
2:      DBMS_JOBS.BROKEN(109,false,SYSDATE + 7);
3: END;

ANALYSIS This job will now execute in one week. You could also mark a valid job as broken by the code in Listing 19.11.

Listing 19.11. Creating a broken job.

1: BEGIN
2:      DBMS_JOBS.BROKEN(109,true);
3: END;

Hands-On Practice in Job Management

You will now run through a long exercise to demonstrate some of the concepts learned today. You will practice creating a few procedures, submitting the jobs, immediately executing the jobs, viewing jobs, altering a job, and removing a job.

Creating Procedures to Submit as Jobs

Before you can really get started, go ahead and enter and then execute the three procedures in Listings 19.12 through 19.14. After creating these procedures, you will be able to submit them as jobs to test the DBMS_JOB package. The procedure in Listing 19.12 displays "Hello World!" to the screen. Listing 19.13 writes "Hello World!" to a file and adds the current system time and date when the procedure executes. Listing 19.14 accesses the same file as Listing 19.13, adds "Hello Again for the Second Time!," and adds the current system time and date when the procedure executes.

INPUTListing 19.12. Displaying "Hello World!" to the screen.

1: CREATE OR REPLACE PROCEDURE HELLO AS
2: BEGIN
3:      DBMS_OUTPUT.PUT_LINE(`Hello World! ` ||
4:           TO_CHAR(SYSDATE,'MM-DD-YY HH:MI:SS AM'));
5: END;

INPUTListing 19.13. Writing "Hello World!" to a file.

 1: CREATE OR REPLACE PROCEDURE HELLOFLE IS
 2:
 3: --DECLARE
 4: -- Create a file handle of type UTL_FILE.FILE_TYPE
 5:      v_MyFileHandle UTL_FILE.FILE_TYPE;
 6: BEGIN
 7: -- Open the file to write.
 8:      v_MyFileHandle := UTL_FILE.FOPEN(`C:\','HELLO.TXT','a');
 9:      UTL_FILE.PUT_LINE(v_MyFileHandle,
10:           `Hello World! ` || TO_CHAR(SYSDATE,'MM-DD-YY HH:MI:SS AM'));
11:
12: -- Close the file handle which points to myout.txt
13:      UTL_FILE.FCLOSE(v_MyFileHandle);
14: EXCEPTION
15: -- Create Exception to simply display error code and message
16:      WHEN OTHERS THEN
17:           DBMS_OUTPUT.PUT_LINE
18:                (`ERROR ` || TO_CHAR(SQLCODE) || SQLERRM);
19:           NULL;
20: END;

INPUTListing 19.14. Another process accessing the same file.

 1: CREATE OR REPLACE PROCEDURE SHAREFLE IS
 2:
 3: --DECLARE
 4: -- Create a file handle of type UTL_FILE.FILE_TYPE
 5:      v_MyFileHandle UTL_FILE.FILE_TYPE;
 6: BEGIN
 7: -- Open the file to write.
 8:      v_MyFileHandle := UTL_FILE.FOPEN(`C:\','HELLO.TXT','a');
 9:      UTL_FILE.PUT_LINE(v_MyFileHandle,
10:           `Hello Again for the Second Time! ` ||
11:                TO_CHAR(SYSDATE,'MM-DD-YY HH:MI:SS AM'));
12: -- Close the file handle which points to myout.txt
13:      UTL_FILE.FCLOSE(v_MyFileHandle);
14: EXCEPTION
15: -- Create Exception to simply display error code and message
16:      WHEN OTHERS THEN
17:           DBMS_OUTPUT.PUT_LINE
18:                (`ERROR ` || TO_CHAR(SQLCODE) || SQLERRM);
19:           NULL;
20: END;

Submitting All Jobs to the Job Queue

You can submit all three jobs at once to the job queue with the code in Listing 19.15.

INPUTListing 19.15. Submitting all three jobs at once.

 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 to hello is: `
 8:           ||v_jobnum);
 9:      DBMS_JOB.SUBMIT(v_JobNum,'hellofle;',SYSDATE,
10:            `SYSDATE + (1/(24*60*60))');
11:      DBMS_OUTPUT.PUT_LINE(`Your Job Number assigned to hellofle is: `
12:           ||v_jobnum);
13:      DBMS_JOB.ISUBMIT(109,'sharefle;',SYSDATE,'SYSDATE +
14:             (1/(24*60*60))');
15:      DBMS_OUTPUT.PUT_LINE(`Your Job Number assigned to
16:            sharefle is: 109');
17: END;

The output should look similar to

OUTPUT

Your Job Number assigned to hello is: 24
Your Job Number assigned to hellofle is: 25
Your Job Number assigned to sharefle is: 109

Running All Three Jobs Immediately

Because I have no patience, let's submit all three jobs immediately in Listing 19.16. Before you execute the code, make sure that you have typed SET SERVEROUTPUT ON and pressed Enter at the SQL*Plus prompt.

INPUTListing 19.16. Submitting all three jobs at once.

1: BEGIN
2: --Make sure you enter the job numbers assigned for the first two jobs
3:      DBMS_JOB.RUN(24);
4:      DBMS_JOB.RUN(25);
5:      DBMS_JOB.RUN(109);
6: END;

Your output should look similar to

Hello World! 06-22-97 09:37:42 PM