Developing an ODI XML Transformation That Can Be Executed Within a BPEL Process

Purpose

This OBE tutorial describes and shows you how to create an Oracle Data Integrator (ODI) scenario that will import, transform, and export an XML file. This scenario can then be executed as a partner link within a BPEL process.

Time to Complete

Approximately 2 hour and 30 minutes

Topics

This OBE tutorial covers the following topics:

  Overview

Scenario

 Examples
 Verifying the Prerequisites
 Example 1: Developing an ODI XML Transformation Using Interfaces
 Example 2: Developing an ODI XML Transformation Using Procedures
 Example 3: Developing a BPEL Process for Invoking an ODI Scenario from SOA
 Example 4: Modifying the BPEL Process Created in Example 3 to Invoke a Second Scenario
 Summary

Viewing Screenshots

 Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: Because this action loads all screenshots simultaneously, the response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over each individual icon in the following steps to load and view only the screenshot associated with that step.

The screenshots will not reflect the specific environment that you are using. They are provided to give you an idea of where to locate specific functionality in Oracle Data Integrator.

Overview

For enterprise SOA deployments, there is almost always a need for enterprise data extraction, loading, and transformation. The XML layer of SOA has proven inefficient for certain enterprise data extraction, loading, and transformation (ELT). By leveraging the native SOA architecture within Oracle Data Integrator, you can take advantage of Oracle Data Integrator to perform the ELT. Our internal testing has showed that ODI can be easily 20 times faster than non-XML ELT use cases and, depending on the type of transformation involved, ODI can outperform XSLT XML ELT use cases.

 

Back to Topic List

Scenario

Linda works as a database administrator for Global Enterprise. In Global Enterprise, Linda is responsible for performing database management and integration tasks on various resources within the organization. In particular, Linda is responsible for XML transformation that can be used within a BPEL process. She needs to build an ODI scenario that will import the XML file, perform transformation, and then export this file to a new XML file. Linda is also considering a scenario to create an ODI XML procedure that will import the XML file for further transformation with SQL statements using the ODI XML Driver. The file will then be exported to a new XML file. Linda also needs to create a BPEL process that will execute the ODI scenario to transform the XML file and create a new XML file.

Examples

In this OBE, students learn the scenario through four examples.

Example 1: Developing an ODI XML Transformation Using Interfaces
This example walks you through the steps needed to create an ODI interface that will import an XML file. The file will then be transformed within an ODI interface and be exported to a new XML file.

Example 2: Developing an ODI XML Transformation Using Procedures
This example walks you through the steps needed to create an ODI procedure that will import an XML file. The file will then be transformed with SQL statements using the ODI XML Driver and be exported to a new XML file.

Example 3: Developing a BPEL Process for Invoking an ODI Scenario from SOA
This example walks you through the steps needed to create a BPEL process to poll for a file. When the file appears, BPEL will then execute the ODI scenario created in Example 1 to transform the XML file and create a new XML file.

Example 4: Modifying the BPEL Process Created in Example 3 to Invoke a Second Scenario
This example walks you through the steps needed to modify the BPEL process created in Example 3 to have this process execute an additional ODI scenario. When the file appears, BPEL will execute the ODI scenario created in Example 1 to transform the XML file and create a new XML file, and then execute an additional ODI scenario that was created in Example 2.

 

Back to Topic List

Verifying the Prerequisites

Before you start the tasks, make sure that your system environment meets the following requirements:

Software Requirements

The system should include the following installed products:

The system should include the following configuration:

  • odi-public-ws.aar must be deployed in Axis2. Use the Upload Service link on the Axis2 admin page to upload this file (download from here).
  • The ODI Master and Work repository must be created. You can refer to the steps from here.
  • The xml, xtd, and csv files for the examples must be available. For example, the GEO_DIM.xml file that is included with this sample is approximately 8 MB to demonstrate using a moderately sized XML file with ODI and BPEL. You can download the complete set of demo/xml files from here.
  • The schema ( ODI_STAGE) must be created for the target tables of the exercise and a data server and physical schema for this schema must be available within the ODI Topology Manager. To do this, perform the following steps:
    • Start the Oracle Database 10g XE Administrative Console.
    • Log in using system as the User name and oracle1 as the Password.
    • From the Enterprise Manager Console, select Administration > Database Users > Create Users.
    • Provide the username as ODI_STAGE and password as oracle1.
    • Select the DBA check box and click Create. Note that the ODI_STAGE user is created.
    • Select Start > All Programs > Oracle > Oracle Data Integrator > Topology Manager to open the ODI Topology Manager.
    • Scroll down to the pane at the bottom left to click the Physical Architecture tab.
    • At the top-left corner, under the Physical Architecture pane, click " +" to expand Technologies > Oracle.
    • Right-click Oracle and select Insert Data Server.
    • The Data Server: new window appears. Provide the following parameters:
      Parameter Value
      Name Local_XE
      Instance / dblink (Data Server) XE
      User ODI_STAGE
      Password oracle1
    • Click the JDBC tab. For the JDBC Driver value, click Browse and select Oracle JDBC Driver from the Name drop-down list. Click OK.
    • For the JDBC Url value, click Browse and select jdbc:<hostname>:<port>:<sid>. Click OK. Populate the values according to your environment details.
    • Click Test, Test again, and then OK.
    • Click OK for the Data Server: new window.
    • Under the Physical Architecture pane, click " +" to expand Technologies > Oracle > Local_XE.
    • In the Physical Schema: Local_XE.Schema window, under the Definitions tab, select ODI_STAGE from the Schema (Schema) drop-down list and ODI_STAGE from the Schema (Work Schema) drop-down list.
    • Click the Context tab.
    • Click Add context. Select Global from the Context drop-down list and set Logical Schema as ODI_STAGE.
    • Click OK.

    If not done before, start the services and components for Database 10g XE, Oracle Data Integrator 10g (10.1.3.4), and Oracle SOA Suite 10g.

Back to Topic List

Example 1: Developing an ODI XML Transformation Using Interfaces

This example walks you through the steps needed to build an ODI process using an interface to perform the import, transformation, and export of the XML file. The ODI Web service will invoke the SCENXFORMXML_INT scenario, which is built on PKGXFORMXML. PGKXFORMXML_INT declares one variable to be used within the package, and then executes the XFORMXML interface . ImportXformExportXML_INT maps the source XML file to the target XML. The source XML file is loaded into the staging area using LKM SQL to SQL. The source XML file is then joined to a .csv file. When a record is matched to the join file, the address field of the CLIENT table is updated. The updated XML is then written to an XML file using IKM SQL to SQL Append. IKM SQL to SQL Append is also modified so that it will write to ExportXMLFileLoc within the Commit step.

 Creating a New Project in ODI
 Inserting a New Model for the XML File
 Inserting a Model for the .csv Lookup File
 Importing the IKM and LKM SQL to SQL KMs to Load and Integrate the XML File
 Creating a New Interface to Transform the XML and Write to a New XML File
 Creating a Project Variable to set the XML Export File Location
 Editing SQL to SQL Append to Create an XML File on the Commit Step
 Creating a New Package to Execute the INT_ImportXFormExportXML Interface
 Creating a New Scenario to be Executed from the ODIInvoke Web Service

Back to Topic List

 

Creating a New Project in ODI


A project is a collection of ODI objects that have been created by users. It should encompass a single functional domain. The best way is to use a project to represent a functional domain in your integration processes or to represent an integration project that you need to develop with ODI. Each project contains its own knowledge modules, variables, markers, and other types of objects. These objects can be shared between different projects by duplicating them. You can also use global objects such as variables or sequences to define parameters that are common to all projects.

To create a new project in ODI, perform the following steps:

1.

Select Start > All Programs > Oracle > Oracle Data Integrator > Designer to open ODI Designer.

 

2.

The Oracle Data Integrator Login screen appears. From the Login Name drop-down list, select Repo_designer.

 

3.

The default User name and Password are preset as SUPERVISOR and SUNOPSIS. Click OK to login.

 

4.

In the pane at the bottom left, click the Projects tab.

 

5.

Note that the Projects pane appears at the top-left corner. Right-click in the pane and select Insert Project.

 

6.

The Project: New window appears. Enter the name as BPELXFormWithODI. Note that the Code field is also populated with the same text. Click OK to view the new project that is populated in the Projects pane.

 

 

Back to Topic List

Inserting a New Model for the XML File

To insert a new model for the source XML file, perform the following steps:

1.

In ODI Designer, scroll to the pane at the bottom left and click the Models tab.

 

 

2.

Note that the Models pane appears at the top-left corner. Right-click in the pane and select Insert Model.

 

3.

In the Model: New window, enter values for the new model from the following table, and then click the Reverse tab.

Parameter Value
Name SRC_GEO_DIM_XML
Technology XML
Logical Schema XML_DEMO_GEO

 

4.

On the Reverse tab, select Global from the Context drop-down list, and then scroll down. Click the Reverse button. This generates the XML source.

 

 

5.

Click Yes for confirmation, and then click OK.

 

6.

In the Models pane, note the new data store being populated. Click " +" to expand and view the SRC_GEO_DIM_XML model.

 

Back to Topic List

 

Inserting a Model for the .csv Lookup File

To insert a model for the new .csv lookup file, perform the following steps:

1.

Click the Models tab in Designer.

 

2.

Right-click and select Insert Model.

 

3.

In the Model: New window, enter values for the new model from the following table. Click the Reverse tab. Select the Global context. Don't click the Reverse button. Click OK. The new data sources are added in the next step.

Parameter Value
Name SRC_FILE_LOOKUP
Technology FILE
Logical Schema FILE_DEMO_SRC

 

 

4.

Insert a new datastore for the .csv lookup file. Right-click the newly created SRC_FILE_LOOKUP and select Insert Datastore. Select the resource as DIM_GEO_LOOKUP.csv. Name the datastore DIM_GEO_LOOKUP.

 

 

5.

Click the Files tab and select Delimited as the file format. Set the Heading (Number of Lines) to 1. For Field Separator, select Other and enter a comma.

 

6.

Click the Column tab and click the Reverse button. Click Yes to confirm to save your changes before the reverse.

 

 

7.

The following columns should be reversed. Set the client_id type to String, and then click OK to save.

 

 

Back to Topic List

 

Importing the IKM and LKM SQL to SQL KMs to Load and Integrate the XML File

Before creating the interface, the LKM and IKM that are required to load and integrate the XML data must be imported into the project. To import the LKM and IKM, perform the following steps:

1.

Click the Projects tab in Designer. Expand Knowledge Modules, right-click the Loading (LKM) node, and then select Import Knowledge Modules. Browse to the impexp folder in the directory where ODI is installed, for example I:\ODI\oracle\impexp, and then click OK. Select LKM SQL to SQL, and then click OK.

 

 

2.

Right-click the Loading (LKM) node and select Import Knowledge Modules. Browse to the impexp folder ( I:\ODI\oracledi\impexp), select IKM SQL to SQL Append, and then click OK.

 

3.

Right-click the Loading (LKM) node and select Import Knowledge Modules. Browse to the impexp folder of the ODI Install ( I:\ODI\oracledi\impexp), select LKM File to SQL, and then click OK.

 

4.

Verify that the Knowledge Modules node looks as follows:

 

 

 

Back to Topic List

 

Creating a New Interface to Transform the XML and Write to a New XML File

To create a new interface INT_ImportXFormExportXML, perform the following steps:

1.

Click the Projects tab in ODI Designer and expand First Folder. Right-click the Interfaces node and select Insert Interface. Name the Interface INT_ImportXFormExportXML. Select the Staging Area Different From Target check box. Select SUNOPSIS_MEMORY_ENGINE.

 

2.

Click the Diagram tab, and then click the Models tab to drag the source and target to Designer. Drag the Client Datastore from SRC_GEO_DIM_XML into the Target container. Drag the Client Datastore from the SRC_GEO_DIM_XML into the Source container. When Designer asks " Do you want to perform an Automatic Mapping?" click Yes. The Diagram tab should look as follows.

Note: For this exercise, we set the source file name and location within the IKM, but need to edit the Commit step. Therefore, we can use the same model for the source and target because they have the same logical design.

 

3.

Drag the DIM_GEO_LOOKUP source into the source container, and then join the two tables by dragging Client ID from Client to Client_ID of DIM_GEO_LOOKUP. Click the Join icon, and then select the CLIENT (CLIENT) check box in join properties in the lower part of the screen.

 

 

4.

In Target Datastore, click the ADRESS field, change Mapping for ADRESS to coalesce (DIM.new_address,CLIENT.ADRESS).

 

 

5.

Click the Flow tab. Click SS_0 (1 - FILE_GENERIC) and then, in the LKM Selection section, set the LKM for DIM to LKM File to SQL. Click SS_0 (2 - XML_GEO_DIM) and make sure that the LKM for Client is set to LKM SQL to SQL. In the Target window, make sure that the IKM for CLIENT (target) is set to IKM SQL to SQL Append. Set the DELETE_ALL option to Yes as shown in the screenshot. Click OK to save. The INT_ImportXFormExportXML interface is now set up.

 

 

 

Back to Topic List

 

Creating a Project Variable to Set the XML Export File Location

To create a project variable to set the XML export file location, perform the following steps:

1.

Click the Projects tab in ODI Designer. Right-click the Variables node and select Insert Variable.

 

 

2.

Name the variable ExportXMLFileLoc. Set the Datatypes to Text. Set the default value to "..\demo\xml\DIM_GEO_OUT.xml" Click OK.


 

 

Back to Topic List

 

Editing SQL to SQL Append to Create an XML File on the Commit Step

To edit SQL to SQL Append to create an XML file on the Commit step, perform the following steps:

1.

Click the Projects tab in ODI Designer. Expand the Knowledge Modules > Integration (IKM) node. Right-click the IKM SQL to SQL Append node, and then select Edit. If the Object Locking dialog box appears, select the check box, and then click No.

 

 

2.

Click the Details tab. Double-click step 152 - Commit. Change the command to CREATE XMLFILE #ExportXMLFileLoc FROM SCHEMA GEO. Click OK, and then click OK again to save the IKM.

Note: This statement writes the transformed XML data in memory to the location stored in the ExportXMLFileLoc variable, which was set up in a previous step.


 

 

Back to Topic List

Creating a New Package to Execute the INT_ImportXFormExportXML Interface

To create a new package to execute the INT_ImportXFormExportXML interface, perform the following steps:

1.

Click the Projects tab in ODI Designer. Expand the First Folder node, right-click Packages, and then select Insert Package. Name the Package PKG_ImportXFormExportXML.

 

 

2.

Expand Variables. Click the Diagram tab. Drag the ExportXMLFileLoc variable from the project variables into the diagram. Set Type to Declare Variable.


 

3.

Drag the INT_ImportXFormExportXML interface into the diagram.

4.

Click the ok icon. Connect the ExportXMLFileLoc declare variable to the INT_ImportXFormExportXML interface by clicking the ExportXMLFileLoc declare variable and dragging the arrow from the variable to the interface. Click OK to save the package. Execute the package: right-click the PKG_ImportXFormExportXML package and select Execute. Click OK.


 

5.

Review the execution results in ODI Operator. To start ODI Operator, select All Programs > Oracle > Oracle Data Integrator > Operator. Log in as Repo_designer using SUPERVISOR as the Username and SUNOPSIS as the Password. In the pane at the bottom left, click the Hierarchical Sessions tab. Scroll up the pane and click " +" to expand Date > date of execution > Steps. The steps of your PKG_ImportXFormExportXML package should be performed successfully. Verify that the new XML file DIM_GEO_OUT.XML is created in the <ODI Home>\demo\xml (I:\ODI\oracledi\demo\xml) directory.


 

 

Back to Topic List

Creating a New Scenario to Be Executed from ODIInvoke Web Service

To create a new scenario to be executed from the ODIInvoke Web Service, perform the following steps:

1.

In the ODI Designer Projects pane, expand First Folder. Right-click the newly created package PKG_ImportXFormExportXML and select Generate Scenario. Name this scenario SCEN_IMPORTXFORMEXPORTXML. Set Version to 001. Click OK to save the scenario.

 

2.

The following screen appears. Click OK to save. You are now ready to execute the scenario from within BPEL.

 

Back to Topic List

 

Example 2: Developing an ODI XML Transformation Using Procedures

This example walks you through the steps to build the ODI process using an interface to perform the import, transformation, and export of the XML file. The ODI Web service will invoke the SCEN_IMPORTXFORMEXPORTXML scenario, which is built on PKG_ImportXFormExportXML. PKG_ImportXFormExportXML declares one variable to be used within the package, and then executes the ImportXformExportXML_INT interface. ImportXformExportXML_INT maps the source XML file to the target XML. The source XML file is loaded to the staging area using a procedure step and the L OAD XML ODI JDBC driver statement. The source XML file is then updated within a procedure step by using an UPDATE SQL statement, which is executed by the ODI XML JDBC driver. The updated XML is then written to an XML file using a procedure step and the Create XML ODI JDBC driver statement.

 Creating a New Project in ODI
 Inserting a New Model for the XML File
 Creating a New ImportXFormExportXML Procedure
 Creating Options for the ImportXFormExportXML Procedure
 Creating Detail Steps for the New ImportXFormExportXML Procedure
 Creating Project Variables for the New Project
 Creating a New Package to Declare Variables and to Execute ImportXFormExportXML Procedures
 Creating a New Scenario to Be Executed from the ODIInvoke Web Service

 

Creating a New Project in ODI

(Note: Project may have been created in Example 1.)


A project is a collection of ODI objects that have been created by users. It should encompass a single functional domain. The best way is to use a project to represent a functional domain in your integration processes or to represent an integration project that you need to develop with ODI. Each project contains its own knowledge modules, variables, markers, and other types of objects. These objects can be shared between different projects by duplicating them. You can also use global objects such as variables or sequences to define parameters that are common to all projects.

To create a new project in ODI, perform the following steps:

1.

Select Start > All Programs > Oracle > Oracle Data Integrator > Designer to open ODI Designer.

 

2.

The Oracle Data Integrator Login screen appears. From the Login Name drop-down list, select Repo_designer.

 

3.

The default User name is SUPERVISOR and the Password is SUNOPSIS. Click OK to login.

 

4.

In the pane at the bottom left, click the Projects tab.

 

5.

Note that the Projects pane appears at the top-left corner. Right-click to select Insert Project.

 

6.

The Project: New window appears. Enter the name as BPELXFormWithODI. Note that the Code field is also populated with the same text. Click OK to view the new project that is populated in the Projects pane.

 

 

Back to Topic List

Inserting a New Model for the XML File

Note: The XML model may have been created in Example 1.

To insert a new model for the source XML file, perform the following steps:

1.

In ODI Designer, scroll to the pane at the bottom left and click the Models tab.

 

 

2.

Note that the Models pane appears at the top-left corner. Right-click and select Insert Model.

 

3.

In the Model: New window, enter the values for the new model. For this, provide the following parameters, and then click the Reverse tab.

Parameter Value
Name SRC_GEO_DIM_XML
Technology XML
Logical Schema XML_DEMO_GEO

 

4.

Under the Reverse tab, select Global from the Context drop-down list, and then scroll down to click the Reverse button. This generates the XML source.

 

 

5.

Click Yes for confirmation. Click OK.

 

6.

In the Models pane, note the new data store being populated. Click the " +" sign to expand and view the SRC_GEO_DIM_XML model.

 

Back to Topic List

 

Creating a New ImportXFormExportXML Procedure


This procedure is the basis for your ODI process. The procedure is set up to be as dynamic as possible using options and variables.

To create the new ImportXFormExportXML procedure, perform the following steps:

1.

On the Projects tab, expand the BPELXFormWithODI project, expand the First Folder node, right-click the Procedures node, and then select Insert Procedure.

 

2.

Name the procedure ImportXFormExportXML. On the Definition tab, select the Source and Target Technology as XML. Click OK to save the procedure.

.

 

Back to Topic List

 

Creating Options for the ImportXFormExportXML Procedure

To create options for the newly created procedure, perform the following steps:

1.

Right-click the newly created ImportXFormExportXML procedure and select Insert Option.

 

 

2.

Name the Option ImportXMLFileLoc. Set Type to Text. Set Description to Import XML File Location. Set Default Value to the exercise location "..\demo\xml\GEO_DIM.xml", and then click OK.

 

3.

Similarly insert another option, ImportDTDFileLoc. Set Type to Text. Set Description to Import DTD File Location. Set Default Value to the exercise location "..\demo\xml\GEO_DIM.dtd". Verify that the options are set as shown below. Click OK.

 

 

4.

Similarly insert another option, ExportXMLFileLoc. Set Type to Text. Set Description to Export XML File Location. Set Default Value to the exercise location "..\demo\xml\GEO_DIM_OUT.xml". Verify that the options are set as shown below. Click OK.

 

 

Back to Topic List

Creating Detail Steps for the New ImportXFormExportXML Procedure

To create Detail steps for new procedure, perform the following steps:

1.

Create the Detail step LoadXMLFileIntoMemory within the ImportXFormExportXML procedure: Right-click the ImportXFormExportXML procedure and select Edit. Click the Details tab. Click the Add icon  to add a new Detail step. Name the Detail step LoadXMLFileIntoMemory. Configure the Detail step as shown in the following table, and then click OK.

Note: In this step, you load into memory the file defined in the ODI Option “ImportXMLFileLoc”, which was set up earlier. After the XML file is in memory, SQL commands can be executed in additional steps to transform the XML data. The SQL commands are executed by ODI using the ODI XML JDBC driver.

Parameter Value
Name LoadXMLFileIntoMemory
Technology XML
Logical Schema XML_DEMO_GEO
Command

LOAD FILE <%=odiRef.getOption("ImportXMLFileLoc")%> WITH DTD <%=odiRef.getOption("ImportDTDFileLoc")%> ON SCHEMA GEO REPLACE AUTO_UNLOCK

 

2.

Create the Detail step XFormXMLInMemory within the ImportXFormXML procedure: Click the Add icon to add a new Detail step. Name the Detail step XFormXMLInMemory. Configure the Detail step as shown in the following table, and then click OK.

Note: This step updates the XML that was loaded into memory in the previous step. This SQL statement updates the age of the clients. Any valid SQL statement can be used to transform the data.

Parameter Value
Name XFormXMLInMemory
Technology XML
Logical Schema XML_DEMO_GEO
Command

Update GEO.Client set age=age+1

 

 

3.

Create the Detail step CreateExportXMLFileFromMemory within the ImportXFormXML procedure : Click the Add icon to add a new Detail step. Name the Detail step CreateExportXMLFileFromMemory. Configure the Detail step as shown in the following table, and then click OK.

Note: This step will write the transformed XML in memory to an XML file. The name and location of the XML file are specified within the ExportXMLFileLoc option. The procedure should look as follows. Click OK to save the procedure.

Parameter Value
Name CreateExportXMLFileFromMemory
Technology XML
Logical Schema XML_DEMO_GEO
Command

CREATE XMLFILE <%=odiRef.getOption("ExportXMLFileLoc")%> FROM SCHEMA GEO

 

 

 

Back to Topic List

 

Creating Project Variables for the New Project

To create project variables that are to be used in the new project, perform the following steps:

1.

Create the ExportXMLFileLoc2 project variable that is to be used within the new package. Click the Projects tab, right-click the Variables node, and then select Insert Variable. Name the variable ExportXMLFileLoc2, set Type to Text, and set Default Value to "..\demo\xml\GEO_DIM_OUT.XML". Click OK to save the variable.

 

2.

Create the ImportXMLFileLoc project variable that is to be used within the new package. Right-click the Variables node and select Insert Variable. Name the variable ImportXMLFileLoc, set Type to Text, and set Default Value to "..\demo\file\Source1.xml". Click OK to save the variable.

 

 

3.

Create the ImportDTDLFileLoc project variable that is to be used within the new package. Right-click the Variables node and select Insert Variable. Name the variable ImportDTDFileLoc, set Type to Text, and set Default Value to "..\demo\xml\GEO_DIM.DTD". Click OK to save the variable.

 

Back to Topic List

Creating a New Package to Declare Variables and to Execute ImportXFormExportXML Procedures

To create a new package, declare variables, and execute ImportXFormExportXML procedures, perform the following steps:

1.

Click the Projects tab within ODI Designer. Expand the First Folder node. Right-click Packages and select Insert Package. Name the package PKG_ImportXFormExportXML_Proc.

 

2.

Click the Diagram tab. Drag the ExportXMLFileLoc2 variable from project variables into the diagram. Set Type to Declare Variable.

 

 

3.

Similarly, drag the ImportXMLFileLoc variable from project variables into the diagram. Set Type to Declare Variable.

 

4.

Similarly, drag the ImportDTDFileLoc variable from project variables into the diagram. Set Type to Declare Variable.

5.

Expand the Procedures node, and then drag the ImportXFormExportXML procedure into the diagram. In the Properties area, click the Options tab. Set the options as shown in the following table:

Option Value
ImportXMLFileLoc #ImportXMLFileLoc
ImportDTDFileLoc #ImportDTDFileLoc
ExportXMLFileLoc #ExportXMLFileLoc2

6.

Connect the Declare Variables serially and then connect to the procedure by clicking the ok icon  . Click and drag from variable to variable, and then from ImportDTDFileLoc to the procedure. Click OK to save the package.

 

7.

Execute the package, and then review the execution results in ODI Operator. Review the execution results in ODI Operator. To start ODI Operator (if not started), select All Programs > Oracle > Oracle Data Integrator > Operator. Log in as Repo_designer with SUPERVISOR as the User name and SUNOPSIS as the Password. In the pane at the bottom left, click the Hierarchical Sessions tab. Scroll up the pane and click " +" to expand Date > date of execution > Steps. The steps of your PKG_ImportXFormExportXML_Proc package should be performed successfully. Verify that the new XML file GEO_DIM_OUT.XML has been created in the <ODI Home>\demo\xml (I:\ODI\oracledi\demo\xml) directory.

 

 

Creating New Scenario to Be Executed from the ODIInvoke Web Service

To create a new scenario to be executed from the ODIInvoke Web service, perform the following steps:

1.

Right-click the newly created PKG_ImportXFormExportXML_Proc package and select Generate Scenario. Name this scenario SCEN_IMPORTXFORMEXPORTXML_PROC. Set Version to 001. Click OK to save the scenario.

 

2.

The following screen appears. Click OK to save. You are now ready to execute the scenario from within BPEL.

 

Back to Topic List

 

Example 3: Developing a BPEL Process for Invoking an ODI Scenario from SOA

In this example, we will build a new Business Process Execution Language (BPEL ) process that will monitor a directory for the source file’s arrival. When the source file arrives, the process collects its file name and location, and invokes ODI to transform and move the file into the target.

We will build one BPEL process to invoke both the ODI scenarios that we built in Example 1 and Example 2. JDeveloper is the tool that we will use to build the BPEL process and deploy it to the application server.

 Creating a New Application for the BPEL Process
 Creating a New Project for the BPEL Process
 Defining the File Adapter
 Inserting the Receive Service to Receive Messages from the FilePoll Service
 Updating the File Adapter to Collect Only the File Name and Location, and Not the Contents of the File
 Creating a Partner Link to Invoke ODI
 Creating an Invoke Activity for the InvokeODI Partner Link
 Initializing the Variables Needed to Invoke the ODI Scenarios
 Building an XSL Transformation to Map Values into the Expected Fields
 Setting the ImportXMLFileLoc ODI Variable
 Setting the Scenario Variable Values
 Deploying the BPEL Process
 Testing the BPEL Process

 

Creating a New Application for the BPEL Process

To create a new application for the BPEL process, perform the following steps:

1.

Start JDeveloper by clicking jdeveloper.exe in G:\jdevStudio10131. Alternatively, use the SOA Suite Jdev shortcut on the desktop. In the Applications Navigator on the left, right-click Applications and select New Application.

 

 

 

2.

Name the application ODI_Example, and then click OK. Click Cancel in the Create Project dialog box that appears. Your new application appears in the Application Navigator.

 

 

Back to Topic List

 

Creating a New Project for the BPEL Process

To create a new project for your BPEL process, perform the following steps:

1.

Right-click your newly created application ODI_Example and select New Project. Select BPEL Process Project from the projects category. Click OK.

 

 

2.

On the BPEL Project Creation Wizard screen, name your BPEL process SourceFileMonitor, select Empty BPEL Process from the Template drop-down list, and then click Finish. Your new project appears in the Application Navigator and the SourceFileMonitor.bpel file opens in the JDeveloper window.

 

Back to Topic List

 

Defining the File Adapter


You use the File Adapter to poll a directory for incoming source files. When a file arrives, the Adapter passes the file name and directory to the ODI scenario via a synchronous Web services call.

To define the File Adapter, perform the following steps:

1.

In the Components Palette at the right, select Services from the drop-down list. Select File Adapter and drag it onto the gray swim lane under the Services heading, which is to the left of the main diagram. An Adapter Configuration Wizard appears. Click Next on the Welcome page.

 

2.

In the Service Name field, enter FilePoll and click Next.

.

 

3.

Select the Operation Type as Read File, and then click Next.

.

 

 

4.

Select the directory to poll for incoming files by using the Browse button. Select <ODI_HOME>\oracledi\demo\file. For example: I:\ODI\oracledi\demo\file as shown in the screenshot. Deselect the Delete files after successful retrieval check box, and then click Next.

Note: Because we are using the File Adapter only to get the file name and directory, we do not want to delete the file. If we select this check box, the file is deleted before the ODI scenario is executed.

.

 

 

5.

Enter Source*.xml into the Include Files with Name Pattern field. This configures the Adapter to pick up any files that match this file name. Click Next.

.

 

6.

Set Polling Frequency to 10 seconds, and then click Next.

.

7.

Select the Native format translation is not required (Schema is Opaque) check box as we are not going to read the contents of the file. Click Next, and then click Finish.

.

 

8.

Retain the defaults and click OK on the Create Partner Link Wizard screen. At the left of the main diagram window, you should see your partner link called FilePoll defined in the Services swim lane. You have now defined the File Adapter as an external service (partner link) that will poll for new files in the incoming directory.

.

 

Back to Topic List

 

Inserting the Receive Service to Receive Messages from the FilePoll Service

To create the Receive service to receive messages, perform the following steps:

1.

In the Components Palette at the right, select Process Activities from the drop-down list. Drag the Receive activity into the Drop Activity Here area at the center of the diagram.

 

 

2.

Drag the arrow to the left of the Receive activity onto the FilePoll partner link and release it. The Edit Receive window appears. In the Name field, enter Receive_FileDetail. Select the Create Instance check box. Click the Magic Wand button next to the Variable field. In the window that appears, retain the details and click OK to create a new BPEL variable.

 

3.

In the Edit Receive window, click the Adapters tab. Click the Flashlight icon next to Header Variable. In the Variable Chooser window, select Variables in the tree and click the Magic Wand icon at the top right of the window. The Create Variable window appears.

 

 

4.

Name the variable FileHeaders and select Message Type from the Type options. Now click the Flashlight icon next to Message Type. In Type Chooser , expand the tree as shown in the following screenshot until you get to InboundHeader_msg, and then click OK.

 

 

5.

Click OK in the Create Variable window. Ensure that FileHeaders is highlighted in the Variable Chooser window, and then click OK. You should now have FileHeaders in the Header Variable field. Click OK in the Edit Receive window.

 

 

6.

Verify that your FilePoll partner link is now connected to the Receive_FileDetail activity.

 

 

 

 

 

Back to Topic List

Updating the File Adapter to Collect Only the File Name and Location, and Not the Contents of the File

To update the File Adapter to collect only the file name and location, and not the contents, perform the following steps:

1.

In the Applications Navigator, expand your SourceFileMonitor project. Expand the Integration Content directory. Expand the FilePoll object. Double-click FilePoll.wsdl file so that it opens in the main window. At the bottom of the main window, with FilePoll.wsdl open in it, click the Source tab.

 

2.

Scroll down the FilePoll.wsdl file until you see the tag:

<jca:operation
PhysicalDirectory="I:\ODI\oracledi\demo\file"
ActivationSpec="oracle.tip.adapter.file.inbound.FileActivationSpec"
DeleteFile="false"
IncludeFiles="Source.*\.xml"
PollingFrequency="10"
MinimumAge="0"
OpaqueSchema="true" >

Edit this tag so that it reads:

<jca:operation
PhysicalDirectory="I:\ODI\oracledi\demo\file"
ActivationSpec="oracle.tip.adapter.file.inbound.FileActivationSpec"
DeleteFile="false"
IncludeFiles="Source.*\.xml"
PollingFrequency="10"
MinimumAge="0"
OpaqueSchema="false"
UseHeaders="true" >

From the main menu, select File > Save All to save the FilePoll.wsdl file.

 

 

Back to Topic List

 

Creating a Partner Link to Invoke ODI

To create a partner link to invoke ODI, perform the following steps:

1.

Click the SourceFileMonitor.bpel tab at the top of the main window. In the gray Services swim lane to the right of the diagram, right-click and select Create Partner Link.

 

 

 

2.

On the Create Partner Link Wizard screen, name the partner link InvokeODI. In the WSDL File field, enter http://localhost:8888/axis2/services/OdiInvoke?wsdl, and then click the Parse WSDL File icon  as shown in the following screenshot. In the Partner Link Type window, click Yes.

 

 

3.

In the Partner Role field, select ODIInvokePortType_Role from the drop-down list, and then click OK. The InvokeODI partner link appears in the Services swim lane.

 

Back to Topic List

Creating an Invoke Activity for the InvokeODI Partner Link

To create an Invoke activity for the InvokeODI partner link, perform the following steps:

1.

In the Components Palette on the right, select Process Activities from the drop-down list. Drag the Invoke activity to a position under the Receive activity in the main diagram window.

 

2.

Drag an arrow from the Invoke_1 activity in the diagram onto the InvokeODI partner link. The Invoke Wizard screen appears. Name the Invoke activity IMPORTXFORMEXPORTXML_PROC. From the Operation drop-down list, select invokeScenario, and then click the Magic Wand icon next to the Input Variable field. In the Create Variable window, change the variable name to InvokeODI_PROC_InputVariable, and then click OK.

Note: First, the BPEL process invokes the SCEN_IMPORTXFORMEXPORTXML_PROC scenario that you built in Example 2. For this scenario, you will pass two of the three variables defined earlier: ExportXMLFileLoc and ImportXMLFileLoc. The BPEL process sets these variables. The third variable I mportDTDFileLoc uses the default value set in ODI.

 

3.

Similarly, click the Magic Wand icon next to the Output Variable field. In the Create Variable window, change the variable name to InvokeODI_PROC_OutputVariable. Click OK, and then click OK again.

 

 

 

4.

Verify that your diagram appears similar to the following screenshot:

 

Back to Topic List

Initializing the Variables Needed to Invoke the ODI Scenarios

To initialize the variables needed to invoke the ODI scenarios, perform the following steps:

1.

From the Components Palette at the right, drag a Transform activity onto the diagram between Receive_FileDetail and IMPORTXFORMEXPORTXML_PROC.

 

2.

Double-click the Transform_1 activity. On the Transform Wizard screen, select FileHeaders from the Source Variable drop-down list and InvokeODI_PROC_InputVariable from the Target Variable drop-down list. Click the Magic Wand icon next to the Mapper File field.

 

Back to Topic List

 

 

Creating an XSL Transformation to Map Values into the Expected Fields

In the following steps, you build an XSL transformation to map values into the expected fields. Perform the following steps:

1.

In the Target: OdiInvoke?wsdl schema at the right, right-click the <target> node and select Expand All.

 

2.

Select the odi: JdbcDriver node in the target schema. Right-click the odi: JdbcDriver node and select Set Text. In the Text field, enter oracle.jdbc.driver.OracleDriver, and then click OK.

Note: Make sure that you do not include spaces before or after the JDBC driver name. This may cause a system error in the scenario invocation process. Here we also assume that you are using Oracle Database as your ODI Master repository. If this is not the case, you’ll need to enter the correct JDBC connect details for your database.

 

3.

Because you set the JdbcDriver node to a static value, most of the fields under the target schema will be set as static fields. To set a node with static text, you can right-click the node and select Set Text as you did for the JdbcDriver node. For the nodes, set the static values from the following table:

Repository Connection
JdbcDriver oracle.jdbc.driver.OracleDriver

Your JDBC driver for the Master repository

JdbcUrl jdbc:oracle:thin:@localhost:1521:XE

Your JDBC connect URL for the Master repository

JdbcUser snpm The Master repository schema or user
JdbcPassword oracle1

oracle1, the Master repository schema password

OdiUser SUPERVISOR

The ODI user

OdiPassword SUNOPSIS The ODI user password
WorkRepository WORKREP

The Work repository name in ODI (not schema name)

Command
ScenName

SCEN_IMPORTXFORMEXPORTXML_PROC

ScenVersion

001

Context GLOBAL
LogLevel 5
SyncMode

1

SessionName SCEN_IMPORTXFORMEXPORTXML_PROC
Agent
Host

localhost

Port

20910

Verify that you have completed all the static mappings. Your Transformation_1.xsl file should appear similar to the following screenshot:

 

Back to Topic List

 

Setting the ImportXMLFileLoc ODI Variable

To set the ImportXMLFileLoc ODI variable, perform the following steps:

1.

Set the first of the dynamic values. In the target schema, under Command and Variables, right-click the Name node and select Set Text . Enter BPELXFormWithODI.ImportXMLFileLoc and click OK.

Note: The ODI variable name must be prefixed with the ODI project name. This corresponds to one of the variables that we created in ODI for the SCEN_IMPORTXFORMEXPORTXML_PROC scenario.

 

 

 

2.

Set the value that corresponds to the ImportXMLFileLoc variable. From the Components Palette at the right, select String Functions from the drop-down list. Drag the concat function into the area in the middle of the Transform_1.xsl diagram.

 

 

3.

From the fileAdapterInboundHeader.wsdl source schema on the left, select, hold, and drag the directory node to draw a line to the left interface of the concat function. Repeat this for the fileName node.

 

4.

Double-click the concat function to view the values in the concatenation function. Place your cursor in the first element and click the Add button on the right. A new field is added between the original two. Enter ‘\’ in the new field (including the single quotation marks).

Note: Some values cannot be cut and pasted from this document because the quotation marks are not pasted correctly.

 

5.

Place your cursor in the first element and click the Add button on the right. A new field is added. Using the Move Up button, make this the first field in the list.

 

6.

Enter ‘&quot;' in the new field (including the single quotation marks). Place your cursor in the last element and click the Add button on the right. A new field is added at the bottom. Enter ‘&quot;' in the new field (including the single quotation marks). Verify that your concatenation function matches the following image, and then click OK.

 

7.

Now select and hold the right interface of the concat function and drag it onto the value node of the target schema (underneath invokeScenarioRequest, Command, and Variables). Select File > Save All from the main menu.

 

 

Back to Topic List

 

Setting the Scenario Variable Values

We have set almost all the values we need to invoke the SCEN_IMPORTXFORMEXPORTXML_PROC scenario. We still need to set the ExportXMLFileLoc2 variable. To set the variable, perform the following steps:

1.

Click the SourceFileMonitor.bpel tab in the main window. From the Components Palette at the right, drag an Assign activity onto the diagram after the Transform_1 activity. On the Copy Operation tab, select Append Operation from the Create drop-down list. The Create Append Operation Wizard appears.

 

 

2.

Double-click the Assign activity to open the Assign Wizard. You will create the ExportXMLFileLoc variable by appending an XML fragment. In the From column on the left, select XML Fragment from the Type drop-down list. In the XML Fragment text box that appears, enter:
<Variables xmlns="xmlns.oracle.com/odi/OdiInvoke/">
<Name> BPELXFormWithODI.ExportXMLFileLoc2</Name>
<Value>”..\demo\xml\GEO_DIM_OUT_BPEL_PROC.xml”</Value>
</Variables>

 

 

3.

In the To column on the right, expand Process > Variables > InvokeODI_PROC_InputVariable > part1 > invokeScenarioRequest, and then select the Command node. The Xpath field at the bottom of the To column should contain /ns3:invokeScenarioRequest/ns3:Command . Click OK in the Edit Append Operation window, and then click OK in the Assign activity.

 

4.

Verify that your BPEL process looks like the following screenshot.

 

Back to Topic List

 

Deploying the BPEL Process

Your SourceFileMonitor BPEL process is now ready to be deployed. In its current state, it will poll the <ODI_Home>/oracledi/demo/file directory for a file with a name that matches Source*.xml. It will then invoke the SCEN_ImportXFormExportXML_Proc scenario passing the picked up file and the static values through to ODI. It assumes that the GEO_DIM.dtd file already exists at ImportDTDFileLoc.

Note: It is assumed that you have previously configured the Axis 2 Framework and ODI to support Web services. Ensure that your ODI Agent is running. If it is not running, start it: Start > All Programs > Oracle > Oracle Data Integrator > Agent.

To deploy the BPEL process, perform the following steps:

1.

In the Applications Navigator on the right, right-click your project ( SourceFileMonitor), and then select Deploy > SoademointegConnection > Deploy to default domain.

 

 

2.

In the logging window at the bottom of the page, click the Apache Ant tab. Verify that Build Successful is displayed in green when the process has finished deploying.

Note: If you see any errors and/or your process fails to deploy, revisit the above steps to correct the errors.

 

 

 

 

Back to Topic List

 

Testing the BPEL Process

To run the BPEL process, perform the following steps:

1.

Open a browser and enter http://localhost:8888/BPELConsole as the URL. The Login page appears. Enter your application server credentials. Enter oc4jadmin in the Username field and welcome1 in the Password field. Click Login. The main BPEL Dashboard appears. Verify that your SourceFileMonitor BPEL project appears under the Deployed BPEL Processes column on the left.

 

2.

To start the process, copy the GEO_DIM.xml file and paste it to the location that the process is polling. Copy GEO_DIM.xml from the <ODI_Home>/oracledi/demo/xml/ directory to the <ODI_Home>/oracledi/demo/file directory.

 

3.

Rename GEO_DIM.xml to Source1.xml. Allow 10 seconds for the File Adapter to pick up the file. Return to your BPEL Console. At the bottom of the Dashboard page, there is a region titled Recently Completed BPEL Process Instances. A new instance of SourceFileMonitor should appear here.

 

 

4.

Click this instance link to open in the Flow view. Click the last activity in the process, IMPORTXFORMEXPORTXML_PROC . The window that appears shows the value of the variable that was provided to the ODI Web service. View the repository connection information and the three variables that we set earlier. Check whether the ImportXMLFileLoc variable value is correctly picking up the Source1.xml file that we dropped in the directory to initiate the process.

Note: The Activity Audit Trail screen shows that the process has finished execution of the steps that we built earlier. In the first step, when the file is picked up, the system retrieves the header details. Then the transform step is used to prepare the details to be sent to ODI. The Assign activity updates the variables to be sent. Finally, the ODI Web service is invoked.

 

5.

At the bottom of the window, you should see the value returned from ODI. If the Web service successfully ran the SCEN_IMPORTXFORMEXPORTXML_PROC scenario, you should see True returned in the OK field. To verify that the process completed successfully, check the <ODI_HOME>/oracledi/demo/xml directory for the GEO_DIM_OUT_BPEL_PROC.xml file (or the directory and file that you set in the ExportXMLFileLoc variable). If the file exists, the scenario was invoked by BPEL and completed its integrations and transformations.

You have now successfully invoked an ODI scenario through a Web service.

Note: You can also verify the execution of the ODI scenario in ODI Operator. ODI Operator is also a good place to debug if errors occur. If you would like to initiate the process again, you’ll need to make sure that the Date Modified time stamp on the file that you drop into the polled directory ( Source1.xml) is newer than the previous file picked up. This is because we chose not to delete or archive the file that we are monitoring with the File Adapter. To change the time stamp, open this file in the text editor, save it without any modifications, and then close the file. Some text editors insert control characters that make this file unuseful. We recommend that you open this file in JDeveloper, add and delete the space, and then save the file without any other modifications.

 

Back to Topic List

Example 4: Modifying the BPEL Process Created in Example 3 to Invoke a Second Scenario

In this example, you Invoke a second ODI scenario from the BPEL process. You extend the BPEL process to invoke the SCEN_IMPORTXFORMEXPORTXML scenario that was created in Example 1. This scenario expects the location of the ImportXMLFile to be a static location, so we will use only one variable, ExportXMLFileLoc. Therefore, this example should be much simpler. Try this example using the following instructions as a reference and attempt this exercise yourself.

 Invoking the ODI Web Service
 Setting the Variable Value to Invoke the ODI Scenario and Building an XSL Transformation to Map Values into the Expected Fields
 Deploying the Updated BPEL Process
 Testing the Updated BPEL Process

 

Invoking the ODI Web Service

To invoke the ODI Web service, perform the following steps:

1.

Open JDeveloper and click the SourceFileMonitor.bpel tab in the main window. From the Components Palette on the right, drag an Invoke activity underneath the IMPORTXFORMEXPORTXML_PROC activity in your diagram. Drag the arrow to the right of the Invoke activity onto the InvokeODI partner link on the right. The Edit Invoke Wizard appears.

 

 

 

2.

In the Name field, enter IMPORTXFORMEXPORTXML. From the Operation drop-down list, select invokeScenario. Click the Magic Wand icon next to the Input Variable field. In the Create Variable window, change the variable name to InvokeODI _InputVariable . Click OK.

 

 

3.

Similarly, click the Magic Wand icon next to the Output Variable field. In the Create Variable window, change the variable name to InvokeODI _OutputVariable. Click OK. In the Edit Invoke dialog box, click OK again. Your diagram should look like the following screenshot.

 

 

Back to Topic List

Setting the Variable Value to Invoke the ODI Scenario and Building an XSL Transformation to Map Values into the Expected Fields

To initialize and set the input variables to invoke the correct ODI scenario, perform the following steps:

1.

Repeat the steps from Example 3 where we used a Transform activity to set these values. From the Components Palette at the right, drag a Transform activity onto the diagram above the IMPORTXFORMEXPORTXML invoke activity that you added in the last step. Double-click the Transform_2 activity. The Transform Wizard appears. From the Source Variable drop-down list, select InvokeODI_POC_InputVariable and from the Target Variable drop-down list, select InvokeODi _InputVariable. Click the Magic Wand icon next to the Mapper File field to create the mapping. Transformation_2.xsl opens in the main window.

Note: For this example, we will set ExportXMLFileLoc statically (we are setting only one variable).

 

2.

In the Source:OdiInvoke?wsdl schema, right-click the <source> node and select Expand All. Similarly, in the Target:OdiInvoke?wsdl schema, right-click the <target> node and select Expand All. Set the JdbcDriver node in the target schema. Because this is already set correctly in the source, drag the JdbcDriver node from the source and drop it onto the JdbcDriver node in the target.

 

3.

For the ScenName field in the target, the values in the source schema are not correct, and must be modified before being used. You will set these statically. Right-click ScenName in the target schema on the right and select Set Text. The Set Text window appears. In the Text field, enter SCEN_IMPORTXFORMEXPORTXML, and then click OK.

 

4.

Set the values for all the nodes in the following table. For some, you will need to use the Set Text function (as you did for ScenName). For others, you can map the same node from the source schema (as you did for the JdbcDriver node). Refer to the following table. After you have completed the mappings, your Transformation_2.xsl file should look like the following screenshot. Select File > Save All from the main menu.

 

RepositoryConnection
JdbcDriver Map from source

Your JDBC driver for the Master repository

JdbcUrl Map from source

Your JDBC connect URL for the Master repository

JdbcUser Map from source The Master repository schema or user
JdbcPassword Map from source

oracle1, the Master repository schema password

OdiUser Map from source

The ODI user

OdiPassword Map from source The ODI user password
WorkRepository Map from source

The Work repository name in ODI (not schema name)

Command
ScenName

SCEN_IMPORTXFORMEXPORTXML

ScenVersion

Map from source

Context Map from source
LogLevel Map from source
SyncMode

Map from source

SessionName SCEN_IMPORTXFORMEXPORTXML
Variables > Name BPELXFormWithODI.ExportXMLFileLoc

Variables > Value
(including quotes)

“..\demo\xml\GEO_DIM_OUT_BPEL.xml”
Agent
Host

Map from source

Port

Map from source

 

 

Back to Topic List

Deploying the Updated BPEL Process

You should now be ready to deploy and test your updated BPEL process.
Note: In its current state, it will poll the <ODI_Home>/oracledi/demo/file directory for a file with a name that matches Source*.xml. It will then invoke the SCEN_ImportXFormExportXML_Proc scenario passing the picked up file and the static values through to ODI. It will then invoke the ODI scenario SCEN_ImportXFormExportXML. It assumes that the GEO_DIM.dtd, GEO_DIM.xml, and DIM_GEO_LOOKUP.csv files exist at the correct locations .

Note: Ensure that your ODI Agent is running. If not, you can start it: Start > All Programs > Oracle > Oracle Data Integrator > Agent.

To deploy the updated BPEL process, perform the following steps:

1.

In the Applications Navigator on the right, right-click your project (SourceFileMonitor), and then select Deploy > SoademointegConnection > Deploy to default domain. If you are prompted to enter a version number, leave it as 1.0, and then click OK.

 

 

2.

In the logging window at the bottom of the page, click the Apache Ant tab. Verify that Build Successful is displayed in green when the process has finished deploying.

Note: If you see any errors and/or your process fails to deploy, revisit the above steps to correct the errors.

 

 

 

Back to Topic List

 

Testing the Updated BPEL Process

To run the BPEL process, perform the following steps:

1.

Open a browser and enter http://localhost:8888/BPELConsole as the URL. The Login page appears. Enter your application server credentials. Enter oc4jadmin in the Username field and welcome1 in the Password field. Click Login. The main BPEL Dashboard appears. Verify that your SourceFileMonitor BPEL project appears under the Deployed BPEL Processes column on the left.

 

2.

To start the SourceFileMonitor BPEL process, open the Source1.xml file that you created in <ODI_Home>/oracledi/demo/file in Example 3 in a text editor. To update its modified date, save the file.

Note: To let the system pick up the file again, you need to update the file's modified date because you configured the File Adapter not to delete or archive the file after we read it. Updating the file's modified date enables the File Adapter polling process again. Make sure that the text editor you use doesn’t save the file with control characters (such as end-of-line characters, and so on) because XML is sensitive to this.

 

3.

Allow 10 seconds for the File Adapter to pick up the file. Return to your BPEL Console. At the bottom of the Dashboard page, there is a region titled Recently Completed BPEL Process Instances. A new instance of SourceFileMonitor should appear here.

4.

Click the instance link of SourceFileMonitor to open in the Flow view. View the execution of the process and verify that it ran as expected. Double-click the last activity in the process, the IMPORTXFORMEXPORTXML invoke step. The window that appears displays the values sent to the SCEN_IMPORTXFORMEXPORTXML scenario.

 

5.

At the bottom of the window, you should also see the value returned from ODI. If the Web service successfully ran the SCEN_IMPORTXFORMEXPORTXML scenario, you should see True returned in the OK field. To verify that the process completed successfully, check the <ODI_HOME>/oracledi/demo/xml directory for the GEO_DIM_OUT_BPEL.xml file (or the directory and file that you set in the ExportXMLFileLoc variable). If the file exists, the scenario was invoked by BPEL and completed its integrations and transformations.

Note: You can also verify the execution of the ODI scenario within ODI Operator. ODI Operator is also a good place to debug if errors occur. If you would like to initiate the process again, you’ll need to make sure that the Date Modified time stamp on the file that you drop into the polled directory is newer than the previous file that was picked up. This is because you configured not to delete or archive the file that we are monitoring with the File Adapter.

 

 

Back to Topic List

 

Summary

In this lesson, you learned how to:

 Verify the Prerequisites
 Create a New Project in ODI
 Insert a New Model for the XML File
 Insert a Model for the .csv Lookup File
 Import the IKM and LKM SQL to SQL KMs to Load and Integrate the XML File
 Create a New Interface to Transform the XML and Write to a New XML File
 Create a Project Variable to Set the XML Export File Location
 Edit SQL to SQL Append to Create an XML File on the Commit Step
 Create a New Package to Execute the INT_ImportXFormExportXML Interface
 Create a New Scenario to Be Executed from the ODIInvoke Web Service
 Create the New ImportXFormExportXML Procedure
 Create Options for the ImportXFormExportXML Procedure
 Create Detail Steps for the New ImportXFormExportXML Procedure
 Create Project Variables for the New Project
 Create a New Package to Declare Variables and to Execute ImportXFormExportXML Procedures
 Create a New Scenario to Be Executed from the ODIInvoke Web Service
 Create a New Project for the BPEL Process
 Define the File Adapter
 Insert the Receive Service to Receive Messages from the FilePoll Service
 Update the File Adapter to Collect Only the File Name and Location, Not the Contents of the File
 Create a Partner Link to Invoke ODI
 Create an Invoke Activity for the InvokeODI Partner Link
 Initialize the Variables Needed to Invoke the ODI Scenarios
 Build an XSL Transformation to Map Values into the Expected Fields
 Set the ImportXMLFileLoc ODI Variable
 Set the Scenario Variable Values
 Set the ExportXMLFileLoc ODI Variable
 Deploy and Run the BPEL Process
 Invoke the ODI Web Service
 Set the Variable Value to Invoke the ODI Scenario
 Build an XSL Transformation to Map Values into the Expected Fields
 Deploy and Test your BPEL Process

 

Back to Topic List

 Place the cursor over this icon to hide all screenshots.

 

 

 

Left Curve
Popular Downloads
Right Curve
Untitled Document