Oracle Built−in Packages- P61: Ah, for the good old days of Version of PL /SQL! Life was so simple then. No stored procedures or functions and certainly no packages. You had your set of built−in functions, like SUBSTR and TO_DATE. You had the IF statement and various kinds of loops. With these tools at hand, you built your batch−processing scripts for execution in SQL*Plus, and you coded your triggers in SQL*Forms , and you went home at night content with a good day's work done. | Appendix A What s on the Companion Disk Submit the event handler as a job to the job queue Enqueue messages Improving AQ Ease of Use Let s start by constructing a package to make it easier to work with AQ objects. I am always looking for ways to shortcut steps I must perform to get things done. The complexity of AQ along with all of the different records and structures begs for a wrapper of code to perform common steps more easily. I describe the program elements defined in the aq package later. To save a few trees I will leave the reader to examine the package body to see how I constructed these programs. In most cases they are very straightforward. First off I define two subtypes so that you can declare variables using names instead of hard-coded declarations like RAW 16 . These subtypes are as follows v_msgid RAW 16 SUBTYPE msgid_type IS v_msgid TYPE v_name VARCHAR2 49 SUBTYPE name_type IS v_name TYPE I also predefined two common exceptions so that you can trap these by name through a WHEN OTHERS clause and a hard-coding of the error number see the file for an example of using this named exception dequeue_timeout EXCEPTION PRAGMA EXCEPTION_INIT dequeue_timeout -25228 dequeue_disabled EXCEPTION PRAGMA EXCEPTION_INIT dequeue_disabled -25226 Now let s run through the different procedures and functions in the packages. The procedure combines the create table queue create queue and start queue steps into a single procedure call PROCEDURE qtable IN VARCHAR2 payload_type IN VARCHAR2 qname IN VARCHAR2 prioritize IN VARCHAR2 NULL If the queue table already exists it is not created. You can also provide a prioritization string if you want to override the default. The procedure has the same interface as but the default value for the prioritize parameter is the most common nonstandard string order by the priority number and within the same priority number by
