cancel
Showing results for 
Search instead for 
Did you mean: 

Invoking ORACLE Stored Procedure

Former Member
0 Kudos

Hello guys,

I have an ORACLE stored procedure which I need to invoke. I'm currently unable to invoke the stored procedure since I'm always getting the same error:

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

I've faced several projects where we needed to invoke stored procedures but these were located in a DB2 or Microsoft SQL Server, and these problems never occured.

The procedure is set to the minimum, and is now only with one input parameter. I had an input and output parameter but for narrowing the problem I've removed the output.

The structure for the stored procedure is the one that is referred by SAP or mentioned thousand of times in this forum:

Statement_name

StoredProcedureName (attribute ACTION)

TABLE

P_TRANSACTION_ID (Attributes isInput, isOutput, type)

For the basics I'm only filling with one statement.

I've read some threads with a similar problem but with no response, or solved but with no suggestion on how they solved it.

Could you please help out?

Kind regards,

Gonçalo Mouro Vaz

Accepted Solutions (0)

Answers (1)

Answers (1)

former_member187339
Active Contributor
0 Kudos

Hi,

Is the structure in Oracle side is of the following format?


 <StatementName>
<storedProcedureName action=u201D EXECUTEu201D>

    <table>realStoredProcedureeName</table>

<param1 [isInput=u201Dtrueu201D] [isOutput=true] type=SQLDatatype>val1</param1>

</storedProcedureName > 

  </StatementName>

Can you paste the structure here?

Regards

Suraj

Former Member
0 Kudos

Hello Suraj,

Yes, the structure is the following:

<STATEMENTNAME>

<SET_PICKUP ACTION="EXECUTE">1</SET_PICKUP>

<TABLE>test.set_pickup</TABLE>

<P_TRANSACTION_ID isInput="true" type="VARCHAR">321</P_TRANSACTION_ID>

</STATEMENTNAME>

Kind regards,

Goncalo Mouro Vaz

Former Member
0 Kudos

Hi,

Have you consider all the input and output parameters of the stored procedure.

Input paramters will be like

<P_TRANSACTION_ID isInput="true" type="VARCHAR">

and all the output parameters need to be defined in structure with isOutput="true".

here you don't need to pass any values to output structure fields becuase the attribute isOutput will handle.

thanks

Swarup

Former Member
0 Kudos

Hello Swarup,

Yes, like I said I've narrowed the stored procedure to the minimum and have defined it only to receive an input parameter. It really seems to be something related to ORACLE....

Kind regards

former_member187339
Active Contributor
0 Kudos

Hi,

Can you try doing this:

1. Remove "1" form <SET_PICKUP ACTION="EXECUTE">1</SET_PICKUP> . Check the message mapping for the same.

2. In test.set_pickup, test is your package and set_pickup is your SP's name correct?

But i am sure this cannot be a problem with PI. Can you paste the code of you SP here?

Also make sure the field SET_PICKUP is of type VARCHAR in DB too

Regards

Suraj

Edited by: S.R.Suraj on Sep 23, 2009 8:19 AM