Listing 19.22. continued

28:      END LOOP;
29: -- display all three types of data
30:      DBMS_OUTPUT.PUT_LINE(v_holdchar || ` ` || v_holddate || ` `
31:           || v_holdnum);
32: END;

If the code executed with no errors, then your output will look similar to

privateline1
privateline2
This is a text string 24-JUN-97 109

ANALYSIS This example shows how you can write different code that performs the same function. Because you know that the private pipe has only two lines in the message buffer, you can use two UNPACK_MESSAGE statements in a row to retrieve the information. Because only one variable is holding the data, you use DBMS_OUTPUT to display the information to the screen before it is overwritten.

You can now retrieve the information from the public pipe. However, I am assuming that you do not know what datatype is in the message buffer or how many items are in the buffer. (I do make an assumption improperly when displaying the final results with DBMS_OUTPUT because I display only one of each datatype because I sent to the pipe one of each datatype. Proper coding would correct this.) You first read the message from the pipe and store this into the message buffer. You then execute a continuous loop until no more data is found in the pipe (NEXT_ITEM_TYPE = 0). I use IF...ELSIF to assign the appropriate datatype to the appropriate variable—no guesswork here.

• If the return type is 6, it is of type NUMBER.
• If the return type is 9, it is of type VARCHAR2.
• If the return type is 12, it is of type DATE.
• If the return type is 0, EXIT because no more data!

The loop ends and prints to screen the three datatypes initially sent on the CREATE procedure.

Removing the Pipe
You can free up resources by removing the pipes. Enter and execute the code in Listing 19.23.

INPUTListing 19.23. Removing both pipes.

 1: DECLARE
 2:      v_stat NUMBER ;
 3: BEGIN
 4:      v_stat := dbms_pipe.remove_pipe(`myprivatepipe');
 5:      DBMS_OUTPUT.PUT_LINE(`The status for removing the
 6:           private pipe is: ` || v_stat);
 7:      v_stat := dbms_pipe.remove_pipe(`mypublicpipe');
 8:      DBMS_OUTPUT.PUT_LINE(`The status for removing the
 9:           public pipe is: `|| v_stat);
10: END;

Your output should look similar to

    The status for removing the private pipe is: 0
    The status for removing the public pipe is: 0

Other Pipe Functions and Procedures

Several other functions and procedures are available for use with the DBMS_PIPE package. These functions and procedures include PACK_MESSAGE_RAW, UNPACK_MESSAGE_RAW, PACK_MESSAGE_ROW_ID, UNPACK_MESSAGE_ROW_ID, RESET_BUFFER, PURGE, and UNIQUE_SESSION_NAME.

PACK_MESSAGE_RAW
When writing data to the message buffer, there was no provision to handle RAW data. The PACK_MESSAGE_RAW function enables you to process RAW data.

The Syntax for the PACK_MESSAGE_RAW Procedure
The format for the procedure is

PROCEDURE PACK_MESSAGE_RAW(data IN VARCHAR2);

You still have the limitation of 4096 bytes for the message buffer size; therefore, you cannot use the LONG RAW datatype.

UNPACK_MESSAGE_RAW
Because you can send the RAW datatype, in order to decode the RAW datatype from the message buffer, you will use UNPACK_MESSAGE_RAW.

The Syntax for the UNPACK_MESSAGE_RAW Procedure
The syntax for the procedure is

PROCEDURE UNPACK_MESSAGE_RAW(data OUT VARCHAR2);

PACK_MESSAGE_ROWID
You can send the ROWID datatype to the message buffer, which is ultimately sent to the pipe by using the PACK_MESSAGE_ROWID procedure.

The Syntax for the PACK_MESSAGE_ROWID Procedure
The syntax of the procedure is

PROCEDURE PACK_MESSAGE_ROWID(data IN VARCHAR2);

Again, don't forget the 4096-byte message buffer limitation.

UNPACK_MESSAGE_ROWID
In order to decode the information from the pipe, you would use the UNPACK_MESSAGE_ROWID procedure.

The Syntax for the UNPACK_MESSAGE_ROWID Procedure
The format for the UNPACK_MESSAGE_ROWID procedure is

PROCEDURE UNPACK_MESSAGE_ROWID(data OUT VARCHAR2);

RESET_BUFFER
If you ever need to clear the message buffer, such as when an exception is raised, you can do so with the RESET_BUFFER procedure. The format of the RESET_BUFFER procedure is as follows:

PROCEDURE RESET_BUFFER;

PURGE
PURGE enables you to remove all data in the pipe specified, which is useful if you need to clear the pipes before processing data to the pipes. It's also useful if an error occurs and you need to reset the pipes.

The Syntax for the PURGE Procedure
The format for the procedure is

PROCEDURE PURGE(name_of_pipe IN VARCHAR2);

UNIQUE_SESSION_NAME
If you are worried that the name of a pipe could exist, you could always assign the name of the pipe to the function UNIQUE_SESSION_NAME, which will provide a name for the pipe that is not used in Oracle. The format for the UNIQUE_SESSION_NAME function is as follows:

FUNCTION UNIQUE_SESSION_NAME RETURN VARCHAR2;

The function will return a unique name, which can be assigned to the name of the pipe with a length up to 30 bytes.

Other Uses for Pipes

Although you can use pipes for parallel processing in multiple processor environments, use pipes for an audit trail, or just use pipes to debug processes, you can also use third-generation languages to communicate with database procedures through pipes.

You can refer to the Oracle Server Applications Guide in the documentation for some examples of C code communicating with Oracle pipes.

Summary

Today you learned how to work with the DBMS_JOB package in Oracle. This package handles all jobs by using background processes to check for jobs to execute at a specific interval, which are placed in job queues. In order to submit a job for processing, you must still submit the job to the queue first. After a job is submitted, the owner can only change the parameters. The owner can also fix any of his broken jobs as well as remove only his own jobs from the queue. You can also import and export jobs from one database to the next. Always remember that the job number is unique, and any attempt to use the same job number will result in a failure of the job to execute.

Q&A

Q Can jobs be executed immediately without being sent to the job queue?

A No. All jobs must be submitted to a job queue, and then you can use DBMS_JOB.RUN to execute the job immediately. The job will then return to executing at its scheduled time interval.

Q What is the difference between SUBMIT and ISUBMIT?

A ISUBMIT enables you to assign your job numbers, provided that the job number is not being used in the system.

Q Which parameters can be altered in the job?

A All the parameters can be altered in a job. You can use CHANGE to alter all of them immediately or use specific procedures such as WHAT, NEXT_DATE, or INTERVAL.