Skip to Content
avatar image
Former Member

JDBC receiver adapter - stored procedure response

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

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

3 Answers

  • Aug 11, 2011 at 07:46 AM

    Hi,

    Please refer the SAP Note 941317 for JDBC adapter incompatibilities. Also, please note that PI only supports Stored Procs for Oracle DB version 10.2.x or higher. If you Oracle DB is of a lower version, you might need to revisit the design.

    Also, for the stored procedure response, this should be in the form of a resultset else PI would not be able to recognize the response structure and would would get a blank response in your return.

    Try to execute the stored proc on the DB itself and confirm that the response is in the proper format. Also, ensure that there is no other return line, say something like "10 rows updated" before the return data as the jdbc adapter would not recognise the response in this case.

    Regards

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Shiladitya Sarkar

      Hi all - thanks for your helpful responses. I have set the question now to 'Answered'. I got it working by the following (structure item level indicated):

      Request message type (to stored procedure):

      1.MT_racf_req

      2. Statement

      3. GET_ICBC_ID

      4. action (set to 'EXECUTE')

      4. ICBCID

      5. isOutput (set to '1')

      5. type (set to 'VARCHAR')

      Response message type (from Stored Procedure):

      1.MT_racf_req_response

      2. Statement_response

      3. ICBCID

      Regards,

      Keith

  • avatar image
    Former Member
    Aug 11, 2011 at 07:16 AM

    Hi,

    Your response structure will have this fields,

    <StatementName_response>

    <update_count>count</update_count>

    <insert_count>count</insert_count>

    </StatementName_response>

    Refer this

    /people/bhavesh.kantilal/blog/2006/07/03/jdbc-receiver-adapter--synchronous-select-150-step-by-step

    http://help.sap.com/SAPhelp_nw70/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

    Add comment
    10|10000 characters needed characters exceeded

  • Aug 11, 2011 at 07:27 AM

    cross chk ur JDBC response structure in case of stored procedures:

    /people/siva.maranani/blog/2005/05/21/jdbc-stored-procedures

    Add comment
    10|10000 characters needed characters exceeded