PI - Problem during mapping a table on a StoredProcedure (JDBC)

[AnaLavieri]

Hello,

I'm developing a PI interface with this scenario: ECC -> PI -> Database (JDBC).

In the JDBC Communication Channel a Stored Procedure is triggered.

The problem is that one of the input parameters of the procedure is a table with a complex type.

I've already search in the SAP documentation, and only plain types are accepted (INTEGER, BIT, TINYINT, SMALLINT, BIGINT, FLOAT, REAL, DOUBLE, NUMERIC, DECIMAL, CHAR, VARCHAR, STRING, LONGVARCHAR, DATE, TIME, TIMESTAMP, BINARY, VARBINARY, LONGVARBINARY, BLOB and CLOB) for input parameters.

Below is the StoredProcedute Definition:

GetPriceSAP(p_shape in varchar2, p_size in varchar2, p_grade in varchar2,p_rolling_mill in varchar2, p_parameters in tbl_price_quote_parameter, p_request_id out oe_price_quote_hed.request_id%type) return tbl_price_quote_results as r_results tbl_price_quote_results;

Note that the variable p_parameter is a table with 3 fields:

- id (NUMBER)

- value (VARCHAR)

- uom (VARCHAR)

I've tried mapping the interface, as shown in the examplebelow:

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

- <ns0:mtPricingReqJboss xmlns:ns0="http://gerdau/jboss/PricingCalculation">

- <Statement>

- <storedProcedureName action="EXECUTE">

<table>OE_PRICE_PKG.GetPrice</table>

</storedProcedureName>

- <access>

<p_shape isInput="true" type="VARCHAR">C</p_shape>

<p_size isInput="true" type="VARCHAR">8 X 22.8#MC</p_size>

<p_grade isInput="true" type="VARCHAR">A36/572G50</p_grade>

<p_rolling_mill isInput="true" type="VARCHAR">Y</p_rolling_mill>

- <p_parameters isInput="true" type="VARCHAR">

- <row>

<id type="NUMBER">2</id>

<value type="VARCHAR">4000</value>

<uom type="VARCHAR" />

</row>

</p_parameters>

</access>

</Statement>

</ns0:mtPricingReqJboss>

In the Communication Channel Monitoring log the error message is displayed:

"Unable to execute statement for table or stored procedure. 'OE_PRICE_PKG.GetPrice' (Structure 'Statement') due to java.sql.SQLException: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'GETPRICE' ORA-06550: line 1, column 7: PL/SQL: Statement ignored ".

Could someone know how to mapping a table with more than one column as an input parameter on a StoredProcedure?

[AnaLavieri]

Thanks.

I've used the XSLT mapping, and it works!

[Former Member]

R u also going to get multiple values and repeating row node for p_parameters table ? if you are going to get the multiple values and repeating row then in that case you can not do using std JDBC structure suggested by SAP. You have to do work around in PI to manage such scenarios.

Option you can try:-

1. First mapping you prepare the XML structure which is expected by JDBC. that will be intermediate structure and mapping.

2. Second mapping you map convert that intermediate structure to single XML CLOB using copy as XML function and pass as single input parameter to JDBC stored procedure.

3. Inside stored procedure you have to first extract the CLOB XML and read individual parameters and carry ahead the process.

Let me know if you need further information.

thanks

chirag

Edited by: Chirag Gohil on Nov 16, 2011 3:23 PM

[Former Member]

Chirag Gohil,

I'm facing a similar situation, I need to use complex type structures as input for a stored procedure (it is a soap to jdbc scenario). Could you please elaborate a bit more on the second mapping, specially regarding the copy as XML function. Should that be a user defined function? How can achieve that?

Any other advice regarding this scenario or other alternatives are welcome.

Thanks in advance.