SAP JCO Connectivity Test

This will be a small post on testing SAP JCO connection. Usually for connecting to SAP systems from Oracle SOA suite, we make use of the SAP adapter and create a SAP target from Application Explorer(AE). After entering the basic credentials like username,password, clientid, system number and application server we can try connecting to SAP system from AE itself. This internally calls the SAP JCO classes to establish the connection.

We can also directly test the connection outside of AE by using the SAP JCO classes. Below sample code can be used for testing the SAP JCO connectivity.

package JCOConnectionTest;
import com.sap.mw.jco.*;
public class JCOConnector {
  public JCOConnector() {
    super();
  }
  JCO.Client mConnection;
  public void Connect1() {
  try {
  // Change the logon information to your own system/user
          mConnection =
             JCO.createClient("010", // SAP client
               "SAP_USER", // userid
               "sap_pswd", // password
               null, // language
               "sap_server", // application server host name
               "00"); // system number
          mConnection.connect();
          System.out.println(mConnection.getAttributes());
          mConnection.disconnect();
       }
       catch (Exception ex) {
         ex.printStackTrace();
         System.exit(1);
       }
  }
      public static void main (String args[]) {
       JCOConnector app = new JCOConnector();
       app.Connect1();
      }
}  

Make sure the sapjco.jar file is present in your classpath(add to project classpath if you are running this from Jdeveloper.) On successful connection you are shown the details of the server with an exit code of 0 and incase of failure you are shown some RFC errors like RFC_LOGON_FAILURE etc.

XSLT 2.0 Grouping & Sorting in 11g BPEL

I had a requirement recently where I had to split my incoming XML file(read from a database) based on a particular field and send the split sections to another BPEL process for further processing.

Here is how the Input xml looked like

 
  arr1
  ...
  ...
  
  
  arr2
  ...
  ...
  
  
  arr1
  ...
  ...
  
  
  arr2
  ...
  ...
  

Desired Output looks like this.

  
  arr1
  ...
  ...
  
  
  arr1
  ...
  ...
  


  
  arr2
  ...
  ...
  
  
  arr2
  ...
  ...
  

As can be seen above, the idea was to split the Input xml based on arr_id field into different collections and each collection having data for a particular arr_id. Once this is done, all I had to do is run a loop in my BPEL and for each collection send the data to the 2nd BPEL process.

I decided to use XSLT Grouping and Sorting feature to accomplish this task. If we are using XSLT 1.0 there is a particular way to do this using xsl:key which has been explained well in http://www.jenitennison.com/xslt/grouping/muenchian.html . However this can be done easily in XSLT 2.0 using xsl:for-each-group.

Below is the XSL file which does this task for us,

<xsl:stylesheet version="2.0" xmlns:ns1="http://xmlns.oracle.com/pcbpel/adapter/db/Select_Arrid" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
 <xsl:template match="/">
  <ns1:root>
    <xsl:for-each-group select="*/ns1:Select_Arrid" group-by="ns1:Arr_id">
        <xsl:sort select="current-grouping-key()">
        <ns1:select_arrid_collection>
           <xsl:copy-of select="current-group()">
                <xsl:apply-templates/>
          </xsl:copy-of> 
        </ns1:select_arrid_collection>
      </xsl:sort>
    </xsl:for-each-group>
   </ns1:root>
 </xsl:template>
</xsl:stylesheet>

Make sure the version of XSL is 2.0 for using the xsl:for-each-group construct. Now the xsl can be used in any transform activity and will give us the desired output. Hope this helps someone having similar XSLT grouping requirement in BPEL.

AIA Error Handling : Error Notifications/Emails

In continuation with my earlier posts on AIA Error handling, I will cover the steps required to enable/send error notifications in this post. AIA Error handling framework has out-of-the-box error email functionality. Incase of partner link errors like remote/binding faults the fault-policy.xml file should take care of sending the error notification/email. Incase of non-partner link error the AIAAsyncErrorHandlingBPEL process is called from catch-all block which inturn sends out the error email.

A sample fault-policy.xml file is shown below

3
2

In above file, the oracle.apps.aia.core.eh.CompositeJavaAction class file gets invoked when remote/binding fault occurs and takes care of sending the error notification to AIAIntegrationAdmin user.

A sample code for populating EBM Header in catch-all block is shown below

1. First create a variable named EBM_HEADER
<variable name="EBM_HEADER" element="ns3:EBMHeader"/>

ns3 here refers to corecom namespace.

2. Make sure your ABM_to_EBM.xsl or EBM_to_ABM.xsl are populating the EBM header section correctly (eg. EBMID, EBOName etc...)

3. In catch-all block make sure you are populating the EBM_HEADER before invoking the AIAAsyncErrorHandlingBPELProcess

<assign name="Assign_Fault">
           <copy>
            <from expression="ora:processXSLT('xsl/EBM_to_Fault.xsl',bpws:getVariableData('EBM_HEADER'))"/>
            <to variable="Invoke_AIAAsyncErrorHandlingBPELProcess_initiate_InputVariable"
                part="FaultMessage" query="/ns3:Fault"/>
          </copy>
          <copy>
            <from expression="ora:getFaultAsString()"/>
            <to variable="Invoke_AIAAsyncErrorHandlingBPELProcess_initiate_InputVariable"
                part="FaultMessage" query="/ns3:Fault/ns3:FaultNotification/ns3:FaultMessage/ns3:Text"/>
          </copy>
          <copy>
            <from expression="xpath20:current-dateTime()"/>
            <to variable="Invoke_AIAAsyncErrorHandlingBPELProcess_initiate_InputVariable"
                part="FaultMessage" query="/ns3:Fault/ns3:FaultNotification/ns3:ReportingDateTime"/>
          </copy>
          <copy>
            <from expression="ora:getProcessId()"/>
            <to variable="Invoke_AIAAsyncErrorHandlingBPELProcess_initiate_InputVariable"
                part="FaultMessage" query="/ns3:Fault/ns3:FaultNotification/ns3:FaultingService/ns3:ID"/>
          </copy>
          <copy>
            <from expression="'BPEL'"/>
            <to variable="Invoke_AIAAsyncErrorHandlingBPELProcess_initiate_InputVariable"
                part="FaultMessage" query="/ns3:Fault/ns3:FaultNotification/ns3:FaultingService/ns3:ImplementationCode"/>
          </copy>
          <copy>
            <from expression="ora:getInstanceId()"/>
            <to variable="Invoke_AIAAsyncErrorHandlingBPELProcess_initiate_InputVariable"
                part="FaultMessage" query="/ns3:Fault/ns3:FaultNotification/ns3:FaultingService/ns3:InstanceID"/>
          </copy>
          <copy>
            <from expression="ora:getECID()"/>
            <to variable="Invoke_AIAAsyncErrorHandlingBPELProcess_initiate_InputVariable"
                part="FaultMessage" query="/ns3:Fault/ns3:FaultNotification/ns3:FaultingService/ns3:ExecutionContextID"/>
          </copy>
        </assign>
      <invoke name="Invoke_AIAAsyncErrorHandlingBPELProcess" inputVariable="Invoke_AIAAsyncErrorHandlingBPELProcess_initiate_InputVariable"
              partnerLink="AIAAsyncErrorHandlingBPELProcess"
              portType="ns10:AIAAsyncErrorHandlingBPELProcess"
              operation="initiate"/>

Apart from this there are certain configurations which need to be done for successfully sending error emails.

1. Set the email driver properties from EM Console :

OutgoingMailServer :
OutgoingMailServerPort :
OutgoingMailServerSecurity :
OutgoingDefaultFromAddress:

2. In AIA_HOME/aia_instances/Instance_name/AIAMetaData/config/AIAConfigurationProperties.xml file , below properties should be set

<Property name="EH.INVOKE.NOTIFY">true</Property>
<Property name="FROM.EMAIL.ID">Email:AiaAdmin@oracle.com</Property>
<Property name="EH.DEFAULT.ACTOR.ROLE">AIAIntegrationAdmin</Property>

3. In User Messaging Preferences console (http://:/sdpmessaging/userprefs-ui) add email Id for user AIAIntegrationAdmin. Login to this console with AIAIntegrationAdmin/welcome1.

NOTE: Message channels are meant to be a single email address or phone number etc.But you can refer to an email distribution list if you want to send these emails to multiple persons.

4. Upload the AIAConfigurationProperties.xml file to MDS and Reload the configuration from AIA Console setup page.Make sure when you update UpdateMetaDataDP.xml for loading the AIAConfigurationProperties.xml into MDS it should look something like below:

<fileset dir="${AIA_HOME}/aia_instances/instance_name/AIAMetaData">
<include name="config/AIAConfigurationProperties.xml" />
</fileset>

Also if the Go buttons are not enabled on AIA Console Setup page, make sure the weblogic user has the role AIAApplicationUser associated with it. You can associate roles on the Weblogic Admin Console under Security Realms.

5. Also set Notification mode to 'Email' from 'SOA Administration' -> 'Workflow Notification Properties' in EM Console.

Error Handling in SAP Adapter

I have come across a couple of scenarios where even though data gets posted successfully to SAP via BAPIs, but the adapter still throws an error message. At the BPEL layer the adapter throws a generic error like  "Error in Processing Input Document"

Something similar to below is shown in JCA log files:

<RETURN>
      <item>
         <TYPE>S</TYPE>
         <ID>RW</ID>
         <NUMBER>605</NUMBER>
         <MESSAGE>Document posted successfully: 060014911815002010 PRDCLNT500</MESSAGE>
         <LOG_NO/>
         <LOG_MSG_NO>000000</LOG_MSG_NO>
         <MESSAGE_V1></MESSAGE_V1>
         <MESSAGE_V2>060014911815002010</MESSAGE_V2>
         <MESSAGE_V3>PRDCLNT500</MESSAGE_V3>
         <MESSAGE_V4/>
         <PARAMETER/>
         <ROW>0</ROW>
         <FIELD/>
         <SYSTEM>PRDCLNT500</SYSTEM>
      </item>
      <item>
        <TYPE>W</TYPE>
         <ID>KI</ID>
         <NUMBER>155</NUMBER>
         <MESSAGE>Profitability segment is derived as new</MESSAGE>
         <LOG_NO/>
         <LOG_MSG_NO>000000</LOG_MSG_NO>
         <MESSAGE_V1/>
         <MESSAGE_V2></MESSAGE_V2>
        <MESSAGE_V3/>
         <MESSAGE_V4></MESSAGE_V4>
         <PARAMETER/>
         <ROW>0</ROW>
         <FIELD/>
         <SYSTEM>PRDCLNT500</SYSTEM>
      </item>
   </RETURN>

MySAP response error: BapiWarning: Profitability segment is derived as new

IWAFManagedConnectionFactory com.ibi.sap.SapConnection rollback(266) Call BAPI_TRANSACTION_ROLLBACK

IWAFManagedConnectionFactory com.ibi.sap.SapAdapter20 inProcess(397) java.lang.Exception: BapiWarning: Profitability segment is derived as new
at com.ibi.sap.DocumentRunner.processIfrDocument(DocumentRunner.java:274)
at com.ibi.sap.SapAdapter20.inProcess(SapAdapter20.java:369
…
…

This ultimately rolls back to the error "Error in Processing Input Document" at BPEL layer.

To overcome this situation SAP adapter provides an option for error handling . From Application explorer if you edit the SAP target then there are 2 choices under Advanced tab : Throws Exception and Creates Error Document.

If you set it to "creates error document" and restart the server, the BAPI invocations should work correctly and above RETURN section should be shown in the BPEL layer as well.

BPEL Console fails with "ORA-28001: the password has expired"

I recently came across an issue while accessing BPEL console, and was shown a SQL Exception "ORA-28001: the password has expired" on the BPEL console page. On checking the Oc4j_soa log files found the same error:

java.sql.SQLException: ORA-28001: the password has expired
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:138)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:316)
...
...

On debugging further found that the orabpel and oraesb account passwords had EXPIRED on the Fusion DB. That was by design and implementation of the Oracle DEFAULT profile to which the users belonged. So to fix the issue the user accounts had to be unlocked/unexpired so that status showed as OPEN.

You can also check with your DBA team to see if the DEFAULT profile can be changed to allow unlimited days for a password and unlimited attempts at logging in (before account is locked).

AIA 3.0 : Service Constructor support for DB adapter and JCA adapters.

As per the AIA developer's guide
"Currently the Service Constructor supports only services that have an exposed interface and cannot yet directly invoke JCA adapters such as the File or DB adapter."

Incase you want to invoke DB adapter in your ProviderABCS or receiving DB records in your RequestorABCS, the Oracle recommended way is to create the adapter as a separate composite and use the same in your AIA Service Constructor.

I came across a similar situation where my RequestorABCS had to be kicked off when SAP sends an idoc (via SAP Channel) or if I had to poll records from a DB table. In both cases I had to create the SAP adapter/DB adapter as a separate composite and invoke the RequestorABCS wsdl from there.
This approach also ensures reusability of the adapter composite code incase other ABCS want to use it.

Targets for MySAP : No targets configured for this adapter.

This is a common error when configuring/working with SAP adapter. The error is seen while running composite BPEL processes which try to connect to SAP or from the iwafjca page.
http://hostname:port/iwafjca/service.jsp

Targets for MySAP
No targets configured for this adapter.

If you have SAP Channels configured for inbound events from SAP to BPEL then you may see errors like below :

SEVERE IWAFManagedConnectionFactory com.iwaysoftware.afjca15.AbstractResourceAdapter endpointActivation(194) An Exception was caught while activating the endpoint
java.lang.IllegalArgumentException: Channel 'SalesOrder_Channel' not available for adapter 'MySAP'.
at com.iwaysoftware.af.container.ChannelManager.checkConfig(ChannelManager.java:421)
...
...
Make sure your ra.xml file under SOA_HOME/soa/thirdparty/ApplicationAdapters/iwafjca.rar/META-INF directory has config properties IWayHome and IWayConfig set correctly. The above errors indicate that the adapter is not able to read the information in repository (file/db).

Check in your configuration folder SOA_HOME/soa/thirdparty/ApplicationAdapters/config/YourConfigName/ if there are multiple files like repository.xml1, repository.xml2 etc in addition to repository.xml. Similarly check if there are multiple st_repository files like st_repository.xml1, st_repository.xml2 in addition to st_repository.xml.

In that case, you might have done some CRUD operation when the iwafjca.rar is deployed and running. When ever you are doing CRUD operations in iwae, please make sure the iwafjca.rar is not deployed or is in inactive mode especially when you are using file repository.

To fix the errors :

1. Make sure your repository.xml contains correct target information.
2. Then redeploy the iwafjca.rar from admin console
3. Restart the managed server. It should work now !