tailieunhanh - Oracle Built−in Packages- P62

Oracle Built−in Packages- P62: 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 THEN IF SQLCODE -25228 Timeout queue is likely empty. THEN item NULL ELSE RAISE END IF END Most of the code is taken up with the basic steps necessary for any dequeue operation create the records specify that I want the action to be immediately visible and specify that AQ should not wait for messages to be queued. I then dequeue and if successful construct the string to be passed back. If the dequeue fails I trap for a specific error that indicates that the queue was empty ORA-025228 . In this case I set the item to NULL and return. Otherwise I reraise the same error. Notice that I call a function called priority_name as a part of my message passed back in dequeue. This function converts a priority number to a string or name as follows FUNCTION priority_name priority IN PLS_INTEGER RETURN VARCHAR2 IS retval VARCHAR2 30 BEGIN IF priority c_high THEN retval HIGH ELSIF priority c_low THEN retval LOW ELSIF priority c_medium THEN retval MEDIUM ELSE retval Priority TO_CHAR priority END IF RETURN retval END This function offers some consistency in how the priorities are named. This package contains the following initialization section BEGIN Create the queue table and queue as necessary. c_qtable message_type c_queue END priority This line of code is run the first time any of your code references a program in this package. This aq procedure see the earlier section Section Improving AQ Ease of Use makes sure that all elements of the priority queue infrastructure are ready to go. If the queue table and queue are already in place it will not do anything including raise any errors. Remember the comment I made about the big gaps between the priority numbers Take a look at the body for the priority package. If you add the header of the generic enqueue procedure to the specification this package will support not only high low and medium priorities but also any priority number you want to pass to the enqueue .