Q Who can remove or alter a job?

A Only the owner of the job can alter or remove the job.

Q What can be done about a broken job?

A You could either use RUN to execute the job immediately or use the BROKEN procedure to reschedule the job. If needed, you could also delete the job with REMOVE.

Q When submitting a job, what punctuation should be used around regular procedure parameters? A Any parameters that normally are surrounded by a single quote (`) must be surrounded by two single quotes (`'); otherwise, the job will never execute.

Q What are the steps to send data to the pipe?

A Create the pipe (if private), then send the data to the message buffer with PACK_MESSAGE, and then finally, send the message buffer to the pipe with SEND_MESSAGE.

Q What are the steps to receive data from the pipe?

A Retrieve the message from the pipe into the message buffer with RETRIEVE_MESSAGE. Then you would use UNPACK_MESSAGE to retrieve all data from the message you just received.

Workshop

You can now test your knowledge of the DBMS_JOB and DBMS_PIPE packages, starting with a quick quiz. You can then practice with several short exercises. You can find the answers in Appendix A, "Answers."

Quiz

1. If the server goes down for two days (Monday to Tuesday), and a job with an execution of SYSDATE + 7 was supposed to run when the server went down (Tuesday), will the job always run on the original day of the week (run every Tuesday)?
2. Why must you use two single quotes around parameters specified in SUBMIT that used to take only one set of single quotes?
3. Can you alter someone else's job?
4. Is there any way to assign your own job number to a job?
5. What interval would you use to run a procedure every hour on the hour starting from the current date?
6. If you send a message to a full pipe, how long will you wait before you abort the process?
7. What is the maximum length of the message buffer?

Exercises

1. Write the code to submit a procedure called PAYDAY, where the parameters are FRIDAY, Bi_Monthly, and 6. The job should always execute at 4 a.m. on Saturday.
2. Write the code to view the JOB, last second run, and WHAT from USER_JOBS.
3. Write the code to submit job 200 once per day starting from SYSDATE for the procedure EASY, which has no parameters.
4. Write the code to alter job 200 to execute once per week for the interval (SYSDATE + 7).
5. Write the code to remove job 200.
6. From Listing 19.22, add the ELSIF procedures to handle RAW and ROWID datatypes.
7. From Listing 19.22, all data read into the public pipe should be inserted into a table called PIPETAB with the following schema: MyString VARCHAR2, MyDate DATE, and MyNum NUMBER. Make sure that you run the CREATE_PIPE procedure from Listing 19.21 so that you have data in the pipes for placing in the table.