SAP Adapter : Configuring Database Repository

SAP Adapter(JCA config) supports both file based as well as database based repository. I have had some bad experiences with file based repository where maintaining the repository.xml has been more or less a nightmare. I have covered some of these errors in earlier posts.

targets-for-mysap-no-targets-configured.html

We sometimes end up with duplicate repository.xml files or 0kb repository.xml files after server restarts. This causes most BPEL processes to fail with Adapter exceptions. So I finally decided to migrate all file based repositories to DB repositories (Not sure why I didn't do this earlier :-( ). Now Oracle doesn't provide any migration tools as such. But this isn't a huge effort and we just need to create the targets/channels afresh and it is a one-time task.

Anyways below mentioned is the list of steps to be followed to configure DB repository for SAP adapter.

1. Create a new schema user on database where you want the repository to reside. (Eg: saprepo_user/xxxxx)

2. Execute the iwse.ora SQL script on the machine where the database is installed. The iwse.ora SQL script is located in the following directory:
$MW_HOME\Oracle_SOA1\soa\thirdparty\ApplicationAdapters\etc

This script creates the required tables that are used to store the adapterconfiguration information in the database. These tables are used by Application Explorer and by adapters during design time and runtime. It is recommended that you use the same credentials to create the database repository and also in the ra.xml file for database user credentials.

3. Create the jcatransport.properties file and save it in the following directory:
$MW_HOME\Oracle_SOA1\soa\thirdparty\ApplicationAdapters\config\Config_Name

4. Enter values for iwafjca.repo.url, iwafjca.repo.user and iwafjca.repo.password fields in the newly created jcatransport.properties file, as shown in the following example:

iwafjca.repo.url=jdbc:oracle:thin:@dbhostname:port:sid
iwafjca.repo.user=saprepo_user
iwafjca.repo.password=xxxx

5. Navigate to the following directory:
$MW_HOME\Oracle_SOA1\soa\thirdparty\ApplicationAdapters\iwafjca.rar\META-INF

6. Open the ra.xml file and provide the JDBC connection information: (Same values as step3)
IWAYRepo_URL property.
IWAYRepo_User property.
IWAYRepo_Password property.

Save your changes to the ra.xml file.

7. Copy the ojdbc14.jar file to the directory $MW_HOME\Oracle_SOA1\soa\thirdparty\ApplicationAdapters\lib

8. Redeploy the iwafjca rar file from Admin console->deployments section and Restart Weblogic server.

9. Now open Application Explorer and create the SAP target again with similar details as before. You can then connect to the DB repository and check the af_config table to find your details stored there.

NOTE:
The above steps are for SOA 11g. For SOA 10g the directory structures will be different.
* jcatransport.properties file in following directory \adapters\application\config\
* copy ojdbc14.jar to \adapters\application\lib\
* Update oc4j-ra.xml (\j2ee\oc4j_soa\application-deployments\default\jca-app-adapter)

I haven't faced any issues so far after migrating from File to DB repository. Will definitely post if I find any discrepancies. Till then some relief :-)

Custom XSLT Function in SOA 11g

Its been a while since I blogged and has been busy few months. I recently got a requirement to strip all special characters from an input string in XSLT. Tried checking the list of pre-defined XSLT functions in Jdeveloper 11g to see if we had something similar. Though we have few string functions to normalize whitespaces and others which match against regex patterns. But none which replaced/stripped special characters/regex patterns from a string. So decided to write some custom XSLT function which could do this.

First we create the java class which implements the logic

package customxslt;
import java.io.PrintStream;
public class AdditionalStringFunctions
{
  public static String replaceChars(String input, String regex, String replaceWith)
  {
    String output = input.replaceAll(regex, replaceWith);
    return output;
  }
  public static void main(String[] args)
  {
    AdditionalStringFunctions aSF = new AdditionalStringFunctions();
    String input = "(+91)80-55555555";
    System.out.println(replaceChars(input, "[^0-9]", ""));
  }
}

All the above code does is, check for input string and any characters apart from 0-9 is stripped.

Next create a configuration file.Name the file as ext-mapper-xpath-functions-config.xml. Sample file is provided below

<soa-xpath-functions version="11.1.1"
                     xmlns="http://xmlns.oracle.com/soa/config/xpath"                 xmlns:regex="http://www.oracle.com/XSL/Transform/java/customxslt.AdditionalStringFunctions">
   <function name="regex:replaceChars">
    <classname>customxslt.AdditionalStringFunctions</className>
    <return type="string"/>
    <params>
      <param name="input" type="string"/>
      <param name="regex" type="string"/>
      <param name="replaceWith" type="string"/>
    </params>
    <desc/>
    <detail>
      <![CDATA[This function returns the value after stripping special characters.]]>
    </detail>
  </function>
 </soa-xpath-functions>

Create a folder called META-INF under the JDeveloper\mywork\Regex\TestRegex\classes directory and place the configuration file there.

Now create a jar file and make sure the class file and the ext-mapper-xpath-functions-config.xml are included in it.

Next register the JAR file in Jdeveloper designtime
This can be done via "Tools > Preferences… > SOA" add the JAR file and restart JDeveloper.

The newly created extension function will be available in the component palette under user defined functions page. You can make use it from there in your XSL code.

To make this function available at SOA runtime put the jar file under MW_Home/user_projects/domains/domain_name/lib

Restart the server and test your code.

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).