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.
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.
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.
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:
Oracle SOA Suite 10g (10.1.3.1.0)
Oracle Database 10g XE
Oracle Data Integrator 10g (10.1.3.4)
Oracle JDeveloper 10.1.3.1 Studio Edition
The Oracle Data Integrator 10.1.3.4.2 patch from Metalink.
To obtain this patch, perform the following steps:
Go to http://metalink.oracle.com.
Log in with your username and password.
Click the Patches and Updates tab, and then
click Simple Search.
From the Search By drop-down list, select
Product or Family and enter odi.
Click Go to download.
The Axis2 1.2 framework. To install this, perform
the following steps:
Go to http://www.mirrorgeek.com/apache.org/ws/axis2/1_2/axis2.war
and download axis2.war to C:\temp.
Deploy the axis2 application to OC4J using the
following command: java -jar admin_client.jar
deployer:oc4j:opmn://localhost:6004/as1013/home oc4jadmin oracle1
-deploy -file c:\temp\axis2.war -deploymentName axis2 -contextRoot
/axis2.
Access the Axis2 page by using the URL http://localhost:8888/axis2/axis2-web/index.jsp.
Log in using admin as the User name and axis2 as the Password.
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.
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.
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.
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.
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:
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.
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.
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 DataIntegrator > 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.
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.
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_ImportXFormExportXMLdeclares 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 LOAD 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.
(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.
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.
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.
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 stepCreateExportXMLFileFromMemory. 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
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.
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 DataIntegrator > 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.
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.
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.
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.
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.
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.
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 ApplicationsNavigator, 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:
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 LinkWizard 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.
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 ImportDTDFileLoc 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:
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.
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:
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 ‘"' 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 ‘"' 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.
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 AppendOperation 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.
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.
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.
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.
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.
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.
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 matchesSource*.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.
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.
Bookmark
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.)
