--===========================================================================
-- Copywright 2002:  chet west & joel craiinshaw 
--=============================================================================
-- DESCRIPTION
-- 	Simple Example of Advanced Queueing
-- NOTES
--  I had problems with the TYPE that is ccreated.  
--  Had to recreate it using TOAD.  
--  I am probably just missing something ssimple. 
--
-- REQUIREMENTS
--  Must have Advanced Queueing Installed  (Enterprise Edition of DB)
--
--=============================================================================
-- MODIFICATION HISTORY
-- Person      Date       Comments
-- ---------   ---------- --------------------------------------------
-- Chet West   09/12/2002 Initial Creationn
--=============================================================================

DROP TYPE MESSAGE_TYPE;
--
--- Create the Message Type
--
CREATE TYPE MESSAGE_TYPE AS OBJECT
(
  FOR_WHO VARCHAR2(80),
  SUBJECT VARCHAR2(80),
  BODY_TEXT VARCHAR2(4000)
)

BEGIN
   DBMS_AQADM.drop_queue_table (
      queue_table =>         'msg_qtab'
   );
END;
--
--- Create the Queue Table for the Messagee Type
--
BEGIN
   DBMS_AQADM.create_queue_table (
      queue_table =>         'msg_qtab'
     ,COMMENT =>             'Message queue table'
     ,queue_payload_type =>  'SYSTEM.message_type'
     ,compatible =>          '8.1'
   );
END;

BEGIN
   DBMS_AQADM.drop_queue (
      queue_name =>          'messageque'
   );
END;
--
--- Create the Queue for the Queue Table
--
BEGIN
   DBMS_AQADM.create_queue (
      queue_name =>          'messageque'
     ,queue_table =>         'msg_qtab'
   );
END;
--
--- Start the Queue
--
BEGIN
   dbms_aqadm.start_queue(queue_name => 'messageque'); 
END;

--
--- Sample Code Follows
--

CREATE OR REPLACE PROCEDURE SYSTEM.message_enq (
   for_who        IN   VARCHAR2
  ,subject        in   varchar2
  ,message_text   in   varchar2
) as
--============================================================================
-- Copywright 2002:  joel crainshaw & chett  west
--=============================================================================
-- DESCRIPTION
-- 	Puts a message into the queue
--=============================================================================
-- MODIFICATION HISTORY
-- Person      Date       Comments
-- ---------   ---------- --------------------------------------------
-- Chet West   09/12/2002 Initial Creationn
--=============================================================================
   message_data message_type;
   enqopt DBMS_AQ.enqueue_options_t;
   msgprop DBMS_AQ.message_properties_t;
   enq_msgid RAW (16);
BEGIN
   message_data := message_type(for_who, subject, message_text);
   DBMS_AQ.enqueue ('messageque', enqopt, msgprop, message_data, enq_msgid);
END;
/

CREATE OR REPLACE PROCEDURE message_deq AS
--============================================================================
-- Copywright 2002:  joel crainshaw & chett  west
--=============================================================================
-- DESCRIPTION
-- 	Gets a message from the queue and outpputs it
--=============================================================================
-- MODIFICATION HISTORY
-- Person      Date       Comments
-- ---------   ---------- --------------------------------------------
-- Chet West   09/12/2002 Initial Creationn
--=============================================================================
   message_data MESSAGE_TYPE;
   deq_msgid RAW (16);
   deq_opt DBMS_AQ.dequeue_options_t;
   msg_prop DBMS_AQ.message_properties_t;
   qname VARCHAR2 (30);
BEGIN
   qname := 'messageque';

   DBMS_AQ.dequeue (
            queue_name =>          qname
           ,dequeue_options =>     deq_opt
           ,message_properties =>  msg_prop
           ,payload =>             message_data
           ,msgid =>               deq_msgid
         );
         DBMS_OUTPUT.put_line ('To:' || message_data.for_who);
         DBMS_OUTPUT.put_line ('Subject:' || message_data.subject);
         DBMS_OUTPUT.put_line ('Message:' || message_data.body_text);
END;
/

--
--- Use the Sample Code
--
BEGIN
   message_enq (
      for_who =>             'CHET WEST'
     ,subject =>             'Test Message'
     ,MESSAGE_TEXT =>        'This is a test message for Oracle AQ.'
   );
   COMMIT;
   message_deq;
   COMMIT;
END;

--
--- Sample DBMS_OUTPUT Follows...
--
To:CHET WEST
Subject:Test Message
Message:This is a test message for Oracle AQ.