–Integrating Advanced Queuing in Oracle Forms 11g using event object

create user talat/talat

grant AQ_ADMINISTRATOR_ROLE, AQ_USER_ROLE to talat;

grant select_catalog_role to talat;

grant execute on dbms_aq to talat;

execute dbms_aqadm.grant_type_access(‘ talat ‘);

execute dbms_aqadm.grant_system_privilege(‘ENQUEUE_ANY’,’ talat ‘,FALSE);

execute dbms_aqadm.grant_system_privilege(‘DEQUEUE_ANY’,’ talat ‘,FALSE);

Type to hold the aqt_call object

create or replace type aqt_call as object(CALL_ID  VARCHAR2(300),CALLED_ADDRESS VARCHAR2(300),CALLED_ADDRESS_NAME VARCHAR2(300),CALLING_ADDRESS VARCHAR2(300),CALLING_ADDRESS_NAME VARCHAR2(300),event_type varchar2(1) )) ;

exec DBMS_AQADM.CREATE_QUEUE_TABLE (queue_table => ‘aqt_call_table’, queue_payload_type => ‘aqt_call’);

Now we will see what are the objects created.

SELECT queue_table,type,object_type,recipients FROM USER_QUEUE_TABLES;

The view will be created as a JOIN between AQT_QUEUE_TABLE and ALL_DEQUEUE_QUEUES view.

Creating Queues

exec DBMS_AQADM.CREATE_QUEUE (queue_name => ‘aqt_call_queue’, queue_table => ‘aqt_call_table’);

select name, queue_table, queue_type from user_queues;

Create subscriber

DECLARE 
subscriber sys.aq$_agent;
begin
subscriber := sys.aq$_agent( ‘admin’ , null, null);
dbms_aqadm.add_subscriber(
queue_name =>
‘aqt_call_queue’ , 
subscriber => subscriber);
end; 

Start the queue

Sql> exec DBMS_AQADM.START_QUEUE(‘aqt_queue’);

Enqueue of message

Web services send call parameters to call_info_enqueue procedure.

procedure call_info_Enqueue(p_CALL_ID in VARCHAR2,p_CALLED_ADDRESS in VARCHAR2,p_CALLED_ADDRESS_NAME in VARCHAR2,p_CALLING_ADDRESS in VARCHAR2,p_CALLING_ADDRESS_NAME in VARCHAR2,p_EVENT_TYPE VARCHAR2 ) as

  enqueue_options    dbms_aq.enqueue_options_t;

  message_properties       dbms_aq.message_properties_t;

  message_handle     RAW(16);

  message            aqt_call;

  message_id         NUMBER;

BEGIN

log_message(‘id:’||p_call_id||’, called address:’||p_called_address||’, called name:’||p_called_address_name||’, calling address:’||p_calling_address||’, calling name:’||p_calling_address_name||’,  event type:’||p_event_type );

Message := aqt_call(p_CALL_ID,p_CALLED_ADDRESS,p_CALLED_ADDRESS_NAME,p_CALLING_ADDRESS,p_CALLING_ADDRESS_NAME,p_EVENT_TYPE);–EVENT_TYPE:1== NEW CALL RECEIVED , 2==CALL ENDED

  enqueue_options.VISIBILITY := DBMS_AQ.ON_COMMIT;

  enqueue_options.SEQUENCE_DEVIATION := null;

  message_properties.EXPIRATION := DBMS_AQ.NEVER;

  DBMS_AQ.ENQUEUE (

    queue_name => ‘ aqt_call_queue ‘,

    enqueue_options => enqueue_options,

    message_properties => message_properties,

    payload => message,

    msgid => message_handle);

  COMMIT;

END call_info_Enqueue;

Handling events in Forms

1- Create new form

2- Create a new event in the new “Events” node in the Object Navigator in the Forms Builder.

3- Create a WHEN-EVENT-RAISED trigger

 declare
new_msg_str varchar2(4000);
begin
new_msg_str:=GET_EVENT_OBJECT_PROPERTY(‘EVENT2’,event_payload);
message(‘Payload is’||new_msg_str);
message(‘ ‘);
end;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s