[PI-Oracle] – DBLookUP

7 12 2010

In my new article i want to show how you can develope in Sap Pi a dynamic DB Oracle LookUp by a JDBC Channel during message mapping.


In an XI mapping, it is a common requirement to be able to perform data lookups. Earlier the lookup functionality was achieved using JDBC/JCO API’s in an advanced function to connect to DB/ SAP System respectively. If the lookup were to be performed with any other application system (synchronous) SOAP/ webservice, the API’s used will change accordingly. In such a case, the developer should have the knowledge of how to integration using the API’s specific to the system. Hence the Lookup code becomes System specific.

But with SP13, using Lookup API in XI, the Lookup functionality has changed and now its even simpler. The developer need not worry about to which application system is the lookup call being made.The Lookup API’s are generic in nature which allow u to perform a lookup independent of the system to which the call is being made. Another feature to be noted is, whenever a mapping program is executed on the Integration Server, the lookup API calls the required application system by using the central Adapter Engine.


For the given UserName and Password details, the Role of the user has to picked up from the DB. Below is the screenshot of the DB-Table that has been used in the scenario.


Fig.1 DataBase Table Structure

Sequence of Steps:

In Configuration Time, create an appropriate communication channel to the System with which the lookup is being performed. For example, if the lookup is to be performed with a Database System, then a communication channel for the DB should be created. The Advanced function (in Message Mapping) will use this channel to perform the lookup’s.


Fig.2 Communication Channel used for Lookup

In Design Time, Create all the required design time Objects to perform a Message Mapping. (Datatypes, MessageTypes, MessageMapping).


Fig.3 Message Mapping

Create a new advanced Function to perform a DB lookup.


Fig.4 Creation of a new advanced Function


Fig.4Advanced Function editor

The following is the logical flow of code used in Advanced function that performs DB lookup.

  1. Construct your Query String
  2. Determine the channel by specifying the Party, Service, Communication channel. Or only System, Communication channel
  3. Get a SystemAccessor for the channel.
  4. Execute your Query and get the resultset.
  5. Loop through the resultset and get the required value. If you want to store more than 1 value, then use container and global container variables for storing them.
  6. Close the System Accessor

In this case, i’ve declared a global table HashMap like this:

HashMap hashMap = HashMap();

In this HashMap, i put all the value that i’ve selected in the lookup Query (in function DbLookUP_GeneralApi). In this function, the input is the DB Oracle Table Name (Object_Name) and the Table Keys (Object_Key) in format KEY1;KEY2;KEY3.

With the Function DbLookUP_GetData, you can get all the field that you have selected in the query, by input the field name (ex. “FIELD1“.

This is the implemented Java Functions:


Imports: java.lang.*;com.sap.aii.mappingtool.*;com.sap.aii.mapping.lookup.*;java.util.*;</span

public void DBLookUP_GeneralApi(String[] Object_Name,String[] Object_Key,ResultList result,Container container){

//write your code here
String key_value1;
String key_value2;
String key_value3;
key_value1 = “”;
key_value2 = “”;
key_value3 = “”;
String[] arr = Object_Key[0].split(“\\;”);
for (int i = 0; i < arr.length; i++) {
if (i == 0) {
key_value1 = arr[i];
} else if (i == 1) {
key_value2 = arr[i];
} else if (i == 2) {
key_value3 = arr[i];
String Query = ” “;
Channel channel = null;
DataBaseAccessor accessor = null;
DataBaseResult resultSet = null;
// Build the Query String
Query = “Select * from “+ Object_Name[0] +” where ORDINE = ‘” + key_value1 +”‘ AND OPERAZIONE = ‘” + key_value2 +”‘ AND CONFIRM_DATE = ‘” + key_value3 +”‘”;
//Determine a channel, as created in the Configuration
channel = LookupService.getChannel(“BS_ORACLE_DEV”,”CC_JDBC_Receiver”);

//Get a system accessor for the channel. As the call is being made to an DB, an DatabaseAccessor is obtained.
accessor = LookupService.getDataBaseAccessor(channel);
//Execute Query and get the values in resultset
resultSet = accessor.execute(Query);
for(Iterator rows = resultSet.getRows();rows.hasNext();){
Map rowMap = (Map)rows.next();
result.addValue((String)”LookUp Status OK”);
hashMap.put(“CAMPO1”, (String)rowMap.get(“CAMPO1”));
hashMap.put(“CAMPO2”, (String)rowMap.get(“CAMPO2”).toString());
// Add others fields
catch(Exception ex){
if (accessor!=null) accessor.close();
catch(Exception e){



Imports: java.lang.*;java.util.*;

public void DBLookUP_GetData(String[] FieldName,ResultList result,Container container){

//write your code here
if (FieldName[0] == “CAMPO1”)
if (FieldName[0] == “CAMPO2”)

// Add others fields


Article referred to blog page:

Lookup’s in XI made simpler
Siva Maranani
Company: Fujitsu America
Posted on Aug. 23, 2005 07:21 PM in SAP Process Integration (PI)




Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo di WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione /  Modifica )

Google photo

Stai commentando usando il tuo account Google. Chiudi sessione /  Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione /  Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione /  Modifica )

Connessione a %s...

%d blogger hanno fatto clic su Mi Piace per questo: