Plsql NOTE--------Advanced Queue Demo

1. Sysdba the user to assign permissions

# Sysprivilege.sql

Prompt----Connect to sysdbaset serveroutput on;prompt----Create user aq and Aq_userdrop user aq Cascade;drop user Aq_u  Ser cascade;create user aq identified by Aq;create user Aq_user identified by aq_user;prompt----Grant SYS privilegegrant Connect,resource to Aq,aq_user;grant aq_administrator_role,unlimited tablespace to aq;grant execute on Dbms_aq to Aq;gran T execute on Dbms_aqadm to aq;grant Select any dictionary to aq;grant execute on Dbms_aq to aq_user;grant execute on DBMS_ Aqadm to aq_user;prompt----Grant AQ queue admin privilegebegindbms_aqadm.grant_system_privilege (' enqueue_any ', ' aq ', False);d Bms_aqadm.grant_system_privilege (' Dequeue_any ', ' aq ', false); End;/show errors;

2. Advanced Queue Administrator under AQ user

# Aqinstall.sql

Prompt----Connect to aqset serveroutput on;prompt----Construct message typecreate or replace type Aq_message force as Object (Request Clob,id integer);/show errors;grant execute on aq_message to Aq_user; Prompt----Drop queue and queue tablebegindbms_aqadm. Stop_queue (queue_name = ' demo_queue ');D bms_aqadm. Drop_queue (queue_name = ' demo_queue ');D bms_aqadm. Drop_queue_table (queue_table = ' demo_queue_table '); end;/show errors;prompt----Create QUEUE TABLE and Queuebegindbms_aqadm. Create_queue_table (queue_table = ' demo_queue_table ', Queue_payload_type = ' aq_message ', multiple_consumers = > TRUE);D bms_aqadm. Create_queue (queue_name = ' Demo_queue ', queue_table = ' demo_queue_table '); End;/show errors;prompt----Grant Aq_user queue privilegebegindbms_aqadm. Grant_queue_privilege (' All ', ' demo_queue ', ' Aq_user ', true); End;/show errors;prompt----Add QUEUE Subscriberbegindbms _aqadm. Add_subscriber (queue_name = ' demo_queue ', subscriber = SYS. Aq$_agent (' Demo_sub ', NUll,null) end;/show errors;prompt----subscriber Registerbegindbms_aq. REGISTER (SYS. Aq$_reg_info_list (SYS. Aq$_reg_info (' Demo_queue:demo_sub ', Dbms_aq. Namespace_aq, ' Plsql://demo_queue_callback_procedure ', Hextoraw (' FF ')), 1); End;/show errors;prompt----Add queue subscriber_1begindbms_aqadm. Add_subscriber (queue_name = ' demo_queue ', subscriber = SYS. Aq$_agent (' demo_sub_1 ', null,null)); end;/show errors;prompt----subscriber_1 Registerbegindbms_aq. REGISTER (SYS. Aq$_reg_info_list (SYS. Aq$_reg_info (' demo_queue:demo_sub_1 ', Dbms_aq. Namespace_aq, ' Plsql://demo_queue_call_procedure ', Hextoraw (' FF ')), 1); End;/show errors;prompt----Start Queuebegindbms_aqadm. Start_queue (queue_name = ' demo_queue ') end;/show errors;prompt----Subscriber Countselect count (*) from Aq$demo_ queue_table_s;

3. Service-side processing by AQ users

# Plsqlcallback.sql

Set serveroutput on; Create TABLE demo_queue_message_table (message VARCHAR2 (4000), id integer), create or replace procedure SendMessage ( Message in Clob,id on Integer, Subcriber in varchar2) isr_enqueue_options Dbms_aq. Enqueue_options_t;r_message_properties Dbms_aq. Message_properties_t;v_message_handle RAW (+); O_payload aq.aq_message;rcpt_list Dbms_aq.aq$_recipient_list_t;l_ Message clob;beginl_message: = message| | To_clob (To_char (Systimestamp, ' dd-mon-yyyy HH24:MI:SS. FF3 ') | | '} '); O  _payload: = Aq.aq_message (L_message,id); Rcpt_list (0): = sys.aq$_agent (subcriber, NULL, NULL); R_message_properties.recipient_list: = rcpt_list;--R_message_ Properties.delay: = 10;dbms_aq. ENQUEUE (queue_name = ' Aq.demo_queue ', enqueue_options = R_enqueue_options,message_properties = R_message_ Properties,payload = O_payload,msgid = V_message_handle); Commit;exception when others thendbms_output.put_line (SQLERRM);d Bms_output.put_line (dbms_utility.format_error_ BackTrace); End;/show Errors;create orReplace function Api_func (request in CLOB) return CLOB isl_response clob;beginl_response: = To_clob (' Send to client{'); ret Urn L_response;end;/show errors; CREATE or replace PROCEDURE demo_queue_callback_procedure (context Raw,reginfo SYS. AQ$_REG_INFO,DESCR SYS. Aq$_descriptor,payload raw,payloadl number) Asr_dequeue_options Dbms_aq. Dequeue_options_t;r_message_properties Dbms_aq. Message_properties_t;v_message_handle RAW (+); o_payload aq.aq_message;i_payload aq.aq_message;i_message Clob; Response clob;l_id Integer; BEGIN R_dequeue_options.msgid: = descr.msg_id;r_dequeue_options.consumer_name: = Descr.consumer_name; Dbms_aq. DEQUEUE (queue_name = descr.queue_name,dequeue_options = R_dequeue_options,message_properties = R_message _properties,payload = O_payload,msgid = V_message_handle); I_message: = To_clob (' message [' | | o_payload.request || '] ' | | ' dequeued at [' | | To_char (Systimestamp, ' dd-mon-yyyy HH24:MI:SS. FF3 ') | | '] '); INSERT into demo_queue_message_table (message,id)VALUES (To_char (i_message), o_payload.id); Response: = Api_func (I_message); l_id: = O_payload.id;sendmessage (Response, l_id, ' demo_sub_1 '); Commit;exception when others thendbms_output.put_line (SQLERRM);d Bms_output.put_line (dbms_utility.format_error_ BackTrace); End;/show errors;

4. Client processing under AQ user

# Plsqlcall.sql

CREATE TABLE demo_queue_message_receive (Message VARCHAR2 (4000), id integer); CREATE PROCEDURE demo_queue_call_procedure (context Raw,reginfo SYS. AQ$_REG_INFO,DESCR SYS. Aq$_descriptor,payload raw,payloadl number) Asr_dequeue_options Dbms_aq. Dequeue_options_t;r_message_properties Dbms_aq. Message_properties_t;v_message_handle RAW (+); o_payload aq.aq_message;i_message Clob; BEGIN R_dequeue_options.msgid: = descr.msg_id;r_dequeue_options.consumer_name: = Descr.consumer_name; Dbms_aq. DEQUEUE (queue_name = descr.queue_name,dequeue_options = R_dequeue_options,message_properties = R_message _properties,payload = O_payload,msgid = V_message_handle); I_message: = To_clob (' message [' | | o_payload.request || '] ' | | ' dequeued at [' | | To_char (Systimestamp, ' dd-mon-yyyy HH24:MI:SS. FF3 ') | | INSERT into Demo_queue_message_receive (message,id) VALUES (To_char (i_message), o_payload.id); Commit;exception when others thendbms_output.put_line (SQLERRM);d Bms_output.put_line (dbms_utility.formAt_error_backtrace); End;/show errors;

5. Message Queue (client)

# Subcriberenqueue.sql

Set Serveroutput on;declarer_enqueue_options Dbms_aq. Enqueue_options_t;r_message_properties Dbms_aq. Message_properties_t;v_message_handle RAW (+); O_payload aq.aq_message;message clob;rcpt_list dbms_aq.aq$_recipient _list_t; Beginmessage: = To_clob (' Send to server{' | | To_char (Systimestamp, ' dd-mon-yyyy HH24:MI:SS. FF3 ' | | '} '); O_payload: = Aq.aq_message (message,1);  Rcpt_list (0): = sys.aq$_agent (' demo_sub ', null, NULL); R_message_properties.recipient_list: = Rcpt_list;--r_message_ Properties.delay: = 10;dbms_aq. ENQUEUE (queue_name = ' Aq.demo_queue ', enqueue_options = R_enqueue_options,message_properties = R_message_ Properties,payload = O_payload,msgid = V_message_handle); Commit;exception when others thendbms_output.put_line (SQLERRM);d Bms_output.put_line (dbms_utility.format_error_ BackTrace); End;/show errors;

6. Results Check

# Checkresult.sql

Set Serveroutput on;prompt----message Detail Select Enq_time, Consumer_name, user_data from Aq$demo_queue_table;prompt- ---server receive Messagesselect message from demo_queue_message_table where ID =1;prompt----client receive Messagessel ECT message from demo_queue_message_receive where id = 1;

7. Installation Scripts && Instruction

#!/bin/bashsysname=syssyspwd=111111username=aquserpwd=aqport=1522username1=aq_useruserpwd1=aq_usersqlplus $ Sysname/[email protected]: $port as Sysdba <<-eofset serveroutput on SIZE 3000@sysprivilege.sqleofsqlplus $ Username/[email protected]: $port/xe <<-eofset serveroutput on SIZE 3000@aqinstall.sqleofsqlplus $username/[ Email protected]: $port/xe <<-eofset serveroutput on SIZE 3000@plsqlcallback.sqleofsqlplus $username/[email Protected]: $port/xe <<-eofset serveroutput on SIZE 3000@plsqlcall.sqleof

++++++++++++++++++++++ instruction ++++++++++++++++++++

1. Execute installaq.sh to install AQ environment;
2. Connect to user ' Aq_user ' using password ' aq_user ';
3. Execute subcriberenqueue.sql to send message;
4. Connect to user ' aq ' using password ' aq ';
5. Execute checkresult.sql to print the messages receive by server and receive by client.


The result is as below:

MESSAGE
--------------------------------------------------------------------------------
Message [Send to server{15-apr-2016 18:08:20.548}] dequeued at [15-apr-2016 18:0
8:20.58]


----Client Receive messages

MESSAGE
--------------------------------------------------------------------------------
Message [Send to client{15-apr-2016 18:08:20.581}] dequeued at [15-apr-2016 18:0
8:20.589]

Plsql NOTE--------Advanced Queue Demo