cancel
Showing results for 
Search instead for 
Did you mean: 

Error when trying to invoke a Synchronous Stored Procedure on Oracle

former_member181962
Active Contributor
0 Kudos
214

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

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

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

ambrish_mishra
Active Contributor
0 Kudos

Hi,

Is the name of SP and table same ?

Ambrish

former_member181962
Active Contributor
0 Kudos

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
Active Contributor
0 Kudos

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
Active Contributor
0 Kudos

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

Ambrish

former_member181962
Active Contributor
0 Kudos

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
Active Contributor
0 Kudos

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