Oracle Apps Knowledge Sharing

Tweet

 1. Overview

While developing a BPEL project we would be faced with so many situations here we need to interact with Database, use FTP, or to read or write a file to local system. For such sort of situations the JDeveloper as provided us with good number of Adapters, through these Adapters we can easily interact with external environment.

Almost all the adapters generate a XML schema once we create them. As XML is understood by all environments, interaction between different environments is simplified.

In JDeveloper we have some eleven adapters like Database Adapter, FTP Adapter, File Adapter, Oracle Application Adapter, JMS Adapter and many more.

This document deals with Database Adapter only.

What is Database Adapter?

Database Adapter is a tool provided by JDeveloper to interact with standard Database. This Adapter is having four different modes of interacting. Based on our requirement we can select a mode and use it to meet our requirement.

Types of Database Adapter

JDeveloper provides four types of operations that can be done using the database Adapter. Those are:

• Call a Stored Procedure or Function.
• Perform an Operation on table. (Which includes operations like insert, delete, select.)
• Poll for New or Changed Records in table.
• Execute Custom SQL

Below links will help you to understand the above mentioned four different operations of a Database Adapter….

1. How to create a BPEL Project  and a database adapter? 

http://www.shareoracleapps.com/2012/02/create-project-and-database-adapter-in.html

2. How to configure a Database Adapter to Call a Stored Procedure or Function?

http://www.shareoracleapps.com/2012/02/bpel-calling-stored-procedure-using.html

3. How to configure a Database Adapter to  poll for New or Changed records in table?

http://www.shareoracleapps.com/2012/02/bpel-creating-database-adapter-for.html

4. How to configure a Database Adapter to  Perform an Operation on table. (Which includes operations like insert, delete, select.)

http://www.shareoracleapps.com/2012/02/database-adapter-to-performs-simple.html

5. How to configure a Database Adapter to Execute Custom SQL

http://www.shareoracleapps.com/2012/02/database-adapter-to-handle-custom-sql.html

Tweet

1. For executing a custom SQL Statement, select the fourth option in the below screen and click ‘Next’.

2. In next step you have to specify the custom SQL statement to be used, for this automatically a XML Schema Definition is generated. Click ‘Finish’ button, this completes the creation of database adapter.

3. Once you have created any Database Adapter and clicked ‘Finish’ button, we will get the below screen. It populates data for WSDL File location, Partner Link Type, and Partner Role.

I hope it helped you guys… Enjoy Sharing…

Tweet

You might be having a requirement wherein we need to update a table or perform some action if ever there is some change in some specific table. For achieving these sort of requirements BPEL provides us with Database Adapter with a polling option.

1. Once you have dragged the database adapter in to the work area and giving the name of the adapter and connection name, you can find the below screen where you have to select ‘Poll for Changed records in Table’.

2. Once you click ‘Next’ you are asked to import the tables to be used. Once you have selected the tables to be imported, click OK.

3. In this step we have to specify the root table from the list of tables that we selected.

4. If ever you want to have some relations between the tables then you can create them in next step. Once you click on Create button then the following screen appears where you have to give the details of the relation you want to create like the parent table name, Child table name, and type of relation and so on. Once this is done press OK.

5. In the next step you can select the columns that you want to exclude in Queries. Click next.

6. Now in current step you have got a screen where in you have to select the operation to be done after table has been polled. The appropriate where condition is generated based on your selection in the following screen.

a. Delete the Row(s) that were Read: By using this, the rows are completely deleted. This scenario is most useful if ever the size of table is very large and you cannot afford to increase the number of rows in it. By doing this the efficiency of the table is increased. You can use this if ever the data being stored is not so important.

Once you have selected this option the next screen asks you the frequency of Polling, and other criteria. Now click ‘Next’.

According to the data given the final SQL statement is generated. You have an option to edit the SQL statement generated also.

b. Update a field in Table (Logical Delete): This is most widely used scenario. You can opt for this if ever you don’t want to check for changes in the same old rows but also cannot afford deleting these rows. Here we have a field in which we try to toggle the values from some constant to other constant. You will poll only on the rows which have some constant and once it is checked we will toggle that value.

c. Update a Sequencing Table: This is used to update a sequencing table based. Sequencing table feature can be used when there is a field whose value will keep on increasing E.g. Primary Key, creation date or last update date. New table will be created where last read value of the field is stored. Adapter checks for the changes based on the value in the custom table.

d. Updating different sequencing table in Another Database: This is used to update a different sequence table in another database. For this you need to give the database name required. This feature can be used when user doesn’t have access to create a new table in the host database.

e. Update Sequence File: This option works similar to above cases but uses a file on the server machine.

Tweet

In this post, let us see how to access a database to execute a simple query like insert, update, select, or delete; BPEL provides a direct method through which we can directly interact with database without any Queries. We just need to specify the table name and the operation to be performed.

Once you have selected the Database connection or created a new database connection the next step is selection of type in which we can interact with Database. In that list we have to select the second option of performing an operation on database.

In this type, we have multiple combinations of simple Operations from which we can check the required operations and use them directly without any Queries. The screen looks as given below.

1. Once we select the required operations and click next, we will have a screen asking us to import the tables from database which we want to access. You get a dialogue box asking for a filter for name based on which the tables would be displayed in the left panel. User has to select the required tables and click on arrow mark then the tables would be displayed on right side. Now once user clicks OK the tables in the right panel are imported.

2. If you selected multiple tables then JDeveloper asks for the master table in next step. Once you have selected the master table in next step you have to give the primary key on that master table.

3. If you want to have some relations between the tables then you can create them in this step. Once you click on Create button then the following screen appears where you have to give the details of the relation that you want to create like the parent table name, Child table name, and type of relation and so on. Once this is done press OK.

4. In next step, you can select the columns that you want to exclude in Queries. Once you click finish, the creation of Database adapter for a simple operation on table is completed.

 

Tweet

 

Let us first understand how to create a simple Project. Then we will see how to create an adapter.

Creating a Project

1. Right click on the "Applications” and select “New Project” as shown in below Screen shot.

1. In “New Project“ dialogue box select “BPEL Process Project” and click OK.

Note: We have three types of Projects, they are

a) Synchronous Process

b) Asynchronous Process

c) Empty Process

In our case we will be using empty Process.

1. Give a name for your Project and select “Asynchronous BPEL Process” for Template as shown in the screen shot, and click Finish.

1. Now on right side of JDeveloper we have a Component Palette. In this Palette select “Services”. By this a list of services are displayed from which select “Database Adapter” and drag it into the workspace as shown in the screen shot. Now Database Adapter Wizard starts.

Note: If ever it is not present then go to the “View” option in menu bar and select Component

Palette.

1. Once you click on next button in the wizard, you are asked to give the name for the Database Adapter. In this case give “DatabaseAdapter” and click ‘Next’.

1. In this step, you have to select the database that you want to interact. If ever you are interacting with a new database then click on “New” button by which Database Connection Wizard starts.

Creating a Database Connection

Database Adapter Wizard is a tool provided by the JDeveloper where in we can easily create a database adapter by providing minimum number of details like connection of database to be used, and table names.

For using this wizard you are required to create a database connection by providing the appropriate credentials. We have two methods of doing this. First one is creating the connection before hand by going into “Connections Navigator” and creating a new database connection by right clicking on Database option.

The second method is while creating a database adapter. This is explained below. You can follow the same steps while creating a connection using method one also.

1. Once the Database connection wizard starts click on next button.
2. In step 1, you have to give a name for the connection and “connection Type”. Mostly the connection type is defaulted to Oracle (JDBC).
3. In step 2, give the user name and password through which you interact with the Database. “Role” field is optional so you may leave it. After you give these details press Next.

1. Now, in step 3, we have to specify various details for the Database connection to establish as shown in the screen shot.

Note: The screen shot below shows the connection details for apps database.

1. Then Test connection as shown in the wizard and once the Success message is displayed click “Finish”. This completes the creation of Database Connection.
2. Once you have created the database connection, select its name from the drop down list. All other fields of the form are displayed automatically. Check if the JNDI name is populated.

Tweet

1. Select Call a Stored Procedure or Function option from the Wizard and click ‘Next’.

2. This is the form where we have to specify the schema type and name of the Procedure. Select ‘Apps’ for Schema from the drop down list.

3. Click on the Browse button for selecting the procedure you want to call.

4. ‘Specify Stored Procedure’ Window is opened where you have to select the procedure to be called. All the procedures present in the database are displayed according to package structure. Those procedures which are not included in any package are displayed at the end under the root node directly. You can give search criteria based on a key string also. Once selected click OK.

5. Once the procedure is selected appropriate schema is created which you can find by navigating to Application tab. There under your project, in integration Content -> schema, you can find the schema. Double click on that in order to open it.

6. Click on ‘Next’ and then Finish. This completes the creation of database adapter. You will find the below screen where in the Partner Link type and partner role are populated. Click OK to complete the creation of database adapter.

Tweet

1. A synchronous process
2. An asynchronous process
3. An empty process

• A client PartnerLink representing the client who initiates this BPEL process as a service. This is shown as the client in the diagram.
• A receive activity, called receiveInput, to receive a request message from the client.
• A reply activity, called replyOutput, for the BPEL process to return a synchronous response message to the client.

• A client PartnerLink representing the client who initiates this BPEL process as a service. This is shown as the client in the diagram.
• A receive activity, called receiveInput, to receive a request message from the client.
• A invoke activity, called callbackClient, for the BPEL process to return asynchronous response message to the client.

Tweet

What are Adapters?

Adapters are used for efficient integration with back-end applications to optimize the business process. Adapters support a robust, light-weight, highly scalable, and standards-based integration framework, which enables disparate applications to communicate with each other. They are used to integrate packaged applications, legacy applications, databases, and Web services. Using adapters, the user can ensure interoperability by integrating applications that are heterogeneous, provided by different vendors, based on different technologies, and run on different platforms.­­­

The following types of technology adapters are available in BPEL.

·         File/FTP adapter

·         Database adapter

·         Oracle Apps adapter

·         AQ adapter

·         MQ adapter

·         JMS adapter

File/FTP Adapter

The file/FTP adapters enable to configure the BPEL process to interact with local and remote file system directories. The file and FTP adapters can read and write the following file formats and use the adapter translator component at both design time and run time

·         XML (both XSD- and DTD-based)

·         Delimited

·         Fixed positional

·         Binary data

·         COBOL Copybook data

It can also treat file contents as an opaque object.
For inbound file, the file and FTP adapters perform the following operations

·         Poll the file system looking for matches

·         Read and translate the file contents based on the translation logic defined at design time

·         Publish the same as an XML message

For outbound files sent from Oracle BPEL Process Manager, the file and FTP adapters perform the following operations

·         Receive messages from BPEL

·         Format the XML contents as specified at design time

·         Produce output files

For inbound file and FTP adapters have parameters for the inbound directory where the input file appears, the file naming pattern and the frequency with which to poll the directory.

Database Adapter

The database adapter enables a BPEL process to communicate with Oracle databases or third-party databases through JDBC.

The following operations are supported in the Database adapter

• Call a Stored procedure or a function
• Perform a table operation which can be one of the following

Ø  Insert Only

Ø  Update Only

Ø  Insert or Update

Ø  Update

Ø  Select

• Poll for new or changed records in a table
• Execute Custom SQL

Before connecting to a database through the database adapter, the database connection must be created and configured for that particular database using the Database Connection Wizard.

Oracle Applications Adapter  

The Oracle Apps adapter enables to interact with various interfaces of an Oracle Apps instance that is hosted in a given database connection.

It offers a rich set of object invocation which includes concurrent programs, XML gateway, EDI sources, business events apart form tables, views and APIs which can be invoked even through the database adapter.

It offers support for open interface imports, and other such things that can be done in the Oracle Apps.

AQ Adapter

It provides a flexible mechanism for bidirectional, asynchronous communication between two applications that uses Queues as a medium to pass on information to each other.

The enqueue operation is outbound while the dequeue operation is inbound thus the AQ adapter is both the producer and consumer of the AQ messages.

Specify the operation whether it is an Enqueue or Dequeue and the queue name from which the message is to be enqueued or dequeued

Tweet

JPR

Build.xml file

Tweet

Tweet