Oracle Integration tutorials For Beginner

 

Oracle Integration

 

1-     What is integration?

When there is a requirement to share/transfer/exchange a data between two different application/system which is having same database (Oracle database) then this can be done with help of integration. i.e To connect two different application integration is used.

 

2-     Different Type Of Integration ?

-        Oracle Advance Queue (AQ/MQ) based integration: It is purely based on oracle plsql & used in oracle database only.

-        File Integration:  This can be done by sharing the data in different files format like (DOC/TXT/EXCEL/CSV/)  , Files can be sharee on server of two system by multiple programing language. (in oracle it can be done using Unix Schell Script/ UTL File Packages which is PLSQL based.)

 

3-     Oracle Advance Queue (AQ/MQ):

-        This is oracle PLSQL based Integration which is based on advance Queuing concept.

-        In this method data can be shared with the help of oracle QUEUE & QUEUE Tables.

-        Data shared by using this method is called PAYLOAD whose data type is XML TYPE.

-        This is more Secure method of integration.

4-     How Oracle Queueing works?

It contains below component:

- Gateway

- QUEUE Table

- QUEUE

- Foreign QUEUE

-SUBSCRIBER

-Propogation Scheduler.

 

Steps:

Note: Before creating any integration, it is mandatory to open the firewall port between two system. If Firewall port/Connection doesn’t open then Queues will go in error.

 -        First Create Database Link on oracle DB to enable connection between two           system.

-       Then CREATE_QUEUE_TABLE In source & destination system.

-        Then Create CREATE_QUEUE on queue table.

-        Then START_QUEUE

-        Then Register_Foreign_Queue

-        Then Add_Subscriber

-        Then Schedule_Propagation

-        Then Enable_Propagation_Schedule

-------------------------------------------------------------------------------------------------------------------

 PLSQL Code to create/Configure Queue (This is only for reference No need to Remember Code in interview.):

Configure LINK:

 

DECLARE

   v_options   SYS.mgw_properties;

   v_prop      SYS.mgw_mqseries_properties;

BEGIN

   v_prop := SYS.mgw_mqseries_properties.construct ();

   v_prop.max_connections := 10;

   v_prop.queue_manager := 'QMEIBS1';

   v_prop.hostname := '10.87.205.70';         

   v_prop.channel := 'QMEIGS1.CRM.SVRCONN';

   v_prop.port := '7111';

   v_prop.outbound_log_queue :=  'ENTCRM.LOG.OUT';

   v_prop.inbound_log_queue :=  'ENTCRM.LOG.IN';

   DBMS_MGWADM.create_msgsystem_link

                       (linkname        => BAJAJAL_TO_BAJABFIN_LINK', -- link name

                        properties      => v_prop,               -- mqseries driver properties

                        options         => v_options             -- options

                        );                                       

END;

 

----------------------------------------------------

--unconfigure Link

begin

dbms_mgwadm.remove_msgsystem_link (linkname   => 'CRM_ENT_TO_EAI_LINK');

end;

/

1-     begin DBMS_AQADM.CREATE_QUEUE_TABLE (queue_table =>'XXBAJAJALIANZ_INSURANCE_INTBL', queue_payload_type => 'SYS.MGW_BASIC_MSG_T', multiple_consumers => FALSE ); end;

 

2-     begin DBMS_AQADM.CREATE_QUEUE (queue_name => 'XXBAJAJALIANZ_INSURANCE_INQ', queue_table =>'XXBAJAJALIANZ_INSURANCE_INTBL'); end;

 

3-     begin DBMS_AQADM.START_QUEUE (queue_name => 'XXBAJAJALIANZ_INSURANCE_INQ'); end

 

4-     begin dbms_mgwadm.register_foreign_queue(name =>'XXBAJAJALIANZ_INSURANCE_INFQ', linkname =>'CRM_ENT_TO_EAI_LINK',provider_queue =>'DOX.CMCOLLSRV.ECRM.ESB.RPO.02',domain => dbms_mgwadm.DOMAIN_QUEUE); end;

 

5-     begin dbms_mgwadm.add_subscriber(subscriber_id =>'XXBAJAJALIANZ_INSURANCE_INSUB', propagation_type => dbms_mgwadm.inbound_propagation, queue_name =>'XXBAJAJALIANZ_INSURANCE_INFQ@CRM_ENT_TO_EAI_LINK',destination =>'apps.XXBAJAJALIANZ_INSURANCE_INQ');end;

 

6-     begin dbms_mgwadm.schedule_propagation(schedule_id =>'XXBAJAJALIANZ_INSURANCE_INSCH' ,propagation_type =>dbms_mgwadm.inbound_propagation,source =>'XXBAJAJALIANZ_INSURANCE_INFQ@CRM_ENT_TO_EAI_LINK',destination =>'apps.XXBAJAJALIANZ_INSURANCE_INQ' , latency => 2); end;

 

7-     begin dbms_mgwadm.enable_propagation_schedule('XXBAJAJALIANZ_INSURANCE_INSCH'); end;

 

-------------------------------------------------------------------------------------------------------------------