Error when trying to invoke a Synchronous Stored Procedure on Oracle

[former_member181962]

Hi Experts,

    I am trying to invoke a Synchronous Proxy in Oracle from SAP PI.

This is the request XML that is generated.

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

- <ns0:Price_Order_Request_MT xmlns:ns0="http://XXXXXXXXXXXXXXX/StoredProcedure">

- <Statement>

- <WRAPPER_HSP_PRICE_ORDER action="EXECUTE">

<table>WRAPPER_HSP_PRICE_ORDER</table>

<LINECNT isInput="true" type="INTEGER">1</LINECNT>

<CUSTOMER_ID isInput="true" type="VARCHAR">0000001234</CUSTOMER_ID>

<SALES_ORDER_LINE_NUMBER isInput="true" type="VARCHAR" />

<PRODUCTS isInput="true" type="VARCHAR">1</PRODUCTS>

<PRICE_DATE isInput="true" type="TIMESTAMP">2014-05-13 00:00:00</PRICE_DATE>

<CURRENCY isInput="true" type="VARCHAR">USD</CURRENCY>

<SALES_ORG_ID isInput="true" type="VARCHAR">5004</SALES_ORG_ID>

</WRAPPER_HSP_PRICE_ORDER>

</Statement>

</ns0:Price_Order_Request_MT>

But i get the following error.

com.sap.engine.interfaces.messaging.api.exception.MessagingException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'WRAPPER_HSP_PRICE_ORDER' (structure 'Statement'): java.sql.SQLException: ORA-06550: line 1, column 7: PLS-00201: identifier 'WRAPPER_HSP_PRICE_ORDER' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored

I have searched through the forums, but could not find a definitive answer.

Best Regards,

Ravikanth Talagana

[Former Member]

in Oracle, is it defined as a function or stored procedure? It should be stored procedure.

[ambrish_mishra]

Hi,

Is the name of SP and table same ?

Ambrish

[former_member181962]

Hi Ambrish,

yes they are the same.

Now i get a different error.

com.sap.engine.interfaces.messaging.api.exception.MessagingException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'WRPR_PKG_MN_ORDER.WRAPPER_HSP_PRICE_ORDER' (structure 'Statement'): java.sql.SQLException: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'WRAPPER_HSP_PRICE_ORDER' ORA-06550: line 1, column 7: PL/SQL: Statement ignored

In the oracle side, these are the data types that are declared:

linecnt    NUMBER,

CUSTOMER_ID    VARCHAR,

SALES_ORDER_LINE_NUMBER        CharArray1,

PRODUCTS    CharArray2,

PRICE_DATE    VARCHAR2,

CURRENCY    VARCHAR2,

SALES_ORG_ID    VARCHAR2

from PI side, here is the XML that we are passing

<WRAPPER_HSP_PRICE_ORDER action="EXECUTE">

<table>WRPR_PKG_MN_ORDER.WRAPPER_HSP_PRICE_ORDER</table>

<LINECNT isInput="true" type="FLOAT">1</LINECNT>

<CUSTOMER_ID isInput="true" type="VARCHAR">0000001234</CUSTOMER_ID>

<SALES_ORDER_LINE_NUMBER isInput="true" type="VARCHAR">1</SALES_ORDER_LINE_NUMBER>

<PRODUCTS isInput="true" type="VARCHAR">1</PRODUCTS>

<PRICE_DATE isInput="true" type="VARCHAR">2014-05-13 00:00:00</PRICE_DATE>

<CURRENCY isInput="true" type="VARCHAR">USD</CURRENCY>

<SALES_ORG_ID isInput="true" type="VARCHAR">5004</SALES_ORG_ID>

</WRAPPER_HSP_PRICE_ORDER>

can you please help?

[former_member181985]

Hi Ravi,

It seems the constant types that you are passing to few attributes of SP structure fields differ from Oracle datatypes.

e.g.,  for <LINECNT isInput="true" type="INTEGER">, it should be <LINECNT isInput="true" type="NUMBER">

for <SALES_ORDER_LINE_NUMBER isInput="true" type="VARCHAR" />, it should be <SALES_ORDER_LINE_NUMBER isInput="true" type="CharArray1" /> etc..

Regards,

Praveen Gujjeti

[ambrish_mishra]

Praveen is right.. change the type and run a test.

Ambrish

[former_member181962]

I tried that. System gives me an error that NUMBER is not a valid type.

As per the SAP documentation, here are the valid types that we can use.

INTEGER, BIT, TINYINT, SMALLINT, BIGINT, FLOAT, REAL, DOUBLE, NUMERIC, DECIMAL, CHAR, VARCHAR, STRING, LONGVARCHAR, DATE, TIME, TIMESTAMP, BINARY, VARBINARY, LONGVARBINARY, BLOB (input and output),CLOB (input and output), CURSOR (output; only in conjunction with the Oracle JDBC driver).

So, can you suggest if there are any other options to help troubleshoot?

[former_member181985]

Hi,

Could you also try to pass the entire SP input fileds (don't miss any optional) with their actual sequence as per SP definition

Regards,

Praveen Gujjeti