Configure Hermes JMS To Connect Websphere MQ 7.1

Hermes JMS is a good UI based tool to connect to JMS queues that allows all common operations like put, get, browse etc as well as some really rich features. Another plus point with Hermes is its vendor neutral! It can connected to almost any JMS based messaging system with minor configuration.

Recently I configured it with Websphere MQ 7.1. I did faced minor issue initially but later it went smoothly. So here are the steps -

• Install Websphere MQ on linux as provided in this link. You need to install MQSeriesClient as well to get the required jars which we will use later in Hermes configuration.
• Copy all the below jars from /<Websphere_MQ_Install_Folder>/java/lib to your local machine.

     com.ibm.mq
     com.ibm.mq.commonservices

     com.ibm.mq.headers

     com.ibm.mq.jmqi

     com.ibm.mq.pcf

     com.ibm.mqjms

     connector

     dhbcore

• Next open Hermes JMS and click configuration to setup new provider and classpath as shown in image below.

      

• Goto providers tab and right click to add a new group. Provide some name like "WMQ7".
• Now add all the jars to this group that we have copied from the Websphere MQ installation and click Ok.
• Create a new session and select the provider we created in last step. Also select the connection factory class - com.ibm.mq.jms.MQQueueConnnectionFactory.
• Add properties - hostname, port, queueManager, channel, transportType. TransportType value should be 1 if your are using TCP/IP for communication.
• Select "IBMWebsphere MQ" in plug-in drop down. Click ok and you are done! Below image shows the fully configured session to Websphere MQ.

NOTE - Make sure that channel name is in ALL CAPS.

Error that I faced while configuring are -

1. java.lang.NoClassDefFoundError: Could not initialize class com.ibm.mq.MQEnvironment
2. hermes.browser.dialog.ConnectionFactoryConfigPanel - Could not initialize class com.ibm.mq.jms.MQQueueConnectionFactory

It all went away after adding the correct jars from the Websphere MQ installation folder.

Setup MQ Queues & QueueManager - For Novice

People who have not worked much on Websphere MQ v7.1 find it tricky to setup queues and security (channels, authorization etc). I too being novice on Websphere MQ took me good time to configure and connect it from Java. Based on my experience mentioning here the steps that I followed to setup the things right on MQ and  I found it easy as well.

1. Install Websphere MQ v7.1 on linux. This will create a new linux user and group named 'mqm'.
2. Since MQ does not allow the users belonging to admin (mqm) group to connect to queues so lets also create linux user say 'mquser' and assign it to mqm group.
3. MQ requires you to first create a queue manager before queue. MQExplorer is eclipse based client program to administer MQ and a good UI based option for novice. So first create a queue manager say - "DefaultQM" with default parameters like port etc.
4. Next create one local queue say "FirstQ" that will be your first queue using explorer. 
5. A server connection channel is required to establish connection to server and access queues. This also can be created by explorer under IBM Websphere MQ > Queue Managers > [DefaultQM] > Channels.
6. Create a new channel authentication records as shown in the document MQChlAuthCreation.doc.
7. You will see a default channel authentication record that blocks all the MQADMIN users. Override this by executing the following command
   SET CHLAUTH('<server connection channel>') TYPE(BLOCKUSER) USERLIST('nobody')
8. more details can be found here. This command actually set unlock all users to establish connection to MQ. Though its not a recommended setting but it works for learning and r&d purpose.
9. Next write a java program to send and receive messages to/from MQ. This is easily available on many developer portal like this one - java program.

If everything is set correctly you will see a java program able to produce and consume a message to MQ. Please contact me in case you need document/java program.

Purge Script For Oracle SOA 11.1.1.6

SOA dehydration store can reach the tablespace limit over the time if not administered properly. Two options are available to deal with such scenarios -


Extend the tablespace which sometimes is not possible due to database/tablespace constraints or some other reasons.
Purge the instances from the dehydration store to free up some space.

Deleting the instances from EM console can be cumbersome if the number of instances to delete is large.
Fortunately Oracle SOA Suite 11.1.1.6 dehydration store purge scripts comes to the rescue. These scripts comes along with the setup and once you install the product it can be located at -

MW_HOME/SOA_ORACLE_HOME/rcu/integration/soainfra/sql/soa_purge directory

This purge script is only supported for Oracle database. In my case the database is Oracle XE 11g - 64 bit on Windows 7.
Here are the steps to follow before running purge scripts -
Connect as SYS user.
Execute following commands -

GRANT EXECUTE ON DBMS_LOCK to USER;
GRANT CREATE ANY JOB TO USER;
Next you need to create the packages,views and other objects that will delete the instances. For this login as DEV_SOAINFRA user and run the soa_purge_scripts.sql available at location MW_HOME/SOA_ORACLE_HOME/rcu/integration/soainfra/sql/soa_purge.
Make sure you run the above script in sql*plus (command line) from the above directory else the package will not compile properly. In sqlplus run with this command - SQL> @soa_purge_scripts.sql
Now connect as DEV_SOAINFRA user and run the delete_instances procedure by following block

DECLARE
   MAX_CREATION_DATE timestamp;
   MIN_CREATION_DATE timestamp;
   batch_size integer;
   max_runtime integer;
   retention_period timestamp;

  BEGIN

   MIN_CREATION_DATE := to_timestamp('2013-01-01','YYYY-MM-DD');
   MAX_CREATION_DATE := to_timestamp('2013-09-09','YYYY-MM-DD');
    max_runtime := 60;
    retention_period := to_timestamp('2013-09-09','YYYY-MM-DD');
   batch_size := 10000;
     soa.delete_instances(
     min_creation_date => MIN_CREATION_DATE,
     max_creation_date => MAX_CREATION_DATE,
     batch_size => batch_size,
     max_runtime => max_runtime,
     retention_period => retention_period,
     purge_partitioned_component => false);
  END;

You should see the tablespace has now some space available.

More details can be found at this document -
http://docs.oracle.com/cd/E23943_01/admin.1111/e10226/soaadmin_partition.htm

Business Rules : Points To Remember

You may find business rule really difficult to implement at first if you are not aware of business rule modelling rules. Listing here some points useful while implementing business rules. I will try to keep this list progressive as and when I discover new points.

Oracle BR matches the fact by types and not by their names. If there are multiple fact of the same type available in the working memory then it will not be able to resolve the fact to update and will throw below exception.

The RL function getFactByType found more than one instance of the fact type

Input facts are asserted by rule engine. Rest all facts needs to be asserted explicitly by assert or assert new expressions. Without asserting a fact you cannot work/perform any function on fact for e.g. modify or assign. There is one other way to make the output fact available in then condition. Assert output fact in initial tab of decision service and use advanced mode and declare variable as shown in image below.

Reclaim Space From SOA Tablespace

Below command is the way to reclaim the tablespace from SOA schemas.

alter table audit_trail deallocate unused;
alter table audit_trail enable row movement;
alter table audit_trail shrink space compact;
alter table audit_trail shrink space;
alter table audit_trail disable row movement;

alter table audit_details deallocate unused;
alter table audit_details enable row movement;
alter table audit_details shrink space compact;
alter table audit_details shrink space;
alter table audit_details disable row movement;

alter table cube_instance deallocate unused;
alter table cube_instance enable row movement;
alter table cube_instance shrink space compact;
alter table cube_instance shrink space;
alter table cube_instance disable row movement;

alter table cube_scope deallocate unused;
alter table cube_scope enable row movement;
alter table cube_scope shrink space compact;
alter table cube_scope shrink space;
alter table cube_scope disable row movement;

alter table dlv_message deallocate unused;
alter table dlv_message enable row movement;
alter table dlv_message shrink space compact;
alter table dlv_message shrink space;
alter table dlv_message disable row movement;

alter table dlv_subscription deallocate unused;
alter table dlv_subscription enable row movement;
alter table dlv_subscription shrink space compact;
alter table dlv_subscription shrink space;
alter table dlv_subscription disable row movement;

alter table document_ci_ref deallocate unused;
alter table document_ci_ref enable row movement;
alter table document_ci_ref shrink space compact;
alter table document_ci_ref shrink space;
alter table document_ci_ref disable row movement;

alter table document_dlv_msg_ref deallocate unused;
alter table document_dlv_msg_ref enable row movement;
alter table document_dlv_msg_ref shrink space compact;
alter table document_dlv_msg_ref shrink space;
alter table document_dlv_msg_ref disable row movement;

alter table headers_properties deallocate unused;
alter table headers_properties enable row movement;
alter table headers_properties shrink space compact;
alter table headers_properties shrink space;
alter table headers_properties disable row movement;

alter table work_item deallocate unused;
alter table work_item enable row movement;
alter table work_item shrink space compact;
alter table work_item shrink space;
alter table work_item disable row movement;

alter table wi_fault deallocate unused;
alter table wi_fault enable row movement;
alter table wi_fault shrink space compact;
alter table wi_fault shrink space;
alter table wi_fault disable row movement;

alter table xml_document deallocate unused;
alter table xml_document enable row movement;
alter table xml_document shrink space compact;
alter table xml_document shrink space;
alter table xml_document disable row movement;

Error while creating domain of SOA server

If you encountered below error while running RCU then it could be either because you have recently changed the network settings like ip, domain name etc of your machine or the network setup of the machine is not complete.

2010-11-09 15:14:47,750 ERROR [create_gui] com.bea.plateng.wizard.domain.gui.tasks.DomainCreationGUITask - Generation Error!!
Traceback (innermost last):
File "<iostream>", line 35, in ?
File "/mymwhome/wlserver_10.3/common/wlst/modules/jython-modules.jar/Lib/socket$py.class", line 338, in gethostname
gaierror: (20001, 'getaddrinfo failed')
...

To resolve this issue first check whether the machine can resolve its own ip and hostname -

$ ping <hostname>
$ ping <ip address>

check DNS name resolution

$ nslookup <hostname>

If the server cannot resolve its own ip or hostname then modify /etc/hosts file with entry

<ip address> hostname.domain hostname

This should solve the error. Rerun the RCU and it should go smooth.

DB based MDS and adf-config.xml

Version 11.1.1.6

JDeveloper can be used to connect to DB based MDS. The steps to create DB based MDS are -


Create DB connection to DEV_MDS.
Create MDS connection (DB based) while selecting the db connection created in first step.
Add that MDS connection to the repository and you are ready to refer the artifacts from DB based MDS.


Sometimes if adf-config.xml is not correct you may face below error while opening the application.

Error: oracle.fabric.common.FabricException: oracle.mds.config.MDSConfigurationException: MDS-01330: unable to load MDS configuration document

MDS-01329: unable to load element "persistence-config"
MDS-01370: MetadataStore configuration for metadata-store-usage "mstore-usage_2" is invalid.
MDS-00011: unable to create configuration object or MDSInstance due to invalid configuration information: oracle.mds.exception.MDSException: MDS-01373: unable to retrieve password.
MDS-01373: unable to retrieve password.


Make sure that mstore-usage is defined correctly. Pasting the correct adf-config.xml here -

<metadata-namespaces>
          <namespace path="/deployed-composites/default"
                     metadata-store-usage="mstore-usage_2"/>
          <namespace path="/soa/shared" metadata-store-usage="mstore-usage_3"/>
        </metadata-namespaces>
        <metadata-store-usages>
          <metadata-store-usage id="mstore-usage_2">
            <metadata-store class-name="oracle.mds.persistence.stores.db.DBMetadataStore">
              <property name="jdbc-userid" value="DEV_MDS"/>
              <property name="jdbc-password" value="password"/>
              <property name="jdbc-url"
                        value="jdbc:oracle:thin:@<ipaddress>:<port>:<SID>"/>
              <property name="partition-name" value="soa-infra"/>
            </metadata-store>
          </metadata-store-usage>
.....

How to Retrieve Payload From SOA Dehydration Store Using Java

Oracle has provided SOA facade and management api to retrieve the composite/instance details from the soa-infra database. List of composite instances can be retrieved based on any filter condition like between 2 dates, by instance id, by state etc. The API documentation is available at -

http://docs.oracle.com/cd/E21764_01/apirefs.1111/e10659/oracle/soa/management/facade/package-summary.html

And the detailed documentation is here -

http://docs.oracle.com/cd/E28280_01/admin.1111/e10226/soaadmin_apimanage.htm

I am listing here a code excerpt to retrieve the list of bpel instances between a give date. The actual code was to retrieve the payload that instantiated the bpel for each bpel instance in list.

First set the filter condition -

locator = LocatorFactory.createLocator(props);
CompositeInstanceFilter filter = new CompositeInstanceFilter();
filter.setMinCreationDate(minCreationDate);
filter.setMaxCreationDate(maxCreationDate);

then get the list of instances -

 List<CompositeInstance> compositeInstance =

                locator.getCompositeInstances(filter);



List<ComponentInstance> childComponentInstances;


get child component instances list by iterating through the list.

for (CompositeInstance compositeInstance1 : compositeInstance) {
                childComponentInstances =                        compositeInstance1.getChildComponentInstances(cInstanceFilter);
                                
System.out.println("Child list size -> " +                                   childComponentInstances.size()+" | name -"+compositeInstance1.getCompositeDN().getCompositeName());


iterate through childComponentInstances list to retrieve the details. AuditTrail can also be retrieved for a particular instance here in this loop.

for (ComponentInstance componentInstance1 :
                         childComponentInstances) {
System.out.println("++++++++++++++++ " +
                               componentInstance1.getComponentName() +
                                           "| Version - " + componentInstance1.getCompositeDN().getRevision() +
" | Instance ID - " +                                        componentInstance1.getCompositeInstanceId() +
" | " +                                        componentInstance1.getCreationDate());
}

}


For complete code please mail me.

Error While Throwing SOAPFaultException From Java Embedding to BPEL - "This class does not support SAAJ 1.1"

This post is valid for SOA 11.1.1.6.

Before looking at how to resolve "This class does not support SAAJ 1.1" error, here are some rules to consider while using java embedding activity in bpel.

• BPEL 2.0 syntax to import a java class is -

<import location="com.oracle.bpel.client.BPELFault" importType="http://schemas.oracle.com/bpel/extension/java"/>

• All custom jars must be added to the classpath and lib (SCA-INF/lib) folder of the soa project as shown in the image below.

• Java embedding can only invoke static methods from the custom jars else you will see an error "failed to compile excelet" while deploying soa project. Make sure that the function that you are invoking from custom jar is static function.

• BPEL understands only BPELFaults. Java exceptions must be caught within a java embedding try-catch block and inside catch block BPELFault needs to be raised like in the code excerpt below.

try {            

    String response = WebserviceHTTPClient.invoke(webserviceURL, operationName, 

                                            bodyContent);     

    addAuditTrailEntry(">>>>>>>> Response - "+response); 

} catch (Exception e){                          

    addAuditTrailEntry(">>>>>>>> Exception - "+ e.getMessage());      

    QName qName = new QName("http://schemas.oracle.com/bpel/extension", "remoteFault");      

     

    // create a new BPELFault exception      

    BPELFault bpelFault = new BPELFault(qName);      

     

    // set the details of the fault      

    bpelFault.setPart("code", "2001");      

    bpelFault.setPart("summary", "some error");      

    bpelFault.setPart("detail", e.getMessage());      

     

    // throw the fault      

    throw bpelFault;      

}

Now about how to resolve SAAJ version error. The solution is provided here but I am listing as a note for me as the scenario I encountered for this issue is slightly different -

Scenario - A custom jar method is invoked in java embedding which is throwing SOAPFaultException. Java embedding  catches the exception and try to build BPELFault. But the message retrieved from the exception caught shows - "This class does not support SAAJ 1.1" instead of the actual soap fault error message.

Resolution - Weblogic has a default SAAJ implementation in package weblogic.webservice.core.soap, that seems be causing this issue. Hence we need to override the properties to use a better implementation weblogic.xml.saaj  package. To override add this to your startWeblogic script and restart the server.

-Djavax.xml.soap.MessageFactory=weblogic.xml.saaj.MessageFactoryImpl

Earlier I have also encountered "There was no content-type header" message in java embedding catch block.  This was because the MimeHeader passed to build SOAPFault was null.

Configuring OUD on weblogic

Installing OUD and setup ODSM (Oracle Directory Service Manager) console is available at this link -

http://docs.oracle.com/cd/E22289_01/html/821-1274/installing-ojd.html

To configure OUD as authentication provider you need to configure a new provider by selecting "Iplanet authenticator". Move the newly created provider at the top in the list of authentication providers.

Select the OUD provider and go to provider specific tab. Enter the values as below -

User from name filter: (&(cn=%u)(objectclass=person))

User Name Attribute: cn

User Object Class: person

Group Base DN: dc=oracle,dc=com

Group From Name Filter: (&(cn=%g)(objectclass=groupofentries))

Static Group Name Attribute: cn

Static Group Object Class: groupofentries

Static Member DN Attribute: member

Static Group DNs from Member DNFilter:(&(member=%M)(objectclass=groupofentries))

NOTE - Remember to set "entryuuid" in the last property i.e. GUIDAttribute of provider specific tab for OUD authenticator.

In SOA 11.1.1.6 you also need to the set virtualize=true as shown in the screen shots below -

Issue Using xp20:upper-case in Assign Activity

Strange issue with using xp20:upper-case in assign activity. Below will not work -

xp20:upper-case($inputVariable.payload/client:MessageType)

where MessageType is of type string.

It will work like this -

xp20:upper-case(string($inputVariable.payload/client:MessageType))

This issue was encountered in SOA 11.1.1.5 - bpel 2.0

WSDL With Multiple Bindings

A service can be exposed on multiple protocols for e.g. web service having one operation exposed over SOAP/HTTP can also support SOAP/SMTP protocol. The only additional part required in WSDL is a binding element with correct transport. A sample WSDL is given below -

<wsdl:definitions name="ValidationService" targetNamespace="http://xmlns.oracle.com/NTApplication/ValidationService" xmlns:client="http://xmlns.oracle.com/NTApplication/ValidationService" xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/" xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/">
<wsdl:types>
<schema xmlns="http://www.w3.org/2001/XMLSchema">
<import namespace="http://xmlns.oracle.com/NTApplication/ValidationService" schemaLocation="../xsd/ValidationService.xsd"/>
</schema>
</wsdl:types>
<wsdl:message name="ValidationProcessRequestMessage">
<wsdl:part name="payload" element="client:process"/>
</wsdl:message>
<wsdl:message name="ValidationProcessResponseMessage">
<wsdl:part name="payload" element="client:processResponse"/>
</wsdl:message>
<wsdl:portType name="ValidationService">
<wsdl:operation name="validate">
<wsdl:input message="client:ValidationProcessRequestMessage"/>
<wsdl:output message="client:ValidationProcessResponseMessage"/>
</wsdl:operation>
</wsdl:portType>
<wsdl:binding name="ValidationServiceBinding" type="client:ValidationService">
<soap:binding transport="http://schemas.xmlsoap.org/soap/http"/>
<wsdl:operation name="validate">
<soap:operation style="document" soapAction="process"/>
<wsdl:input>
<soap:body use="literal" namespace="http://xmlns.oracle.com/NTApplication/ValidationService"/>
</wsdl:input>
<wsdl:output>
<soap:body use="literal" namespace="http://xmlns.oracle.com/NTApplication/ValidationService"/>
</wsdl:output>
</wsdl:operation>
</wsdl:binding>
<wsdl:binding name="ValidationServiceJMSBinding" type="client:ValidationService">
<soap:binding transport="http://www.w3.org/2010/soapjms"/>
<wsdl:operation name="validate">
<soap:operation style="document" soapAction="process"/>
<wsdl:input>
<soap:body use="literal" namespace="http://xmlns.oracle.com/NTApplication/ValidationService"/>
</wsdl:input>
<wsdl:output>
<soap:body use="literal" namespace="http://xmlns.oracle.com/NTApplication/ValidationService"/>
</wsdl:output>
</wsdl:operation>
</wsdl:binding>
</wsdl:definitions>

Assigning BPMWorkflowAdmin role to a user

BPMWorkflowAdmin role is an administrative role for a user in BPM worklist application. You can assign this role by following these steps.

In EM console expand weblogic domain -> [domain_name] and right click on domain. Select Security > Application Roles.

On Application Roles page select application "soa-infra" and click search/go button.

 The result will display a list of roles on soa-infra and registered members. Click link BPMWorkflowAdmin.

 Click AddUser in next screen.

Search for the user whom you want to assign a role and that's it. You are done!!!

SOA Suite Webservice to Access IdentityStore

Oracle SOA Suite provide OOTB web service to retrieve the details from identity store. This web service can prove very useful to verify if a SOA/workflow application can see the users and groups stored in the identity store. The web service URL is -

 http://<server>:<port>/integration/services/IdentityService/identity?wsdl

Oracle provides other services as well that can be found at this link -

http://docs.oracle.com/cd/E16764_01/integration.1111/e10224/bp_workflow.htm#BABFFDDF

Before invoking these services you need to set the realm name in em console system MBean browser at this location -

SystemMbean Browser > Application Defined Mbeans > oracle.as.soainfra.config > Server > WorkflowIdentityConfig > human workflow > WorkflowIdentityConfig.ConfigurationType.

Click on the realm to open operations. Invoke operation setRealmName to change the value from "jazn.com" to "myrealm".

Oracle SOA Application URLs

Below are the urls of application comes with Oracle SOA Suite 11g -

• Console  -  http://localhost:7001/console
• EM console for BPEL  -  http://localhost:7001/em
• OSB console - http://localhost:7001/sbconsole
• Human task application - http://localhost:7001/integration/worklistapp
• Rules composer - http://localhost:7001/soa/composer

Business Rules With Abstract XML Facts As Input

If you are writing business rules with some abstract xml facts as input and at runtime a concrete xml will be passed then make sure that "Check Rule Flow" is unchecked. Otherwise business rule validator will always report a validation error message.

XML supports inheritance and so the top level xml element can be of abstract type as shown below -

Above elements is defined in this manner in schema -


<xsd:complexType name="Document" abstract="true">
<xsd:annotation>
<xsd:documentation xml:lang="en">The abstract base type from which all FpML compliant messages and documents must be derived.</xsd:documentation>
</xsd:annotation>
<xsd:attributeGroup ref="StandardAttributes.atts"/>
</xsd:complexType>


<xsd:complexType name="Message" abstract="true">
<xsd:annotation>
<xsd:documentation xml:lang="en">A type defining the basic structure of all FpML messages which is refined by its derived types.</xsd:documentation>
</xsd:annotation>
<xsd:complexContent>
<xsd:extension base="Document"/>
</xsd:complexContent>
</xsd:complexType>


<xsd:complexType name="NotificationMessage" abstract="true">
<xsd:annotation>
<xsd:documentation xml:lang="en">A type defining the basic content for a message sent to inform another system that some 'business event' has occured. Notifications are not expected to be replied to.</xsd:documentation>
</xsd:annotation>
<xsd:complexContent>
<xsd:extension base="Message">
<xsd:sequence>
<xsd:element name="header" type="NotificationMessageHeader"/>
<xsd:group ref="Validation.model"/>
</xsd:sequence>
</xsd:extension>
</xsd:complexContent>
</xsd:complexType>


<xsd:complexType name="ContractCreated">
<xsd:annotation>
<xsd:documentation xml:lang="en"> Notification that a Contract has been Created </xsd:documentation>
</xsd:annotation>
<xsd:complexContent>
<xsd:extension base="NotificationMessage">
<xsd:sequence>
<xsd:element name="tradeReference" type="PartyTradeIdentifiers" minOccurs="0"/>
<xsd:element name="contract" type="Contract"/>
<xsd:element name="party" type="Party" minOccurs="2" maxOccurs="unbounded"/>
</xsd:sequence>
</xsd:extension>
</xsd:complexContent>
</xsd:complexType>

Now assume an element is defined of type Document like below and used as input to decision service.

<xsd:element name="FpML" type="Document">

The compiler will report an invalid dictionary as it will not find a concrete definition at design time. To suppress this error follow below steps to tell compiler not to check for concrete definition at design time.

DB as Authentication Provider For Human Task

There could be a scenario when a proper LDAP is not available to store users/groups for weblogic/SOA human task application etc. And this scenario is very much possible. In such situations weblogic SQL Authenticator comes to the rescue. SQL authenticator is provided to use database as authentication provider.

Listed down here are the steps to configure database as authentication provider using SQL Authenticator. Note that these steps are based on document available at -

https://support.us.oracle.com/oip/faces/secure/km/DocumentDisplay.jspx?id=1499679.1&h=Y


1. Create tables that will store the user & group details with below scripts -


CREATE TABLE USERS (
U_NAME VARCHAR(200) NOT NULL,
U_PASSWORD VARCHAR(50) NOT NULL,
U_DESCRIPTION VARCHAR(1000))
;
ALTER TABLE USERS
ADD CONSTRAINT PK_USERS
PRIMARY KEY (U_NAME)
;
CREATE TABLE GROUPS (
G_NAME VARCHAR(200) NOT NULL,
G_DESCRIPTION VARCHAR(1000) NULL)
;
ALTER TABLE GROUPS
ADD CONSTRAINT PK_GROUPS
PRIMARY KEY (G_NAME)
;
CREATE TABLE GROUPMEMBERS (
G_NAME VARCHAR(200) NOT NULL,
G_MEMBER VARCHAR(200) NOT NULL)
;
ALTER TABLE GROUPMEMBERS
ADD CONSTRAINT PK_GROUPMEMS
PRIMARY KEY (
G_NAME,
G_MEMBER
)
;
ALTER TABLE GROUPMEMBERS
ADD CONSTRAINT FK1_GROUPMEMBERS
FOREIGN KEY ( G_NAME )
REFERENCES GROUPS (G_NAME)
ON DELETE CASCADE

Populate this tables with some users and groups.

2. Create a datasource of the database having user and group tables.


3. Create SQL Autenticator. For this first goto security realm > myrealm > Providers and click New to create a new authentication provider as shown in the image.

4. Select the newly created provider and set the control flag to SUFFICIENT. Set the control flag of "DefaultAuthenticator" to OPTIONAL if you want to login BPM worklist application with database users.

5. Save changes and click Provider Specific tab. Enter the datasource name created earlier in step 2. Ensure that the following values are selected -

Plaintext Passwords Enabled: select

Data Source Name: <datasource name>

Group Membership Searching:unlimited

Max Group Membership Search Level: 0

Password Style Retained: select

Password Algorithm: SHA-1

Password Style: PLAINTEXT

6. Reorder the authentication provider so that DB provider comes at top.

7. Restart the server & managed servers and verify the db users and groups appear in "Users & Groups" tab.

8. Change the default realm from jazn.com to myrealm. In EM console and goto System Mbean browser Application Defined Mbeans > oracle.as.soainfra.config > Server  > WorkflowIdentityConfig > human-workflow > WorkflowIdentityConfig.ConfigurationType > jazn.com

Invoke the setRealmName operation with value "myrealm".

9. Download the workflow-120-SQLIdentityProvider.zip from Note 1194815.1. the link of which is provided above. Unzip it and copy dbprovider.jar to MW_HOME/user_projects/domains/{soa_domain}/lib

10. Shutdown weblogic server.

11. Navigate to HOME/user_projects/domains/{domain_name}/config/fmwconfig and take backup of jps-config.xml. 

12. Edit jps-config.xml to update following -

a) Add below xml fragment in serviceProviders element.


<serviceProvider type="IDENTITY_STORE" name="custom.provider" class="oracle.security.jps.internal.idstore.generic.GenericIdentityStoreProvider">
<description>Custom IdStore Provider</description>
</serviceProvider>

b) Add below xml fragment in serviceInstances element.


<serviceInstance name="idstore.custom" provider="custom.provider" location="dumb">
<description>Custom Identity Store Service Instance</description>
<property name="idstore.type" value="CUSTOM"/>
<property name="ADF_IM_FACTORY_CLASS" value="org.sample.providers.db.DBIdentityStoreFactory"/>
<property name="DB_SERVER_NAME" value="localhost"/>
<property name="DB_SERVER_PORT" value="1521"/>
<property name="DB_DATABASE_NAME" value="XE"/>
<property name="ST_SECURITY_PRINCIPAL" value="Admin"/>        
<property name="ST_SECURITY_CREDENTIALS" value="welcome1"/>
</serviceInstance>

c) Change <serviceInstanceRef ref="idstore.ldap"/> to <serviceInstanceRef ref="idstore.custom"/>.

13. Restart server and you should be able to login to worklist application with the user configured in the database

Dehydration Store Tables

Some tables of SOA dehydration stores and details are available at below Oracle documentation -

http://docs.oracle.com/cd/E23943_01/core.1111/e10108/bpel.htm#r12c1-t12

Also pasting the contents here for ready reference -

Table Name	Table Description
audit_trail	Stores the audit trail for instances. The audit trail viewed in Oracle BPEL Control is created from an XML document. As an instance is processed, each activity writes events to the audit trail as XML.
audit_details	Stores audit details that can be logged through the API. Activities such as an assign activity log the variables as audit details by default. Audit details are separated from the audit_trail table due to their large size. If the size of a detail is larger than the value specified for this property, it is placed in this table. Otherwise, it is placed in the audit_trail table.
cube_instance	Stores process instance metadata (for example, the instance creation date, current state, title, and process identifier)
cube_scope	Stores the scope data for an instance (for example, all variables declared in the BPEL flow and some internal objects that help route logic throughout the flow).
dlv_message	Stores incoming (invocation) and callback messages upon receipt. This table only stores the metadata for a message (for example, current state, process identifier, and receive date).
dlv_subscription	Stores delivery subscriptions for an instance. Whenever an instance expects a message from a partner (for example, the receive or onMessage activity) a subscription is written out for that specific receive activity.
document_ci_ref	Stores cube instance references to data stored in the xml_document table.
document_dlv_msg_ref	Stores references to dlv_message documents stored in the xml_document table.
wftask	Stores tasks created for an instance. The TaskManager process keeps its current state in this table.
work_item	Stores activities created by an instance. All activities in a BPEL flow have a work_item table. This table includes the metadata for the activity (current state, label, and expiration date (used by wait activities)).
xml_document	Stores all large objects in the system (for example, dlv_message documents). This table stores the data as binary large objects (BLOBs). Separating the document storage from the metadata enables the metadata to change frequently without being impacted by the size of the documents.
Headers_properties	Stores headers and properties information.

Setting Logging Level of SOA

Oracle provides a comprehensive documentation for the administration of Oracle SOA suite. A detailed description of the SOA logging levels is given at this documentation -

http://docs.oracle.com/cd/E15586_01/integration.1111/e10226/appx_trouble.htm#SOAAG97509

Beauty here is you can set the logging level at fine-grained component level. For instance you may want to the human workflow logging level to a finer level than bpel process manager.

Reset Oracle XE 11g Password

With Oracle XE 11g database you cannot retrieve the password if you have not configured the notification mails. As Oracle XE 11g sends mail for the forgotten password and to find the workspace for an email id.

But there is another way to reset the password in case you have forgot the INTERNAL workspace ADMIN password. Here are the steps to reset the ADMIN password from command line.

1. Goto folder <XE_HOME>\app\oracle\product\11.2.0\server\apex on your windows machine.
2. Open sql plus > sqlplus sys@XE as sysdba
3. Run the script > @apxchpwd.sql and enter new password when prompt comes.

Thats' it! the password has been changed now. Login on http://localhost:8080/apex with new password.

OSB Error : Failed to set the value of context variable "body"

A common error one can get while assigning variables in OSB is  -

Failed to set the value of context variable "body". Value must be an instance of {http://schemas.xmlsoap.org/soap/envelope/}Body.

OSB has some predefined variables like body and it expects that the variable body content should always be enclosed with <soap-env:body>. Inside this element you can assign any value.

If you don't want to be worried about this every time manipulating body variable then you must use "Replace" instead of "Assign". Just select "Replace node contents" and replace will take care of not removing the <soap-env:body> from the variable.