Using-DB-Adapters-Part-1-Inserting-into-table

This post will help you while creating DB adapters in a SOA composite.

Let’s take a scenario where we are creating an application for HR System with Sub-Module asAccessCardGeneration for a new Joiner.

Start node will be an initiator node which we will be exposed as Webservice. Now once we receive a Message from an external system [ assume a Dot net application which HRs use for creating employee Id], instance would be created.

Below is data association which we will put into our Start node. To pass of Data from Webservice Request to BPM’s Data Object.

Now the basic idea is to insert input received from Webservice into a DB Table and then return an access card number from Global-Free-Access-Card table. Once an access card is assigned to an Employee, we will remove that access-card-number from free table and insert entry for that access-card-number into Global-Assigned-Access-Card table along with corresponding Employee Id.

DB Scripts to create Basic Tables:

create table employee
(fullName varchar(15),
Email varchar(20),
EmployeeId number(10),
ContactNumber varchar(12),
DateOfJoining date
);

create table globalFreeAccessCards
(AccessCardNumber number(10)
);

create table globalAssignedAccessCards
(AccessCardNumber number(10),
EmployeeId number(10)
);

So as per our requirement , we need to execute three DB operations with Database.

· Insert Webservice Data into employee Table

· Select a Free Access Card Number from globalFreeAccessCards Table and then Delete selected Access-card-number from table.

· Update Selected Access Card Number into globalAssignedAccessCards Table with Assigned Employee-Id.

We will not create three DB adapters, once each for above mentioned respective table.
Steps:
à Open Composite.xml
à Drag and Drop Database Adapter on External-references Section

image003

image004

à As of now, we will use Jdeveloper DB connection which would be deployed as a Data source @ Weblogic server. Ideally we should create a Data source into Weblogic and then map the JNDI name into DB adapter.

àAs of now, we need to insert a row into Employee Table, so will only select insert operation.

à Click on Import- Tables and select Employee Table.

àSet EmployeeId as Primary Key

image008

image009

Now we need to go back again to BPMN process and define implementation for Service Task ‘Insert Employee Record Into Database’.
To pass on Payload-DataObject – Employee’s Value into DB, we will go to transformation section and map Input through XSL.

–> Below is the XSL mapping which will pass on Payload data into Employee Table.

In my next Post, I will show, how to execute Select and Update through DBadapters and process deployment with Sample Code.

Upgrading SOA from 11.1.1.5.0 to 11.1.1.7.0

Upgrading SOA from 11.1.1.5.0 to 11.1.1.7.0

1 General Pre-Patching Tasks

1.1 Stopping the Servers and Processes
Before you run the installer to update your software, you should stop all servers and
processes (including OPMN) on all machines which are part of any domain used from
the Middleware home.

1.2 Backing Up Your Middleware Home, Domain Home and Oracle Instances
After stopping the servers and processes, back up your Middleware home directory
(including the Oracle home directories inside the Middleware home), your local
Domain home directory, your local Oracle instances, and also the Domain home and
Oracle instances on any remote systems that use the Middleware home.

1.3Backing Up Your Database and Database Schemas
If your database schemas require updating, you should also back up your database
before you begin the patching procedure.

2 Patching Oracle Fusion Middleware

Starting the Installer
export ORACLE_HOME=/u02/soasitap/sitap/FMW_HOME/Oracle_SOA
[test@xxxxxxx Disk1]$ pwd
/u02/soasitap/upgrade_patches/Disk1
[test@xxxxxxx Disk1]$ ls
doc install runInstaller setup.exe stage
[test@xxxxxxx Disk1]$ ./runInstaller

Skip the updates

Click next

Select the existing SOA Home and click Ok.

Select Weblogic server

Click Install.

Click Finish.

====================================================

3 Updating Your Schemas with Patch Set Assistant
The Patch Set Assistant is used in patch set releases only to update the database
schemas of Oracle Fusion Middleware components.

3.1 Before You Begin Using the Patch Set Assistant

Check Your Database and Schemas

SQL> SELECT owner, comp_name, version, status, upgraded FROM schema_version_registry;

OWNER COMP_NAME VERSION STATUS U
————— ——————– ———- ———– –
B2B_ORABAM BAM Services 11.1.1.4.0 VALID N
xxxxxxxCRP_ORABAM BAM Services 11.1.1.4.0 VALID N
SIT_ORABAM BAM Services 11.1.1.4.0 VALID N
OTMPRE_ORABAM BAM Services 11.1.1.4.0 VALID N
MPCB2B_ORABAM BAM Services 11.1.1.4.0 VALID N
xxxxxxxCRP_IAU Audit Service 11.1.1.4.0 VALID N
MPCB2B_MDS Metadata Services 11.1.1.5.0 VALID N
B2B_MDS Metadata Services 11.1.1.5.0 VALID N
xxxxxxxCRP_MDS Metadata Services 11.1.1.5.0 VALID N
SIT_MDS Metadata Services 11.1.1.5.0 VALID N
OTMPRE_MDS Metadata Services 11.1.1.5.0 VALID N
B2B_ORASDPM SDP Messaging 11.1.1.2.0 VALID N
xxxxxxxCRP_ORASDP SDP Messaging 11.1.1.2.0 VALID N
M
xxxxxxxCRP_SOAINFRA SOA Infrastructure 11.1.1.5.0 VALID N
Services

SQL> select count(*) from dba_objects where status=’INVALID’;

COUNT(*)
———-
10

SQL> @rdbms/admin/utlrp.sql

SQL> select count(*) from dba_objects where status=’INVALID’;

COUNT(*)
———-
0

3.2 Create an Edition on the Database Server for Editions-Based Redefinition
Not required

3.3 Setting ORACLE_HOME and JAVA_HOME Environment Variables
If you are running the Patch Set Assistant and your install does not contain an
Oracle WebLogic Server (standalone) or Java files, you must set the the JAVA_
HOME environment variable. PSA may not be able to detect the location of the
required Java files without setting this variable.

3.4 Running the Patch Set Assistant
The Patch Set Assistant is installed into the bin directory in the Oracle Common home
by the Product or Patch Set Installer.
You can use the Patch Set Assistant to patch any component schema in the
Middleware home from where the Patch Set Assistant started.

[test@xxxxxxx bin]$ pwd
/u02/soasitap/sitap/FMW_HOME/oracle_common/bin
[test@xxxxxxx bin]$ ./psa

You can only use Patch Set Assistant to update one top-level component at a time. In the figure above, notice that Oracle SOA is selected, which means Patch Set Assistant would update the SOAINFRA schema as well as the MDS schema, upon which the SOAINFRA schema is dependent. However, the top-level “Oracle Metadata Services” box is not selected even though that schema is going to be updated.

You must check all the boxes acknowledging that all prerequisites for upgrade have been met, or else you will be unable to continue to the next screen.

Use this screen to specify database connection credentials and schema information for upgrade. This screen appears once for each component schema that needs to be updated

3.5 Verifying schema version
After upgrade of schemas
OWNER COMP_NAME VERSION STATUS U
————— ——————– ———- ———– –
B2B_ORABAM BAM Services 11.1.1.4.0 VALID N
xxxxxxxCRP_ORABAM BAM Services 11.1.1.4.0 VALID N
SIT_ORABAM BAM Services 11.1.1.4.0 VALID N
OTMPRE_ORABAM BAM Services 11.1.1.4.0 VALID N
MPCB2B_ORABAM BAM Services 11.1.1.4.0 VALID N
xxxxxxxCRP_IAU Audit Service 11.1.1.7.0 VALID Y
MPCB2B_MDS Metadata Services 11.1.1.5.0 VALID N
B2B_MDS Metadata Services 11.1.1.5.0 VALID N
xxxxxxxCRP_MDS Metadata Services 11.1.1.7.0 VALID Y
SIT_MDS Metadata Services 11.1.1.5.0 VALID N
OTMPRE_MDS Metadata Services 11.1.1.5.0 VALID N
B2B_ORASDPM SDP Messaging 11.1.1.2.0 VALID N
xxxxxxxCRP_ORASDPM SDP Messaging 11.1.1.7.0 VALID Y

OTMPRE_ORASDPM SDP Messaging 11.1.1.2.0 VALID N
SIT_ORASDPM SDP Messaging 11.1.1.2.0 VALID N
MPCB2B_ORASDPM SDP Messaging 11.1.1.2.0 VALID N
xxxxxxxCRP_SOAINF SOA Infrastructure S 11.1.1.7.0 VALID Y
RA ervices

3.6 Checking for Invalid Database Objects

SQL> select count(*) from dba_objects where status=’INVALID’;

COUNT(*)
———-
0

4 Post-Patching Tasks

4.1 Updating the JDK
You will need to have the latest supported JDK version installed in your patched
environment.

4.2 Start the services
Now start the services by first starting the database & then start the domain.

5 Issues & Resolution

Issue: WSM-02557 oracle.wsm.policymanager.accessor.BeanAccessor
Resolution:
Log into EM then select the domain in use and use the following the menu path, WebLogic Domain> Web Services > Platform Policy Configuration

From there, select the Policy Accessor tab., then click Add to add a property.

In the Add Property window, specify the following values:

a) In the Name field, enter this property: java.naming.provider.url.

b) In the Value field, enter this t3://host:port.
Example, in the case of the test-pc environment : t3://xxxxxxx.xxxxxxx.com:8021

8001 is the managed server http port where the policy manager exists. Therefore, make sure you enter t3://yourHostName:yourHTTPManagedServerPort

c) Click OK. then Apply to accept the changes.

Once the changes have been accepted in the system, you would need to restart the servers in order for the change to be recognized.
Refer Enterprise Manager Cannot Invoke WS Policy Manager When Managed Servers SSL Port Is Enabled (Doc ID 1481062.1)

Issue: Hostname verification error
Resolution:
• change the file
$DOMAIN_HOME/config/fmwconfig/policy-accessor-config.xml
and change the property to ,
false

After this restart the Admin Server and SOA Server and check.

• Demo trust certificates where reverted back with old certificates
• Keystores where reimported to the domain.
• Configuring Oracle WebLogic Server (10.3.X) to use SSL in Fusion Middleware 11g (11.1.1.X) (Doc ID 1235653.1)