AI Raod Map

enqueue and dequeue

 I can give you a simple working example of enqueue and dequeue with ANYDATAARRAY in Oracle SQL/PLSQL.

-- Queue Table to hold ANYDATA type messages

BEGIN

    DBMS_AQADM.CREATE_QUEUE_TABLE (

        queue_table        => 'test_queue_tab',

        queue_payload_type => 'SYS.ANYDATA',

        multiple_consumers => FALSE

    );

    DBMS_AQADM.CREATE_QUEUE (

        queue_name  => 'test_queue',

        queue_table => 'test_queue_tab'

    );

    DBMS_AQADM.START_QUEUE (

        queue_name => 'test_queue'

    );

END;

/

--2. Declare a Payload Array in PL/SQL

DECLARE

    lv_payload_array  SYS.ANYDATAARRAY := SYS.ANYDATAARRAY(); -- initialize empty array

BEGIN

    -- Extend and add messages to the array

    lv_payload_array.EXTEND(2);

    

    -- Add 2 messages of type VARCHAR2

    lv_payload_array(1) := SYS.ANYDATA.ConvertVarchar2('Hello');

    lv_payload_array(2) := SYS.ANYDATA.ConvertVarchar2('World');

    -- Enqueue each message

    FOR i IN 1..lv_payload_array.COUNT LOOP

        DBMS_AQ.ENQUEUE(

            queue_name         => 'test_queue',

            enqueue_options    => DBMS_AQ.ENQUEUE_OPTIONS_T(),

            message_properties => DBMS_AQ.MESSAGE_PROPERTIES_T(),

            payload            => lv_payload_array(i),

            msgid              => NULL

        );

    END LOOP;

    COMMIT;

END;

/

--Dequeue Messages

DECLARE

    lv_dequeue_options    DBMS_AQ.DEQUEUE_OPTIONS_T;

    lv_message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;

    lv_msgid              RAW(16);

    lv_payload            SYS.ANYDATA;

    lv_varchar            VARCHAR2(100);

BEGIN

    lv_dequeue_options.wait := DBMS_AQ.NO_WAIT;

    LOOP

        BEGIN

            DBMS_AQ.DEQUEUE(

                queue_name         => 'test_queue',

                dequeue_options    => lv_dequeue_options,

                message_properties => lv_message_properties,

                payload            => lv_payload,

                msgid              => lv_msgid

            );

            -- Extract VARCHAR2 value from ANYDATA

            IF lv_payload.GetVarchar2(lv_varchar) = DBMS_TYPES.SUCCESS THEN

                DBMS_OUTPUT.PUT_LINE('Dequeued: ' || lv_varchar);

            END IF;

        EXCEPTION

            WHEN NO_DATA_FOUND THEN

                EXIT; -- No more messages

        END;

    END LOOP;

END;

/

Output

Dequeued: Hello

Dequeued: World

This is the simplest enqueue/dequeue example using ANYDATAARRAY in Oracle AQ.

Collection Varray TABLE() in a SELECT statement

 

create object types, nested tables or VARRAY collections, and then use TABLE() in a SELECT statement

-- Object type representing an employee

CREATE OR REPLACE TYPE emp_obj AS OBJECT (

    emp_id   NUMBER,

    emp_name VARCHAR2(50)

);

/

-- Nested table of emp_obj

CREATE OR REPLACE TYPE emp_tab AS TABLE OF emp_obj;

/

-- Table storing departments with employees as a collection

CREATE TABLE dept_with_emps (

    dept_id   NUMBER,

    dept_name VARCHAR2(50),

    employees emp_tab

) NESTED TABLE employees STORE AS employees_nt;

/

INSERT INTO dept_with_emps VALUES (

    10,

    'HR',

    emp_tab(

        emp_obj(101, 'Alice'),

        emp_obj(102, 'Bob')

    )

);

INSERT INTO dept_with_emps VALUES (

    20,

    'IT',

    emp_tab(

        emp_obj(201, 'Charlie'),

        emp_obj(202, 'David'),

        emp_obj(203, 'Eve')

    )

);

COMMIT;

/

-- Select all employees from the nested collection

SELECT d.dept_id,

       d.dept_name,

       e.emp_id,

       e.emp_name

FROM dept_with_emps d,

     TABLE(d.employees) e;

/

---**************** Object Type **********----

DROP TYPE emp_tab FORCE;

DROP TYPE emp_obj FORCE;

-- Step 1: Create an Object Type

CREATE OR REPLACE TYPE emp_obj AS OBJECT (

    emp_id   NUMBER,

    emp_name VARCHAR2(50)

);

/

-- Step 2: Create a Nested Table Type of emp_obj

CREATE OR REPLACE TYPE emp_tab AS TABLE OF emp_obj;

/

-- Step 3: Use the collection in SQL with TABLE() function

SELECT *

FROM TABLE(

         emp_tab(

            emp_obj(101, 'Alice'),

            emp_obj(102, 'Bob'),

            emp_obj(103, 'Charlie')

         )

     );

/

DECLARE

    v_emps emp_tab := emp_tab(

                        emp_obj(201, 'David'),

                        emp_obj(202, 'Eve')

                      );

BEGIN

    FOR rec IN (SELECT * FROM TABLE(v_emps)) LOOP

        DBMS_OUTPUT.PUT_LINE('ID: ' || rec.emp_id || ', Name: ' || rec.emp_name);

    END LOOP;

END;

/