Nov 14, 2011 at 08:25 PM

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

233 Views

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?