Execute SQL query from OSB 11g Xquery file


below snap shot of code is just for reference. I recently had a requirement where I had to execute a SQL query in XQuery file and same should be returning some result.

Eventually objective here is to use an existing xquery function fn-bea:execute-sql().

Here with below code, I would be getting a shipment id from request and using that, I need to call a DB query and evaluate the result

xquery version "1.0" encoding "Cp1252";

declare namespace xf = "http://tempuri.org/testprocess/PUB/trans/ClientRef/";

declare function xf:EmpRef($dummydata as xs:string,
$shipmentId as xs:string)
as xs:string {
let $sql-string := "SELECT client_info FROM client_table WHERE SHIPMENT_ID=? and ROWNUM 0) then
fn:data('no data')


declare variable $dummydata as xs:string external;
declare variable $ShipmentId as xs:string external;

xf:ClientRef($dummydata ,

Posted in OSB | Tagged , , | Leave a comment

java.lang.NullPointerException while creating new SOA Application in Jdev 12c

Hi all,

Just want to share an experience regarding an issue I came across while working on Jdeveloper 12c version

Issue: I was getting below exception while trying to create a new SOA Application and Project in my Jdeveloper:

at oracle.jdeveloper.model.ApplicationContent.getInstance(ApplicationContent.java:62)
at oracle.jdeveloper.template.wizard.NewApplicationFromTemplateWizard.configureApp(NewApplicationFromTemplateWizard.java:250)
at oracle.jdeveloper.template.wizard.NewApplicationFromTemplateWizard.getOrCreateWorkspace(NewApplicationFromTemplateWizard.java:232)
at oracle.jdeveloper.template.wizard.NewObjectFromTemplateWizard$CreateObjectFromTemplateWorker.doInBackground(NewObjectFromTemplateWizard.java:814)
at oracle.jdeveloper.template.wizard.NewObjectFromTemplateWizard$CreateObjectFromTemplateWorker.doInBackground(NewObjectFromTemplateWizard.java:796)
at javax.swing.SwingWorker$1.call(SwingWorker.java:296)
at java.util.concurrent.FutureTask.run(FutureTask.java:262)
at javax.swing.SwingWorker.run(SwingWorker.java:335)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:745)

Resolution: you need to delete o.ide file from below path and take a restart of Jdev :


By doing so the issue was resolved.




Posted in Uncategorized | Leave a comment

Adapter db not supported for jca – Error while deploying OSB project using JCA adapter


Recently had a very strange issue while deploying a OSB(11g) project having JCA adapter(fetch data from a DB procedure) using the hudson auto deployment scripts.

The code worked in manual deployment in our development environment were as while deploying using the Hudson build job, code failed during compilation with error “Adapter db not supported for jca”.

While analyzing the issue, we did noticed that JCA file having a reference of adapter as “db” which were causing this issue. After changing this to adapter=”Database Adapter” , this issue is fixed.

Environment configuration:-

1. JDev 12c was used to build the JCA adapter.
2. OSB 11g was used to build and compile the code.

Before changes :

<adapter-config name=”SearchAllData” adapter=”db” wsdlLocation=”../WSDL/SearchAllataAbstract.wsdl” xmlns=”http://platform.integration.oracle/blocks/adapter/fw/metadata”&gt;

After changes :

<adapter-config name=”SearchAllData” adapter=”Database Adapter” wsdlLocation=”../WSDL/SearchAllataAbstract.wsdl” xmlns=”http://platform.integration.oracle/blocks/adapter/fw/metadata”&gt;

Posted in OSB | Tagged , | Leave a comment

Weblogic Thread Pool Configuration

In my current project we were facing an issue where our SOA application running on weblogic servers runs out of Processing Threads when high load coming and resulted in Active Requests piling up and hence degradation in performance and failure.

On further troubleshooting the issue there is a Oracle Doc ID found 1935014.1 which explains the same issue and suggested to set a MinPoolSize argument for all MS so that threads will be created faster in order to serve incoming request.Hence below are the steps which can be implemented;

  1. Open Weblogic console
  2. go to MS -> Server start -> take the session
  3. Add below values in start up arguments
  4. Restart the server
  5. Same steps needs to be repeated for rest of the MS in the weblogic domain.

Below is the Oracle Doc ID which explains the same problem;

SOA Worker Threads Ramping Up Slowly on Weblogic / Exalogic Platforms (Doc ID 1935014.1)

After setting this value it actually means;
– 100 Threads initially created to serve 100 concurrent request

– When the 101st request comes in (when all 100 threads are processing and no free thread available), then the next 100 threads are created. A total of 200 threads will be available for processing requests.

– When all 200 threads are processing requests and the 201st request comes in, then the next 100 threads are created. A total of 300 threads will be available for processing requests and so on.

So threads are created in batches according to the -Dweblogic.threadpool.MinPoolSize size setting.

Posted in AIA, OHS, SOA, Thread Pool, Uncategorized, Weblogic | Leave a comment

Importing certificates in weblogic server

While working on day to day work, I was able to find 2 different way to import a certificate on your weblogic server

  1. Importing certificate using key tool over command prompt
  2. Importing certificate using KeyStore Service (KSS)


Importing certificate using key tool over command prompt

To know the WebLogic server’s trust Keystore location: On the home page, proceed to Servers under Environment subsection.

  • Select the Admin server out of the list of servers displayed –
  • Proceed to the Keystores tab, under Keystores tab, select the Demo Trust Keystore path. This is the path where our keystore resides.

At this path, run the following keytool command:
keytool -import -trustcacerts -alias TestCA –file <Filename with location> -keystore DemoTrust.jks -storepass DemoTrustKeyStorePassPhrase
For e.g., assuming downloaded certificate is kept at the location where .jks file available and name of the certificate is certificatename.cer then keytool command would look like:
keytool -import -trustcacerts -alias TestCA–file certificatename.cer -keystore DemoTrust.jks -storepass DemoTrustKeyStorePassPhrase



A message Certificate was added to keystore is displayed, which confirms the successful import of the certificate.

Note- If you get a ‘Certificate already exists in the Keystore message’, enter ‘Y’(yes) and proceed to import the certificate.

You can verify the same by enlisting all the certificates using the following command:
keytool –list –keystore DemoTrust.jks -storepass DemoTrustKeyStorePassPhrase


The newly imported certificate appears as part of existing certificates in the keystore,



Importing certificate using KeyStore Service (KSS)

To import a certificate using KeyStore Service (KSS), need to ensure that KSS is enabled.

Please ensure KSS for demo is enabled.If its not, follow the below steps to enable it
go to the WebLogic console, Click on Domain –> Security –> Advanced page and select ‘Use KSS for Demo’ checkbox.
Click Save.

Note – Above option is available on Oracle 12c version. I could not find same on 11g.

Now, navigate to your em console and right click on weblogic domain –> Security –>KeyStore


Expand the drop-down list in which the Keystore resides and Select the row corresponding to the Keystore. For this case, System –>Trust


If the Keystore is password-protected, you are prompted for a password. Enter the Keystore password and click OK.
Click on Manage option,
The Import Certificate dialog appears.
Select the certificate type, either Certificate or Trusted Certificate, from the drop-down. For this case, use ‘Trusted Certificate’. Provide an alias, for example, ‘testTrust’.

Specify the certificate source. If using the Paste option, copy and paste the certificate directly into the text box. If using the Select a file option, click Browse to select the file from the operating system.
Click OK. The imported certificate or trusted certificate appears in the list of certificates.
Click OK.


Restart your Admin server to reflect the changes.

Posted in Weblogic | Tagged , , , | Leave a comment

BPEL Invoke process Error- WSM-07620 : Agent cannot enforce policies due to either failure in retrieving polices or error in validations


Today while working on BPEL 12c local setup setp, I was hit by an error while testing my service. I was keep on getting error message like below

The selected operation process could not be invoked.
An exception occurred while invoking the webservice operation. Please see logs for more details.
oracle.sysman.emInternalSDK.webservices.util.SoapTestException: oracle.fabric.common.PolicyEnforcementException: WSM-07620 : Agent cannot enforce policies due to either failure in retrieving polices or error in validations, detail= "WSM-02557 The documents required to configure the Oracle Web Services Manager runtime have not been retrieved from the Policy Manager application (wsm-pm), possibly because the application is not running or has not been deployed in the environment. The query "&(@appliesTo~="WS-CLIENT()")(policysets:global/%)" is queued for later retrieval.
WSM-02557 The documents required to configure the Oracle Web Services Manager runtime have not been retrieved from the Policy Manager application (wsm-pm), possibly because the application is not running or has not been deployed in the environment. The query "/policies/oracle/wss_username_token_client_policy" is queued for later retrieval.

Root Cause – during my setup, I initally setup SOA manager server with port 7003 but later due to port conflict, I did changed this to 7013 which caused servers out of sync and caused this error.

Fix – I have reverted back the managed server to point to 7003 port and restarted the domain. Issue fixed after this.


Posted in OWM, SOA | Tagged | 1 Comment

DVM Transformation of a comma separated delimited string input in SOA 11g

Problem statement:- During one of my project, we had a requirement to apply a DVM transformation on a comma separated delimited input string and post DVM transformation send transformed output as comma separated single string value.

For example :- As an input we are receiving input as <input>A,B,C</input> and now we have to apply a DVM transformation based on below rule

DVM Capture

And the output to target should be <output>X,Y,Z</output>.

Solution :-

To achieve this we have followed below approach

  1. Used oraext:create-nodeset-from-delimited-string() function to convert a delimited string input to a XML output
  2. Apply DVM function on input values
  3. Use oraext:create-delimited-string() function to create a comma(,) separated string.

Below is the snapshot of BPEL Design

BPEL SnapShot


To achieve this, we have used a intermediate XSD.


<?xml version="1.0" encoding="windows-1252" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
<xsd:element name="InputString">
<xsd:element name="DVMInput" type="xsd:string" minOccurs="0" maxOccurs="unbounded"/>
<xsd:element name="OutputString">
<xsd:element name="DVMOutput" type="xsd:string" minOccurs="0" maxOccurs="unbounded"/>


Below is the BPEL source code for your reference.

<sequence name="main">

<!– Receive input from requestor. (Note: This maps to operation defined in XSLTDVMConversion.wsdl) –>
<receive name=”receiveInput” partnerLink=”xsltdvmconversion_client” portType=”client:XSLTDVMConversion” operation=”process” variable=”inputVariable” createInstance=”yes”/>
<assign name=”CreateNodeSetOfDelimitedString”>
<bpelx:from expression=”oraext:create-nodeset-from-delimited-string(‘{http://www.example.org}DVMInput’,bpws:getVariableData(‘inputVariable’,’payload’,’/client:process/client:input’),’,’)”/>
<bpelx:to variable=”TempInputVariable”
<assign name=”DVMTransform”>
<from expression=”ora:doXSLTransformForDoc(‘xsl/Transformation_DVM.xsl’, $TempInputVariable)”/>
<to variable=”TempOutPutVariable”/>
<assign name=”CombinNodeSetToDelimatedString”>
<from expression=”ora:doXSLTransformForDoc(‘xsl/Transformation_DelimatedString.xsl’, $TempOutPutVariable)”/>
<to variable=”outputVariable” part=”payload”/>
<!– Generate reply to synchronous request –>
<reply name=”replyOutput” partnerLink=”xsltdvmconversion_client” portType=”client:XSLTDVMConversion” operation=”process” variable=”outputVariable”/>


Attached the sample code your reference. Feel free to post your query if you have see any issue with sample code.

Posted in SOA | Tagged , , | Leave a comment

Primavera server producing huge amount of Archive logs and causing issue with application

We had issue with our production environment where server was generating huge amount of Archive logs and causing archive logs to fill frequently, which eventually cause shows unexpected error on application server.

As an solution, refer below notes from Oracle support.

  1. P6 Global Scheduled Services Are Stuck In A “Running” State (Doc ID 1400104.1)
  2. Master Note For Primavera P6 Services (Doc ID 1612845.1)
  3. How To Restart Project Scheduled Services That Are “Stuck” In A Running State (Doc ID 1418876.1)
Posted in Primavera Integration | Tagged , | Leave a comment

How to track container id belongs to which soa server

Many times we faced an issue where messages piling up in resequencer and from mediator container id lease table we found that there is one container ID which is not getting refreshed (renewal) and holding messages. Now in order to fix this we first need to track that bad container ID belongs to which specific soa server in order to take appropriate action on that server only.

This can be identified by increasing TRACE log level of mediator. Below are the steps;

  1. Login to EM console.
  2. Go to Log Configuration of SOA Infra
  3. Put TRACE 32 at oracle.soa.mediator.dispatch and save the changes.

Note: It does not require restart of the server.

Check soa server diagnostic logs where below string starts coming by which we can identify this is the container ID.

Renew container id [BC0S40987623G43K12368TY243P321]

Posted in AIA, Container ID, Mediator, Resequencer | Tagged , , , , , | Leave a comment

Unable to import DB table using DBAdapter in JDeveloper 111117

Though you might be finding this post silly but trust me I had to struggle a lot to find this issue in one of my team machine.

We had installed Jdev 11117 and for SOA Extension Plugin, we was having a download zip file, so used that but that caused issue mismatch. Though Jdev did not prompt me any warning for version mismatch but later while using DBAdapter, we found that we were unable to import the table or other things were not working properly.

To fix this issue, we installed the SOA extension plugin from the update which was compatible with Jdev11117.

So my learning is, always download the plugin from updates 🙂

Posted in Jdeveloper, SOA | Leave a comment