File to DB in BPMN

If you have already completed the previous exercise now you are ready to go ahead do some more experiment on the BPMN.

In this exercise we will try to read a data from a file and will write the data in to a table in db.

Create a new Project



select manual process



Once process is created delete the user task so that now the process looks like this




Right click on reference and create a file adapter service to poll record from a location in your local machine

You can point it to following schema

<?xml version= '1.0' encoding= 'UTF-8' ?>
<schema xmlns="http://www.w3.org/2001/XMLSchema"
targetNamespace="http://xmlns.oracle.com/purchase/order"
xmlns:xsd="http://xmlns.oracle.com/purchase/order"
elementFormDefault="qualified">
<element name="PORequest" type="xsd:PORequestType"/>
<complexType name="PORequestType">
<sequence>
<element name="OrderNumber" type="string" minOccurs="1"/>
<element name="ProductName" type="string" minOccurs="0"/>
<element name="ProductDescription" type="string" minOccurs="0"/>
</sequence>
</complexType>
</schema>


Complete the file adapter wizard and you should be able to see that in the reference

Create a table in your db

create table PO_DETAILS (order_number varchar2(50),OrderDetails varchar2(50),orderCode varchar2(50));

And go to Services tab in Business catalog and create a db adapter service and point it to insert data in the db table you have created.

After creating both the adapters it should be visible in your Business catalog



Now go to your BPMN process

Right click on start and go to implementation

Choose type as Use Interface and select the File Adapter that you have created to read the file from a location in your local machine.



Drag and drop a SErvice task and insert it between the start and stop activity.

Go to implementation table select type as Service call

and select the db adapter service that you have created to insert data in to the table in the database.



Create a module "DataModule" for business object.

Create two business Object for File input and DB input based on the schema for file and db adapter schema.

Once business object is created create two PRocess data object based on the business object.



Now go back to your bpmn process

Go to Data Association and map the data from filein to dbIN



Go ahead and deploy your project.

Once process is deployed place the file in the location from where file adapter is polling.

As soon as the file is polled go to the em console

http://host:port/em

and check for the instance.

Checked the instance and found that the process is currently in suspended state




further dwelling in to the error go the following error message



auditQueryPayload auditId="2028" ciKey="30004">
<dataState>
<dataObject name="FaultMessage" isBusinessIndicator="false">
<value>
oracle.bpm.bpmn.engine.model.runtime.microinstructions.TrappableException: faultName: {{http://schemas.xmlsoap.org/ws/2003/03/business-process/}selectionFailure} messageType: {{http://schemas.oracle.com/bpel/extension}RuntimeFaultMessage} cause: {faultName: {{http://schemas.xmlsoap.org/ws/2003/03/business-process/}selectionFailure} messageType: {{http://schemas.oracle.com/bpel/extension}RuntimeFaultMessage} parts: {{ summary=
<summary>empty expression result. The expression bpmn:getDataObject('fileIn')/ns:ProductDescription is empty. An attempt to read or copy data referenced or computed by the XPath expression either had invalid data, according to the XML schema, or did not contain certain optional data. Ensure that the variable or expression result named in the error message is not empty. Enable XML schema validation of related data elements to ensure the run-time data is valid. </summary>
} }
</value>
</dataObject>
</dataState>
</auditQueryPayload>

The reason for the issue is that we have mapped the data from fileIn but there is no data in fileIN dataobject

We are receiving data in the argument of input activity so we need one more assignment from input argument to the fileIN data object that can be done in the start activity.

Go to the start activity.

Double click on it and go to implementation tab

Go to data association and map data from input argument to the fileIN as shown

Redeploy the process and test it.



Place one more file in the input directory and validate if the process is completed successfully



finally validate the database table if you can see the record