Viewing Information About the Jobs

You can now view information about your jobs. Type the code in Listing 19.17.

INPUTListing 19.17. Viewing information on your jobs.

SELECT JOB,WHAT,LAST_SEC,INTERVAL from USER_JOBS;

Your output should look similar to

OUTPUT

JOB
--------
WHAT
------------------------------
LAST_SEC
--------
INTERVAL
------------------------------
       24
HELLO;
21:57:43
SYSDATE + (1/(24*60*60))

       25
hellofle;
21:57:43
SYSDATE + (1/(24*60*60))

      109
sharefle;
21:57:43
SYSDATE + (1/(24*60*60))

Altering the Job Running the Procedure HELLO

Listing 19.18 demonstrates modifying the Hello file to execute from once every minute to once per week from the current date. Remember, as background processes, you will not see "Hello World!" on the screen, but the job is still running and can be verified by running a query against USER_JOBS.

INPUTListing 19.18. Altering the HELLO process to run once per week.

1: BEGIN
2: -- Enter your job number assigned!
3:     DBMS_JOB.INTERVAL(24,'SYSDATE + 7');
4: END;

You can now see if the modification truly was successful by executing the query from Listing 19.17. Your output should look similar to

OUTPUT

JOB
--------
WHAT
------------------------------
LAST_SEC
--------
INTERVAL
------------------------------
       24
HELLO;
21:57:43
SYSDATE + 7

       25
hellofle;
21:57:43
SYSDATE + (1/(24*60*60))

      109
sharefle;
21:57:43
SYSDATE + (1/(24*60*60))

ANALYSIS Notice that the Hello file's time interval reflects SYSDATE + 7! You have successfully altered the interval.

Removing the Hello Job

Listing 19.19 demonstrates removing the Hello job.

INPUTListing 19.19. Removing the Hello job from the queue.

1: BEGIN
2: -- Enter your job number assigned!
3:     DBMS_JOB.REMOVE(24);
4: END;

To verify it, execute the code in Listing 19.20.

INPUTListing 19.20. Verifying the job removal.

SELECT JOB from USER_JOBS;

Your output should look similar to

OUTPUT

JOB
--------
      25
     109

ANALYSIS Notice that you now have only two jobs. By the way, if you look at the file C:\HELLO.TXT, it should look similar to

Hello World! 06-22-97 09:37:42 PM
Hello Again for the Second Time! 06-22-97 09:37:42 PM
Hello World! 06-22-97 09:38:35 PM
Hello Again for the Second Time! 06-22-97 09:38:35 PM
Hello World! 06-22-97 09:38:36 PM
Hello Again for the Second Time! 06-22-97 09:38:36 PM
Hello World! 06-22-97 09:39:36 PM
Hello Again for the Second Time! 06-22-97 09:39:36 PM
Hello World! 06-22-97 09:39:37 PM
Hello Again for the Second Time! 06-22-97 09:39:37 PM
Hello World! 06-22-97 09:40:38 PM
Hello Again for the Second Time! 06-22-97 09:40:38 PM
Hello World! 06-22-97 09:40:38 PM
Hello Again for the Second Time! 06-22-97 09:40:38 PM
Hello World! 06-22-97 09:41:38 PM
Hello Again for the Second Time! 06-22-97 09:41:38 PM
Hello World! 06-22-97 09:41:38 PM
Hello Again for the Second Time! 06-22-97 09:41:38 PM
Hello World! 06-22-97 09:42:39 PM
Hello Again for the Second Time! 06-22-97 09:42:39 PM
Hello World! 06-22-97 09:42:39 PM
Hello Again for the Second Time! 06-22-97 09:42:39 PM
Hello World! 06-22-97 09:43:40 PM
Hello Again for the Second Time! 06-22-97 09:43:40 PM
Hello World! 06-22-97 09:43:40 PM
Hello Again for the Second Time! 06-22-97 09:43:40 PM
Hello World! 06-22-97 09:44:40 PM

Your output might be a lot longer, depending on when you view this file. Notice that the two procedures HELLOFLE and SHAREFLE are appending to HELLO.TXT the phrase and the date and time every minute.

The DBMS_PIPE Package

The DBMS_PIPE package enables you to communicate between multiple sessions in the same database instance. You communicate by sending and receiving messages over the pipe. When you send a message, this is referred to as a writer. When you receive a message, this is referred to as a reader. Each pipe can have one or more writers and one or more readers. Pipes can be accessed by anyone who has access to the database instance, has access to the pipe, and can execute PL/SQL code.

One key feature of pipes is that they are asynchronous. You can access a pipe without having to use COMMIT. In addition, the ROLLBACK command does not work with pipes. This fact allows you to use pipes as a powerful debugging tool, as well as using pipes as an audit trail.

Public Versus Private Pipes

In PL/SQL version 2.2, private pipes were added. All earlier versions supported public pipes, which could be accessed by anyone in the database instance as long as the name of the pipe was known by the user and the user had EXECUTE access to DBMS_PIPE. Now you can create private pipes, which can be accessed only by

• DBAs
• The creator of the pipe
• Any stored procedure created by the owner

You would use private pipes in cases such as running three modules of a job simultaneously that need to share data without being interrupted. Another possibility is to use the private pipes as an audit trail or debugging tool. Public pipes would be used for projects to which everyone needs access.

Steps in Using Pipes

The following steps demonstrate the order in which pipes operate:

• If you are creating a private pipe, you first issue the CREATE_PIPE function.
• Whether the pipe is public or private, you send the data you want to transmit to a pipe to the message buffer by issuing the PACK_MESSAGE procedure. You are limited to 4096 bytes in the message buffer area.
• Before the buffer is overfilled, you should then issue the SEND_MESSAGE function to send the data to the pipe. If you are creating a public pipe, SEND_MESSAGE will create the pipe by default.
• When you are ready to receive data, you first call the RECEIVE_MESSAGE function. Each time the function is called, it will read the first unread message in the pipe, and dump it into the message buffer. Every time you want to extract the next message, you need to call RECEIVE_MESSAGE. If you need to know the datatype, because it could vary, you call the function NEXT_ITEM_TYPE.
• You can now use UNPACK_MESSAGE to interpret the message.