SOA 12c - DB Adapter (Execute Pure SQL)

Here we go!
Information that you need to know:

Pure SQL Adapter is an option in the Oracle Database Adapter Wizard that allows you to type the SQL string directly and have an XSD/Web service generated automatically. The database tables are introspected dynamically in the Adapter Configuration Wizard to test the SQL and populate the XSD file better (that is, with valid return types.) The Pure SQL support allows the Oracle Database Adapter to deal with tables/views as entities and for dealing directly with SQL. You can use Pure SQL:

  • for simple data projection style report queries
  • in cases where the result set is not table oriented, such as select count(*)
  • to perform an update or delete all
  • when working with XMLType columns and xquery
  • when using complex SQL, which are not modeled in the Adapter Configuration Wizard expression builder

You can use the Pure SQL Adapter with Oracle XMLTypes. It is a natural fit for inserting XML into XMLType tables and columns, and retrieving XML using xquery selects. Pure SQL is a natural fit for the Oracle Database Adapter that provides a relational-xml mapping that parallels XML DB(XDB) support. So, when using XDB the adapter should be as lightweight and transparent as possible, to let you focus on XDB and XQuery.For more detailed information read the following URL: https://docs.oracle.com/html/E10231_03/adptr_db.htm#BDCDJDGC

In this post, we will learn how create a SOA Project with Database Adapter - Operation Type: Execute Pure SQL

The next images are the SOA Project execution:

The first step is create a database table.

Note: The next table is used just to make use of COUNT function.

For example:

Once created the table in the Oracle Database, we are going to open JDeveloper IDE and click on File menu > New > Application.

For example:


On the Application window, set the next values, and click on next button.
Application Name DatabaseAdapter
Directory C:\JDeveloper\mywork\DatabaseAdapter
Application Package Prefix

For example:


On the Project window, set the next values, and click on next button.
Project Name  ExecutePureSQL
Directory C:\JDeveloper\mywork\DatabaseAdapter\ExecutePureSQL

For example:


On the Configure SOA settings window, set the next values, and click on finish button.
Standard Composite  Empty Composite

For example:


After clicking on finish button, the JDeveloper IDE has the next appearance:

Then the next step, on External References section right click > Insert > Database, then enter the dbExecutePureSQL value on Service Name, then click on next button.

For Example:

Select the Oracle JDBC connection, then click on next.

Note: If you need additional information about how create a Oracle JDBC connection click on the next URL: Create Oracle DB Connection

For Example:

Then on Operation Type window, chose the Execute Pure SQL option, then click on next button.

Then on Custom SQL window, add this SQL statement:
SELECT COUNT(*) as COUNT FROM XXISC_FGG_EMPLOYEES
Click on ok button, then next button.

For Example:

Then on Advanced Options window, we keep the same values settings by default, then click on next button.

For Example:

Then on JCA Endpoint Properties window, we keep the same values settings by default, then click on next button.

For Example:

Then on Finish window, click on finish button.

For Example:

Then the next step, we need to select the BPEL Process component, this component is located on the right side, on Components section, when you found this, select it and drag it to Components section on work area.

For Example:

On the Create BPEL Process window, set the next values, and click on ok button.

BPEL Specification BPEL 2.0 Specification
Name BPELExecutePureSQL
Namespace http://xmlns.oracle.com/DatabaseAdapter/ExecutePureSQL/BPELExecutePureSQL
Directory C:\JDeveloper\mywork\DatabaseAdapter\ExecutePureSQL\SOA\BPEL
Template Type WebService
Template Asynchronus BPEL Process
Service Name bpelexecutepuresql_client
Expose as a SOAP Service Checked
Delivery async.persist
Transaction async.persist
Input {http://xmlns.oracle.com/DatabaseAdapter/ExecutePureSQL/BPELExecutePureSQL}process
Output {http://xmlns.oracle.com/DatabaseAdapter/ExecutePureSQL/BPELExecutePureSQL}processResponse

For example:

Now we need drag to add a new Reference, with this action we will have available the Partner Links on .bpel file.

For example:

Now we are going to do double click on component: BPELExecutePureSQL, then on BPELExecutePureSQL.bpel file, we need to select the Invoke activity and drag to work area, just in middle of receiveInput and callbackClient and change the name from Invoke1 to InvokeExecutePureSQL

For example:

Then we need to drag onto a Partner Link to make a Web Service connection. On the Edit Invoke window, set the next values, and click on ok button.

Input Variable dbExecutePureSQLInput
Type {http://xmlns.oracle.com/pcbpel/adapter/db/DatabaseAdapter/ExecutePureSQL/dbExecutePureSQL}dbExecutePureSQLInput_msg
Output Variable dbExecutePureSQLOutput
Type {http://xmlns.oracle.com/pcbpel/adapter/db/DatabaseAdapter/ExecutePureSQL/dbExecutePureSQL}dbExecutePureSQLOutput_msg

For example:

Then click on Apply button, next click on Ok button, then click on Save All button.

Now in this moment we have the Invoke activity to Database Web Service, the next step is create a new variable and assign to it the value of dbExecutePureSQLOutput variable.

We need to drag the Assign activity to work area after of InvokeExecutePureSQL component.

Then change the activity assign name from Assign1 to AssignCallback.

For example:


Now we need to double click on AssignCallback activity.

On the Edit Assign window, we need do the following, and do click on Ok button.

1.- Create the new variable with name countVariable
2.- Set the next values.

$countVariable $dbExecutePureSQLOutput.dbExecutePureSQLOutput/ns2:COUNT
$outputVariable.payload/client:result string($countVariable)

For example:

Now the next step is create a jar file to can deploy it on WebLogic Server, we need to select the ExecutePureSQL project and righ click, then chose the ExecutePureSQL... option.



On the Deployment Action window, set the next values, and click on next button.
Deployment Action  Generate SAR File

For example:


On the Deployment Configuration window, set the next values, and click on next button.
New Revision ID  1.0

For example:


On the Deployment Summary window, click on Finish button.
For example:


If all steps were successful, then you will view on yours JDeveloper IDE the correctly deployment.
For example:

Comments

Post a Comment

Popular posts from this blog

SOA 12c - Fault Handling (Creating Fault Policies and Fault Bindings)

Image
Here we go! Information that you need to know: Using Fault Handling in a BPEL Process. Fault handling allows a BPEL process service component to handle error messages or other exceptions returned by outside web services, and to generate error messages in response to business or runtime faults. For more detailed information read the following URLs: https://docs.oracle.com/cloud/latest/soa121300/SOASE/GUID-FE546F09-E9DF-4859-AEF6-A1C7A3F40C75.htm#SOASE478 Fault management framework is used to catch faults and perform user-specified actions defined in a fault policy file. In SOA Suite 12c we have a wizard to create fault policies and fault binding files. In this post, we will learn how to add a Fault Policy in an SOA project. We need to start the JDeveloper IDE. Click on File menu, then click on New option, followed immediately by Application option. For Example: Then on the New Gallery window, choose Applications option in General Category, then choose SOA Application ...
Read more

SOA 12c - DB Adapter (Poll for New or Changed Records in a Table)

Image
Here we go! Information that you need to know: Polling Strategies The inbound receive enables you to listen to and detect events and changes in the database, which in turn can be the initiators of a business process. This is not a one-time action, but rather an activation. A polling thread is started, which polls a database table for new rows or events. Whenever a new row is inserted into the MOVIES table, the polling operation raises it to the SCA Run Time. The strategy is to poll every record once. The initial SELECT has to be repeated over time, to receive the rows that exist at the start and all new rows as they are inserted over time. However, a new row once read is not likely to be deleted, and therefore can possibly be read repeatedly with each polling. The various ways to poll for events, called polling strategies, also known as after-read strategies or publish strategies, range from simple and intrusive to sophisticated and nonintrusive. Each strategy employs a dif...
Read more

SOA 12c - DB Adapter (Perform an Operation on a Table)

Image
Here we go! Information that you need to know: Data manipulation language (DML) operations align with basic SQL INSERT , UPDATE , and SELECT operations. SQL INSERT , UPDATE , DELETE , and SELECT are all mapped to Web Service operations of the same name. The MERGE is either an INSERT or UPDATE , based on the results of an existence check. A distinction is made between the data manipulation operations—called outbound writes—and the SELECT operations—called outbound reads. The connection between the Web Service and the SQL for merge (the default for outbound write) and queryByExample are not as obvious as for basic SQL INSERT , UPDATE , and SELECT . For more detailed information read the following URL: https://docs.oracle.com/cd/E28280_01/integration.1111/e10231/adptr_db.htm#CHDFGBGD In this post, we will learn how create a SOA Project with Database Adapter - Operation Type: Perform an Operation on a Table The next images are the SOA Project execution: We are going...
Read more