Oracle Built−in Packages- P65: 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 Filename on companion disk FUNCTION onsale product_in IN VARCHAR2 RETURN BOOLEAN IS queueopts msgprops obj sale_t BEGIN Take immediate effect no commit required. Reset the navigation location to the first message. Dequeue in BROWSE. You never dequeue destructively. You let the Queue Monitor automatically expire messages and move them to the exception queue. Retrieve only the message for this product. UPPER product_in Locate the entry by correlation identifier and return the object. c_queue queueopts msgprops obj g_msgid RETURN IS NOT NULL EXCEPTION WHEN THEN RETURN FALSE END This is a standard nondestructive dequeue operation. Notice that I use the correlation identifier to make sure that I attempt to dequeue a message only for this particular product. I also set navigation to first message to make sure I get the first message in enqueue time for the product. In this case I do not return any of the sale information. Instead I return TRUE if I found a non-NULL product in the dequeued object. If I timeout trying to retrieve a message I return FALSE. Of course I need to be able to put a product on sale. I do that with the procedure. Filename on companion disk PROCEDURE mark_for_sale product_in IN VARCHAR2 price_in IN NUMBER starts_on IN DATE ends_on IN DATE IS queueopts msgprops sale_obj sale_t BEGIN Calculate the delay number of seconds and the expiration in same terms GREATEST 0 starts_on - SYSDATE 24 60 60 GREATEST 0 ends_on - starts_on 24 60 60 Delayed for seconds.
