Technical Articles
Calling an Oracle Function synchronously from SAP PO using JDBC Receiver Adapter
I have been working as SAP PI/PO Consultant and in one of my recent engagements there was a requirement to call an Oracle function synchronously from SAP PO. I did not find enough information on SAP community discussions and some of the members even suggested that it is not possible to call Oracle function directly and it has to be invoked through a stored procedure. However, through some hints and SAP help documentation, I have managed to achieve this requirement. Hence, I thought of writing this blog post to help other members who are looking for information for a similar requirement.
This is my first blog post and your feedback and comments would help to make further improvements.
For explaining the end to end design, I have created an oracle function ‘get_order_details’ which accepts input argument ‘CustomerNumber’ and returns the order details associated with that particular customer (FirstName, State, OrderAmount) as shown below
Oracle function execution and output
The Oracle daTo call the above function from SAP PO, we need to create ESR objects as explained below.
Request Structure Details
Sender Data Type:
Input parameter to the function is just a single field ‘Customer Number’ and I have created request data type for sender system as below
Sender Datatype
Sender System Message Type:
This message type will use the data type created in the previous step
Sender Message Type
Receiver Data Type:
The structure has to be created as shown below. Please note that this structure is different from normal Insert, Update and Stored Procedure structures. How this structure works will be explained in the message mapping step.
Receiver Data type
Receiver Message Type:
Receiver Message type
Response Structure Details
Sender Data Type (for Receiver system):
The response from Oracle Function is a concatenated string consisting of Name, State and Amount separated by pipe | delimiter. Please note that in Response Data Type, the root node should have the same name as that of the root node of request data type with ‘_response’ appended to it. This is case sensitive and should be strictly followed. In my case, since Request Data type has root node as ‘StatementName’, the root node of Response Data Type is ‘StatementName_response’
Response Sender Data type
Sender Message Type (for Receiver system):
Another point to note here is that the response message type name should have the same as that of request message type but with ‘_response’ appended to it as shown below.
Response Sender Message type
Receiver Data Type (for Sender system):
This is the xml structure which Sender system will receive as response and there are no name restrictions here. The concatenated string returned from Oracle function will be split into three fields namely ‘FirstName’, ’State’, ’Amount’ as shown below.
Response Receiver Data type
Receiver Message Type (for Sender system)
Response Receiver Message type
Sender Service Interface:
This will be an outbound synchronous interface
Sender Service Interface
Receiver Service Interface;
Receiver Interface is Inbound and synchronous
Receiver Service Interface
Request Mapping:
This is the most critical step in this development.
The Oracle Function gets invoked through an SQL Query like ‘Select <OracleFunction(parameters) > from DUAL
I will use the action ‘SQL_QUERY’ to invoke this function from SAP PO.
Unlike in usual statement types, we do not need to specify any table name or stored procedure name here. The action and access element have a very important role.
Action has to be mapped to constant ‘SQL_QUERY’
In the access field, we have to pass the exact SQL query which we want to execute in Oracle database. This query remains unmodified and executes the oracle function.
For example, if I want to find details of CustomerNumber=123, I will execute the below query in Oracle
Select get_order_details(‘123’) from DUAL
If my requirement is just to get details of CustomerNumber=123, I can hardcode this query in a constant and map it to access element directly and it will do the job.
But the interface has to be dynamic and should fetch details of all customer numbers and for this the query has to be dynamic as well. For this purpose, we have a ‘key’ node under which we can specify the dynamic fields. These fields will act as placeholders for the query that we hardcode to the access field.
In this case, I have only one dynamic field and so I have just one field under key as ‘CustNum’
For this interface to work for all customers, the below query has to be hardcoded and mapped to access field as shown below
Select get_order_details(‘$CustNum$’) as DETAIL from DUAL
$CustNum$ is a placeholder and its actual value will be populated from the field key/CustNum before the SQL statement is executed.
CustomerNumber is mapped to CustNum field in the target structure
Please note that in my query, I have stored the output of the function in a variable ‘DETAIL’ by using ‘as’ keyword. Storing result in variable is necessary otherwise the response xml will have dynamic tag like <GET_ORDER_DETAILS(‘123’)> for each input value and it would be difficult to handle in response mapping. By using this variable, my element tag name will always be <DETAIL>
Response Mapping:
Please refer to below screenshot for response mapping. Note the element tag name is ‘DETAIL’ which I defined as variable in the previous step.
DETAIL is split into fields ‘FirstName’, ’State’, ’Amount’ using a simple UDF.
I have not provided details about Integration Directory configuration as it is same as other synchronous scenarios.
Please see the response payload below
End to end result on SOAP UI screen as below
Conclusion:
It is possible to call Oracle functions from SAP PO and this blog post explains how to achieve it. There is a different JDBC structure to be used when calling oracle functions using action ‘SQL_QUERY’ and the query passed to the access element is executed in the Oracle database without any modifications. The query can be made dynamic by using placeholders in the query and defining associated variables under the key node. At run time, the values in these variables get populated in the query before the actual execution of SQL statement is done.
References: