Skip to Content
0
Former Member
Aug 11, 2011 at 07:11 AM

JDBC receiver adapter - stored procedure response

317 Views

I am on PI 7.11 and have the following scenario:

RFC->PI->JDBC

whereby the JDBC receiver access an Oracle db (unsure of that version) using a stored procedure "GET_ICBC_ID".

  • My issue is that I never seem to have any data back in PI on the JDBC response coming back from Oracle. ***

Here is the definition of that stored procedure in Oracle:

Stored Procedure Name: GET_ICBC_ID

Parameters

ICBCID OUT VARCHAR2 <- Returned RACF ID (output)

REQUESTOR IN VARCHAR2 default 'SAPHR' <- Optional Requestor ID.

COMMUNITY IN VARCHAR2 default 'EMP' <- Optional Community ID.

Here is my JDBC call in to the stored procedure (sxi_monitor), which seems to be fine:

<?xml version="1.0" encoding="UTF-8" ?>

- http://sap.com/xi/Z_SkillSoft">

- <Statement>

- <GET_ICBC_ID action="EXECUTE">

<REQUESTOR isInput="true" type="VARCHAR">SAPHR</REQUESTOR>

<COMMUNITY isInput="true" type="CHAR">EMP</COMMUNITY>

</GET_ICBC_ID>

</Statement>

</ns1:MT_racf_req>

It appears as though the call into the JDBC is working, as the Oracle guys have shown me the logs (showing success) on the Oracle side.

This is my sxi_monitor entry (of that response message):

<?xml version="1.0" encoding="utf-8" ?>

- http://sap.com/xi/Z_SkillSoft">

<Statement_response />

</ns1:MT_racf_req_response>

Also when I look at some of the message properties I see:

<SAP:MessageSizePayload>0</SAP:MessageSizePayload>

<SAP:MessageSizeTotal>13243</SAP:MessageSizeTotal>

<SAP:PayloadSizeRequest>0</SAP:PayloadSizeRequest>

<SAP:PayloadSizeRequestMap>0</SAP:PayloadSizeRequestMap>

<SAP:PayloadSizeResponse>179</SAP:PayloadSizeResponse>

<SAP:PayloadSizeResponseMap>149</SAP:PayloadSizeResponseMap>

which I believe is saying that something came back in to PI as a response?? Not sure.

My Message Type definition is:

http://www.w3.org/2001/XMLSchema" xmlns=" http://sap.com/xi/Z_SkillSoft" targetNamespace=" http://sap.com/xi/Z_SkillSoft">

<xsd:element name="MT_racf_req_response" type="DT_racf_req_response" />

<xsd:complexType name="DT_racf_req_response">

<xsd:sequence>

<xsd:element name="Statement_response" type="DT_statement_response" />

</xsd:sequence>

</xsd:complexType>

<xsd:complexType name="DT_statement_response">

<xsd:sequence>

<xsd:element name="ICBCID" type="xsd:string" />

</xsd:sequence>

</xsd:complexType>

</xsd:schema>

I have also tried inserting <row> between <Statement_response> and the ICBCID element. Still no luck.

I suspect something is wrong with my response message type definition, but am not seeing it. Also, am unsure how I can debug this - it appears as though Oracle side is fine, but nothing back into PI.

Any thoughts appreciated.

Keith