CREATE TABLE sales_ordering (
  orderid      NUMBER PRIMARY KEY,
  customerid   NUMBER NOT NULL,
  customername VARCHAR2(20) NOT NULL,
  qty          NUMBER NOT NULL,
  price        NUMBER NOT NULL
);

--AQ payload type
CREATE OR REPLACE TYPE sales_order_msg_t AS OBJECT (
  orderid      NUMBER,
  customerid   NUMBER,
  customername VARCHAR2(20),
  qty          NUMBER,
  price        NUMBER
);

--AQ queue with DLQ (this is the feature)
BEGIN
  DBMS_AQADM.CREATE_QUEUE_TABLE(
    queue_table        => 'SALES_QTAB',
    queue_payload_type => 'SALES_ORDER_MSG_T'
  );

  DBMS_AQADM.CREATE_QUEUE(
    queue_name  => 'SALES_Q',
    queue_table => 'SALES_QTAB',
    max_retries => 3,      -- after 3 failures → DLQ
    retry_delay => 0
  );

  DBMS_AQADM.START_QUEUE('SALES_Q');
END;
--
CREATE OR REPLACE TRIGGER trg_sales_to_aq
AFTER INSERT ON sales_ordering
FOR EACH ROW
DECLARE
  enqopt  DBMS_AQ.ENQUEUE_OPTIONS_T;
  msgprop DBMS_AQ.MESSAGE_PROPERTIES_T;
  msgid   RAW(16);
BEGIN
  DBMS_AQ.ENQUEUE(
    queue_name         => 'SALES_Q',
    enqueue_options    => enqopt,
    message_properties => msgprop,
    payload            => sales_order_msg_t(
                            :NEW.orderid,
                            :NEW.customerid,
                            :NEW.customername,
                            :NEW.qty,
                            :NEW.price
                          ),
    msgid              => msgid
  );
END;
-----
CREATE OR REPLACE NONEDITIONABLE PROCEDURE consume_sales AS
  deqopt  DBMS_AQ.DEQUEUE_OPTIONS_T;
  msgprop DBMS_AQ.MESSAGE_PROPERTIES_T;
  msg     sales_order_msg_t;
  msgid   RAW(16);
  v_amount NUMBER;
BEGIN
  deqopt.wait := DBMS_AQ.NO_WAIT;
  deqopt.dequeue_mode := DBMS_AQ.REMOVE;

  DBMS_AQ.DEQUEUE(
    queue_name         => 'SALES_Q',
    dequeue_options    => deqopt,
    message_properties => msgprop,
    payload            => msg,
    msgid              => msgid
  );

  IF msg.qty < 0 THEN
    RAISE_APPLICATION_ERROR(-20001, 'qty must be > 0');
  END IF;

  v_amount := msg.price / msg.qty;  -- qty=0 -> ORA-01476

  COMMIT;  -- success consumes message

EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -25228 THEN
      RETURN; -- no message available
    END IF;
    ROLLBACK; -- failure -> retry -> DLQ after max_retries
END;