CREATE OR REPLACE TYPE order_msg_type AS OBJECT (
  order_id   NUMBER,
  customer   VARCHAR2(100),
  total_amt  NUMBER
);
/

BEGIN
  -- 2. Create the physical storage table for the queue
  DBMS_AQADM.CREATE_QUEUE_TABLE(
    queue_table        => 'order_queue_tbl',
    queue_payload_type => 'order_msg_type'
  );

  -- 3. Create the Queue referencing the table
  DBMS_AQADM.CREATE_QUEUE(
    queue_name  => 'order_queue',
    queue_table => 'order_queue_tbl'
  );

  -- 4. Start the queue so it can receive messages
  DBMS_AQADM.START_QUEUE(queue_name => 'order_queue');
END;
/

DECLARE
  v_enqueue_options    DBMS_AQ.enqueue_options_t;
  v_message_properties DBMS_AQ.message_properties_t;
  v_message_handle     RAW(16);
  v_payload            order_msg_type;
BEGIN
  -- Instantiate the payload
  v_payload := order_msg_type(1001, 'Alice', 250.00);

  DBMS_AQ.ENQUEUE(
    queue_name         => 'order_queue',
    enqueue_options    => v_enqueue_options,
    message_properties => v_message_properties,
    payload            => v_payload,
    msgid              => v_message_handle
  );
  
  -- The message is NOT visible to consumers until you commit!
  COMMIT;
END;
/

DECLARE
  v_dequeue_options    DBMS_AQ.dequeue_options_t;
  v_message_properties DBMS_AQ.message_properties_t;
  v_message_handle     RAW(16);
  v_payload            order_msg_type;
BEGIN
  -- Wait up to 10 seconds for a message
  v_dequeue_options.wait := 10; 

  DBMS_AQ.DEQUEUE(
    queue_name         => 'order_queue',
    dequeue_options    => v_dequeue_options,
    message_properties => v_message_properties,
    payload            => v_payload,
    msgid              => v_message_handle
  );

  -- Process the message!
  DBMS_OUTPUT.PUT_LINE('Processing Order: ' || v_payload.order_id);
  
  -- Removing the message from the queue requires a commit
  COMMIT;
END;
/