...

This data will be further monitored as defined in the next section - Configuring DB Component to monitor the data inserted into Oracle Database.

Perform the following actions to insert data in DB:

1. Open Oracle Application Express in browseryour preferred browser.
2. Provide the workspace, username and password details and click Login.
   
   Image Modified
   Figure 1: Logging into Oracle Application Express
   
   
3. Navigate to SQL Workshop > ObjectBrowser.
4. Select the table into which data is to be inserted. In this case, table EMPLOYEE with columns EMPID, EMPNAME and EMPDEPARTMENT.
5. Select Data tab and click Insert Row.
   
   Image Modified
   Figure 2: Insert Rows in Oracle DB
   
   
6. Add new row details in EMPLOYEE table and click Create.
   
   Image Modified
   Figure 3: Insert data in EMPLOYEE table.

...

1. Configure a DB component to monitor data that is inserted using Oracle Application Express.
2. Add an Event Process with name 'DB_Replication_Eg'. Drag the DB component from DB category in the Service Palette to the Fiorano Orchestrator.
   
   Image Modified
   Figure 4: Adding DB component to Fiorano Orchestrator
   
   
3. Rename the DB component from 'DB1' to 'DB_MonitorDataInOracle' to differentiate it from the DB component that will be configured later.
4. Double-click the DB component in the Orchestrator to open Configuration Property Sheet (CPS). Click the ellipsis button against Database Conifguration to open DbConfigurations panel.
   
   Image Modified
   Figure 5: DB Component CPS
   
   
5. Provide the following details in the DBConfigurations Panel and click OK.
   1. Database: Oracle (Choose the database to be connected to; 'Oracle' in this example)
   2. Driver: oracle.jdbc.driver.OracleDriver (Retain the default value)
   3. URL: jdbc:oracle:thin:@<MachineIP>:xe (machine IP in which the Oracle database is installed and 'xe' is the database name).
   4. User name: User name to connect to the database.
   5. Password: Password for the above user name.
   6. Named Configuration: OracleNamedConfig (Provide any value here to save the database configuration as Named Configuration. 'OracleDBNamedConfig' is the name used in this example).
      
      Image Modified
      Figure 6: Database configuration in DbConfigurations Panel
      
      
6. Click the Test button in Managed Connection Factory panel to see if the database has been configured correctly.
   
   Image Modified
   Figure 7: Testing Database configuration in Managed Connection Factory Panel
   
   
7. If the configuration is valid, it gets displayed in Test Connection Configuration pop-up.
   
   Image Modified
   Figure 8: Testing Database Configuration
   
   
8. Once the database has been configured properly, click Next in the Managed Connection Factory panel to navigate to Interaction Configurations panel.
   
   Image Modified
   Figure 9: Moving to Interaction Configurations Panel
   
   
9. Click SQL Configuration ellipsis button to configure 'Monitor Table' in SQL Configuration Wizard panel.
   
   Image Modified
   Figure 10: SQL Configuration in Interaction Configurations Panel
   
   
10. Click the Add button and select Monitor Table option from the menu.
    
    Image Modified
    Figure 11: Selecting 'Monitor Table' in SQL Configuration Panel
11. In SQL Creation Wizard, check Insert under Monitor Table for: and add a table that is to be monitored for Insert operation by clicking the ellipsis button against Monitor Table property.
    
    Image Modified
    Figure 12: SQL Creation Wizard
    
    
12. In Table Selection Dialog, click the Fetch Schemas button which fetches all the schemas in the database into Schema drop-down. Select a schema and then click Refresh Tables button to fetch/refresh the table list for the selected schema. Select the table from the list for which Monitoring has to be configured and click OK.
    
    Image Modified
    Figure 13: Selecting the table in Table Selection Dialog Panel
    
    
13. In SQL Configuration Wizard, navigate through all the pages by clicking Next and finally click Finish on the last page.
    
    Image Modified
    Figure 14: Finishing Monitor Table configuration
    
    
14. An Information dialog box is displayed in which the paths where SQL queries created for above operation are saved. Info: These can be used to delete triggers, stored procedures and copy tables created for monitoring data.
    
    Image Modified
    Figure 15: Paths for cleanup SQL
    
    
15. A stored procedure and a nested update query are created which will be used to monitor data. The Adapter Mode automatically changes to 'Scheduler' when a Monitor Table statement is configured. This means that the component will not have an input port and is launched in scheduling mode. Click Finish in SQL Configuration Wizard.
    
    Image Modified
    Figure 16: Queries to monitor data in Scheduling mode
    
    
16. Click Finish in Interaction Configurations panel to finish configuring DB_MonitorDataInOracle component.
    
    Image Modified
    Figure 17: Finishing 'DB_MonitorDataInOracle' component configuration

...

1. Drag the Xslt component from Transformation category in the Service Palette to the Fiorano Orchestrator.
2. Connect the components in the following manner:
   
   1. Output port of DB_MonitorDataInOracle to the input port of Xslt
   2. Output port of Xslt to the input port of DB_InsertDataInMSSQL.
      
      Image Modified
      Figure 18: Connecting Xslt component to the two DB components
      
      
3. Double-click Xslt1 component in the Orchestrator to open Configuration Property Sheet (CPS). Click the ellipsis button against Mappings to configure mappings.
   
   Image Modified
   Figure 19: Configure Mappings
   
   
4. Schemas of the OUT_PORT of DB_MonitorDataInOracle and IN_PORT of DB_InsertDataInMSSQL are displayed. Map them in the following manner, save and close the mappings editor.
   
   Image Modified
   Figure 20: Define transformations
   
   
5. Click Finish in the Component Configuration panel to finish Xslt configuration.
   
   Image Modified
   Figure 21: Finishing Xslt component configuration

...

Connect the output port of DBInsertDatainMSSQL component to the input port of a Display (present in Util category in Service Palette) component.

Image Modified
Figure 22: Connecting Display to DB_InsertDataInMSSQL

...

1. Click Check Resource and Connectivity _{Image Modified} icon (or press ALT+SHIFT+C) to check resource and connectivity.
2. Click Run Event ProcessProcess _{Image Modified} icon (ALT+SHIFT+R) to run the event process. Display1 window gets opened.
3. Insert a record into EMPLOYEE table following the steps in Insert Data into Oracle Database section.

4. The output message in Display window will have UpdateCount as "1" confirming the successful monitoring and insertion of data.

   Info: The

   Info
   The data inserted in Oracle database is monitored

   and

   and the same

   record

   record is inserted

   in MS SQL

   into MSSQL Server database.

   Image Modified
   Figure 23: Output message after inserting data in MS SQL Server Database

Anchor
__RefHeading__53_1001738385 __RefHeading__53_1001738385

Reference

• Download DB_Replication_Eg example event process and import it to the Event Process Repository in the Server Explorer in eStudio to understand the configuration used in this example and to execute the working sample.
• Please note that the options used in this example are minimum, which helps you to get an overview of the application. To explore the other options present in the DB, Xslt and Display, refer sections: DB, Xslt and Display respectively.