Oracle Open Interface

What is Oracle open interfaces?

Open interfaces used to integrate Oracle Applications with the non-oracle systems.
such as
1. Import data from your custom applications or legacy systems.
2. Migrate the old custom application data to the Oracle applications.
3. Import data from the banks, partners, suppliers, customers etc.


What is conversion?

Conversion is a one time Interface to migrate data from legacy application to the Oracle


Inbound Interface
The Interface which transforms and loads the data into the Oracle Applications system is called inbound interface.


Outbound Interface
The Interface which extracts the data from the oracle Application system for the purpose of loading into the external system is called outbound interface.


Common approach to interface is

1. Data would be extracted from the source machine and loaded into the custom table (stagging table) in Oracle apps database. Depending upon the complexity of the data Stagging table can be avoided.
2. The extracted data is then transformed into a form which oracle can recognize and pushed into interface tables.
3. Final step is load the data from interface table to base table of Oracle applications.

This whole process is called extract load and transformation (ETL).


Oracle provides Interface tables for data intensive applications like Invoice, receipts, journals etc and PL/SQL APIs for master data interfaces like people, items, suppliers etc.


Traditional approach used sql Loader to load the data and pl/sql program to validate and extract data.
Modern approach uses BPEL, ESB for online data interfaces and Oracle Data Integrator for data intensive batch processing.


Common Interface technologies
1. XML Gateway
2. SQL*Loader
3. DB Link
4. Java Concurrent Program
5. BPEL or ESB
6. Oracle Data Integrator or ODI
7. PL/SQL package for reading file or XML data
8. Oracle Advanced Queuing


Some Useful Links

1. Oracle Integration Repository - List of Oracle open interfaces
2. Technical reference Manual - Column level details of Interface tables, base tables and APIs
3. Technical reference Manual for different versions.

BPEL Tree SQL query

How to query the BPEL tree in the dehyderation database directly?

Tree finder using SQL query.

select
lpad(' ', (level - 1) * 2) || title as padded_name, process_id, modify_date-creation_date, creation_date, modify_date, state
from cube_instance ci1
connect by prior cikey = parent_id
start with cikey = :parent_process;


TreeFinder jsp is the most resource hungry webpage in the BPEL console. This query is an alternative to the Tree Finder jsp.

Abstract WSDL vs Concrete WSDL

In recent times, the abstract WSDL terminology is used more than ever and I am seeing more & more coffee-table discussions on this topic. In this post I will make an effort to provide an illustrated explanation in regards to the differences & usage of concrete and abstract WSDL.

First up, abstract WSDL is not a new technology. A typical WSDL (Web Services Description Language) consists of the following sections; types, messages, portType, binding & service elements. A quick look at what each of these sections define will ease the task of clarification of the differences;

Types - Defines the data type definitions for messages that will be exchanged by the web service. Generally defined from a schema (XSD)

Message - Defines the set of actual messages that will be exchanged. A message can consist of one or more parts which is a logical separation which together constitutes the message.

PortType - Defines the abstract operations provided/available and abstract messages involved. Operation refers to the messages involved in the transaction.

Bindings - Defines the message format and protocol information for operations defined by the portType.

Service - Defines the endpoint where the webservice will be exposed

Based on these definitions, we can figure out that the Types, Message & PortType sections of a WSDL definition is abstract (since it has no information pertaining to the communication protocol & how or where the service is exposed) where as the Bindings & Service section is concrete.

Hence, if we have a WSDL which just has these elements (excluding the Bindings & Service sections), then the WSDL is referred to as "Abstract WSDL". This WSDL definition cannot be used to connect to the webservice due to the 'missing information' on where & how the service is exposed. But there are certain advantages with abstract wsdl which we will see in a bit.

Definition of concrete WSDL now becomes a no-brainer. If a WSDL has all the sections - including the concrete parts such as Bindings & Service then it is referred to as a concrete WSDL. This WSDL defnition can be used to connect to the actual service since it has the protocol and service endpoint information.

Now, where exactly do we use an abstract WSDL?

Coming to the world of SOA, I am taking the example of Oracle SOA composites referencing external services. Ever wondered why some SOA composites are in "Invalid State" even though all the services that it references are up & available? This is because of the usage of concrete WSDL reference where the referenced service is not available yet when the caller service composite starts up. There is no way for the server to know about the service dependencies when it starts. By leveraging the concept of the abstract WSDL, we would be able to effectively resolve dependencies both at design time as well as runtime.

More on resolving service dependency issues in SOA on my next post here

Comments:-

1. hi bro,
   
   i have small doubt if we are calling a wsdl from MDS(runtime),i.e wsdl we are calling is abstract or concrete how could we know

   Reply
   Replies

   
   SathyaSeptember 8, 2016 at 10:45 AM
   Your composite will have 2 sections - reference & binding. Your reference will point to abstract WSDL which is what is used while loading composite references during startup. Service binding in your SOA composite will have the actual concrete WSDL.
   Reply
2. 
   AnonymousSeptember 30, 2016 at 3:58 PM

   Hi Satya,
   
   I like your posts. I have been started reading all of your posts and they are very informative.
   
   My question is related to context of this post is, what is the disadvantage of having concrete WSDL in the MDS instead of abstract. so that I no need to remove the binding and service parts of the wsdl before I store them in the MDS. At design/load time, the composite only needs types, messages and port related information and is already available in the concrete wsdl stored in the MDS. What is the disadvantage of having binging and service information in the MDS wsdl?
   

   Reply
   Replies

   
   SathyaOctober 1, 2016 at 1:13 PM
   Thanks for your comments. Think of a situation where the service has been moved/upgraded - typically in such scenario the service endpoint changes but contract still remains the same. We don't want to store the concrete wsdl (with actual service endpoint) in MDS, hence we decouple the service from its contract - Abstract WSDL helps with this design. Your SOA composite will have the reference pointing to abstract WSDl and the service binding will reference the concrete WSDL.
   Reply

Unrecognized option: -jrockit

starting weblogic with Java version:

Error: Could not create the Java Virtual Machine.
Error: A fatal exception has occurred. Program will exit.
Starting WLS with line:

Unrecognized option: -jrockit

If above error comes after starting Standalone weblogic then go through below solution:

Solution: Find "commEnv.cmd"   file in Weblogic Home

windows Path: C:\Oracle\Middleware\wlserver_10.3\common\bin


In "commEnv.cmd"  file change below value which is all ready mention as a -jrockit  


if "%PRODUCTION_MODE%" == "true" goto oracle_prod_mode set JAVA_VM=-jrockit

Change as below value
if "%PRODUCTION_MODE%" == "true" goto oracle_prod_mode set JAVA_VM=-client

:oracle
if "%PRODUCTION_MODE%" == "true" goto oracle_prod_mode
set JAVA_VM=-client
set MEM_ARGS=-Xms128m -Xmx256m
set JAVA_OPTIONS=%JAVA_OPTIONS% -Xverify:none
goto continue
:oracle_prod_mode
set JAVA_VM=-client
set MEM_ARGS=-Xms128m -Xmx256m
goto continue

----------------------------------------------------------------------------------------------------------

Unrecognized option: jrockit while trying to start Weblogic Admin console

JRockit VM might not be installed in the system. If JVM being used is HotSpot (which we can confirm by checking the java version), change the JAVA_VM property(under the label “oracle:“) in the file “commEnv.cmd” to (“-client” or “-server” depending on the JVM version being used) present in the folder %WL_HOME%/common/bin/.

This is what the file extract looks like after the change:

: oracle
if "%PRODUCTION_MODE%" == "true" goto oracle_prod_mode
set JAVA_VM=-client

Now, restart the server.

Skipping Activites Execution in BPEL

Hi Guys,
 
I have been away from the blog for quite some time due to work and all.Finally got time to post new blog here. Today I will be explaining the new feature that is available from 11.1.1.6 onwards.Some of you may already knowing it but for those who dont let me tell. You can now skip activities in your BPEL flow without using any switch or if condition. Oracle provides an extension that enables you to specify an XPath expression in an activity in BPEL versions 1.1 and 2.0 that, when evaluated to true, causes that activity to be skipped. This functionality provides an alternative to using a switch activity for conditionally executing activities. The skip condition for activities is specified as follows:

<activity bpelx:skipCondition="boolean-expr"/>

The bpelx:skipCondition attribute causes an XPath expression to be evaluated immediately upon creation of the activity instance. If the skip expression returns a false boolean value, the activity is executed. If the skip expression returns a true boolean value, the activity is completed immediately and execution moves to the activity immediately following that one.

This construct is equivalent to a switch/case structured activity with a single case element with a condition that is the opposite of the skip condition.

I will be demonstrating how to use it.Suppose in my code whenever I pass the input as 5 , I want to skip this invoke activity. Means I dont want this activity to get executed.

How do we go about it.Double click the invoke activity. Go to "Skip Condition" tab. Click on expression and write out your expression as shown in the screenshot:

Now, whenever I will pass the input value as "5", this invoke activity wont be executed.In the flow trace you will see this activity as "Activity Skipped".

There you go.In this way you can skip any activity you intend to skip without coding switch/IF condition in your BPEL code.


Hope this helps.

Happy Learning,
Cheers !!!

Adding nodes and creating variables in XSL - Oracle SOA 11G

Hi guys,

Today I am gonna write about,how to use summation function in XSL and how to create a cariable and use it in your xsl. Actually,there was a requirement in our project,which was like adding some values of some fields in the input and based upon the sum we required to pass some value to the target system.So, to accomplish this we used sum function, that is available in xslt.

I will be demonstrating it taking one example.Suppose we have some fields that are coming in the input such as A,B,C and we need to add all these values and see whether its sum is greater than zero or less than zero.If the sum is greater than 0, we will pass say "pass" and if its other way around we will pass say "fail" to the target system.I have create a sample BPEL process for this usecase.Process is very simple: it is exposed as webservice and taking input from the client and after doing aforesaid transformation and logic will publish the message in jms Queue.I will just explain the XSL part.

Step1: In my transformation, first I will create a variable that I will be using to storing the sum of all the required nodes. So in your xsl on target node,right click and add variable "tempVar".

Step2: Drag and drop "Sum" function from Mathematical functions in the centre of xsl.

Step3: Now in the function add all the nodes that you want to add separated by "|".In this example I am adding surcharge,tax,vat,shipmentcharge nodes.

 <xsl:variable name="tempVar"
                select="sum((/imp1:employee/imp1:surcharge | (/imp1:employee/imp1:order/imp1:Tax | (/imp1:employee/imp1:order/imp1:VAT | /imp1:employee/imp1:order/imp1:shipmentCharge))))"/>

Step4: Based upon the sum we need to send value in the target XML.So in our case if its greater tha 0, We will send "Invocie" in typecode tag and "Credit MEmo" otherwise.To implement this add a choose when and otherwise condition. 

Step5: It will check the value of "tempVar" and based on the result,value will be set in the target xml.

Step6: Now deploy and test your composite. First we will test for positive vsalue.I have passed values in all the field greater than 0.As per our logic value in Typecode tag is sent as "INVOICE".

Step7: To check the opposite scenario,pass values such that sum is less than 0.Values now passed is "Credit Memo".

In this way we can use the sum function and also you learned ,how to create a variable in xsl and use that in your XSL.







NOTE: There can be scenarios where values in some nodes can be empty.So you have to handle that as well,otherwise,you will get "NaN" in your result.Hence in order to handle blank nodes use below expresssion:

sum((/imp1:employee/imp1:surcharge[. != ""] | (/imp1:employee/imp1:order/imp1:Tax[. != ""] | (/imp1:employee/imp1:order/imp1:VAT[. != ""] | /imp1:employee/imp1:order/imp1:shipmentCharge[. != ""]))))
  
Hope this post is useful to you guys.

Happy Learning,
Cheers !!!

Dynamically passing JNDI name to JCA Adapters - SOA 11G

Hi All,

We can pass the JNDI name to any JCA adapter (JMS,DB,File) at runtime as well.All we need to do is configure one property inside the invoke activity that will pass jndi at runtime to the JCA adapter.Just go to the source of your invoke activity where you need to pass the jndi at runtime.

 Then inside the invoke activity add below line:
<bpelx:inputProperty name="jca.jndi" variable="jndiVar"/> in case of BPEL 1.1 and <bpelx:toProperty name="jca.jndi" variable="jndiVar"/> in case of BPEL 2.0 where "jndiVar" is a variable that will contain your jndi name that will be passed at runtime.

Now assign the value to "jndiVar" and place this assign activity before the invoke activity where dynaic jndi needs to be passed.

Deploy and test your composite to verify that jndi is being passed dynamically at runtime.

NOTE: you won't be able to see this property under Invoke --> Properties tab. You have to add it manually by going inside the source of your BPEL process.

Hope it will help you in your implementation.

Happy Learning,
Cheers !!!

Getting and Setting JMS Header Properties - SOA 11G

Hi Guys,

Sometimes there is requirement where you are required to set some JMS header values and then send the jms message to the client or consumer may interact with you via jms queue and sends some user defined properties in the JMS header.In BPEL 11G, we can easily set or retreive these values.

Setting JMS Header Values

Step1: Click on the invoke activity inside your BPEL process that is calling your JMS adapter for publishing messages in the jms queue.Then click on the source tab of BPEL.

Step2: Inside the invoke activity code add this property <bpelx:inputProperty name="jca.jms.JMSProperty.propertyname" variable="variablename"/>
where propertyname: name of the property you want to set and variablename: variable from which you are passing value to this property.For instance,in out usecase I am setting following properties:

      <bpelx:inputProperty name="jca.jms.JMSProperty.MSG_ID" variable="TransID"/>
      <bpelx:inputProperty name="jca.jms.JMSProperty.FROM_SYSTEM" variable="FROM_SYSTEM"/>   
      <bpelx:inputProperty name="jca.jms.JMSProperty.ServiceName" variable="ServiceName"/>
Make sure you populate all these properties before calling invoke activity,otherwise these properties will contain null/blank values.

Step3: Deploy and test your code.Verify that these values are getting set by looking into the flow trace of your composite.

Step4: Also,you can verify the message in jms queue as well and see if the properties are properly set or not.

Getting JMS Header Values

Step5:Inside your BPEL process,click on the receive activity that is receiving input from the jms adapter and click on source.

Step6: Add this property inside your receive activity: <bpelx:outputProperty name="jca.jms.JMSProperty.propertyname" variable="variablename"/>. It is same as we did for setting the property except the outputProperty tag.In my example, I am retreiving the value of jms header that I set previously.

      <bpelx:outputProperty name="jca.jms.JMSProperty.MSG_ID" variable="TransID"/>
      <bpelx:outputProperty name="jca.jms.JMSProperty.FROM_SYSTEM" variable="FROM_SYSTEM"/>   
      <bpelx:outputProperty name="jca.jms.JMSProperty.ServiceName" variable="ServiceName"/>
     
Create the variables that will store the values of these properties.

Step7: Deploy your code and test your composite.Inside flow trace verify you are able to retreive the jms properties headers value from the JMS message.

In this way you guys can set and get the header properties of any JMS message in SOA 11G.Apart from accessing these values from BPEL,you can also access these values from Mediator.In mediator, they are accessed in the assign step using:

Inbound: $in.property.jca.jms.JMSProperty.<propertyname>
Outbound: $out.property.jca.jms.JMSProperty.<propertyname>

Hope this post of some help to you.

Happy Learning,
Cheers !!!