CREATE ROLE my_aq_adm_role;

CREATE ROLE my_aq_user_role;

begin

       DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(privilege =>  'ENQUEUE_ANY',grantee => 'my_aq_user_role',admin_option => FALSE);
end;

begin
    DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(

    privilege => 'DEQUEUE_ANY',

    grantee => 'my_aq_user_role',
    admin_option => FALSE);
end;

create tablespace sp_foraq datafile 'aq_tbs.dbf' size 50 M;
---AQ Admin

CREATE USER aqadm IDENTIFIED BY aqadm DEFAULT TABLESPACE sp_foraq TEMPORARY TABLESPACE temp;

GRANT my_aq_adm_role TO aqadm;

GRANT connect, resource TO aqadm;

--AQ User
CREATE USER aquser IDENTIFIED BY aquser DEFAULT TABLESPACE sp_foraq TEMPORARY TABLESPACE temp;

grant connect,resource to aquser;

GRANT my_aq_user_role TO aquser;

GRANT EXECUTE ON dbms_aqadm to aqadm;
--change user.
CONNECT aqadm/aqadm;

CREATE TYPE queue_message_type AS OBJECT(

no NUmBER,

title VARCHAR2(30),

text VARCHAR2(2000)
);

GRANT EXECUTE ON queue_message_type TO my_aq_user_role;

GRANT EXECUTE ON DBMS_AQ TO my_aq_user_role;

begin
      DBMS_AQADM.create_queue_table(queue_table => 'queue_message_table',queue_payload_type => 'queue_message_type');
end;

begin
    
      DBMS_AQADM.create_queue(queue_name => 'message_queue',queue_table => 'queue_message_table');
end;

begin
      dbms_aqadm.start_queue(queue_name => 'message_queue');
end;
/* use to delete
begin
      dbms_aqadm.stop_queue(queue_name => 'message_queue');
      dbms_aqadm.drop_queue(queue_name => 'message_queue');
      dbms_aqadm.drop_queue_table(queue_table => 'queue_message_table');
end;

*/

CONNECT aquser/aquser;

--enqueue
DECLARE

    queue_options DBMS_AQ.enqueue_options_t;
   

    message_properties DBMS_AQ.message_properties_t;

    message_id
     RAW(16);
  
    my_message  aqadm.queue_message_type;
   

    BEGIN

    my_message := aqadm.queue_message_type(1,

    'This is a sample message',

    'This message has been posted on' || to_char(SYSDATE,'DD.MM.YYYY HH24:MI:SS'));

    DBMS_AQ.enqueue(queue_name => 'aqadm.message_queue',

    enqueue_options => queue_options,

    message_properties => message_properties,

    payload => my_message,

    msgid => message_id);

    COMMIT;

END;

--dequeue
DECLARE
queue_options DBMS_AQ.DEQUEUE_OPTIONS_T;

message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;

message_id RAW(2000);

my_message aqadm.queue_message_type;

BEGIN

DBMS_AQ.DEQUEUE(

queue_name => 'aqadm.message_queue',

dequeue_options => queue_options,

message_properties => message_properties,

payload => my_message,

msgid => message_id);

COMMIT;

DBMS_OUTPUT.PUT_LINE(

'Dequeued no: ' || my_message.no);

DBMS_OUTPUT.PUT_LINE(

'Dequeued title: ' || my_message.title);

DBMS_OUTPUT.PUT_LINE(

'Dequeued text: ' || my_message.text);

END;