cancel
Showing results for 
Search instead for 
Did you mean: 

JDBC Stored Procedure problem

Former Member
0 Kudos

JDBC problem:

i am trying to execute the storedprocedure which has got 3 parameters(2 input and 1 output). I created the following data type:

Request data type:

DT_jdbc_update

-->StatementName

-->storedProcedureName

action (EXECUTE)

table (Actual storedprocedure name)

-->PvSapDoc

isInput (true)

type (CHAR)

-->PvAmountSap

isInput (true)

type (CHAR)

Response data type:

DT_jdbc_update_response

-->statementName_response

-->row

-->pvRETURN

isOutput (true)

type (CHAR)

But when i am trying to execute the scenario, getting this error:

com.sap.aii.af.ra.ms.api.DeliveryException: Error processing request in sax parser: Error when executing statement for table

/stored proc. 'ProcSAPSPDFICO086' (structure 'StatementName'): java.sql.SQLException: ORA-06550: line 1, column 7: PLS-00306:

wrong number or types of arguments in call to 'PROCSAPSPDFICO086' ORA-06550: line 1, column 7: PL/SQL: Statement ignored

Am i doing any mistake in request data type parameters as it's saying that wrong number arguments.

Just for the information here is the storedprocedure code:

_________

CREATE OR REPLACE PROCEDURE ProcSAPSPDFICO086(PvSapDoc VARCHAR2,

PnAmountSap VARCHAR2,

pvRETURN out VARCHAR2)

IS BEGIN

UPDATE AHMFASPD_TEMPREQS

SET VNODOC = PvSapDoc,

NAMOUNTSAP = PnAmountSap

WHERE VID = 'FICO086';

EXCEPTION

WHEN OTHERS THEN

pvRETURN := 'error SAPSPDFICO086 update AHMFASPD_TEMPREQ ';

END;

________

How can i resolve this...??

Quick response is higly appreciable.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Murali,

Please check whether your structure really fits to the one required by the JDBC adapter:

<MT_jdbc_update>
<StatementName2>
  <dbTableName action=”INSERT”>
    <table>realDbTableName</table>
    <access>
      <col1>val1</col1>
      <col2>val2</col2>
    </access>
    <access>
      <col1>val11</col1>
    </access>
  </dbTableName>  
</StatementName2>
</MT_jdbc_update>

It is crucial to set up all the hierarchy levels as explained by the documentation.

Best regards

Joachim

bhavesh_kantilal
Active Contributor
0 Kudos

Hi,

Just check if this is how your Stored Proceudre is,

<DT_jdbc_update>

<StatementName>

<storedProcedureName action="EXECUTE">

<table> name of stored proc </table>

<PvSapDoc> xxx</pvsapdoc>

<pvamountsap> yyy </pvamountsap>

</storedprocedure>

</statementname>

</dt-jdbc_update>

I am not sure if, the a stored procedure can be used to return some value back in case of a Receiver JDBC adapter.

Can you try this without having any Return occuring in your stored procedure and check.

Regards,

Bhavesh

bhavesh_kantilal
Active Contributor
0 Kudos

Hi Murali,

Found the reason.

1. You will have to specify the type of the attriubte. (Datatype of the attribute)

2. You will have to specify whether the attribute isinput, isoutput .

Just look at this excerpt from this link,

http://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

<i>action=EXECUTE

Statements with this action result in a stored procedure being executed. The name of the element is interpreted as the name of the stored procedure in the database. If you use the optional <table> element, the value specified here is used as the stored procedure name. This enables you, for example, to define stored procedure names containing non-XML-compatible characters or characters that prevent them from being used in interface definitions in the Integration Builder/PCK. If specified, <table> must be the first element in the block within <dbTableName>.

The elements within the stored procedure are interpreted as parameters. They can optionally have the attribute isInput=“1“ (input parameter) or isOutput=“1“ (output parameter) or both (INOUT parameter). If both attributes are missing, the element is interpreted as an input parameter. The parameter names must be identical to those of the stored procedure definition.

The attribute type=<SQL-Datatype> , which describes the valid SQL data type, is mandatory for all parameter types (IN, OUT, INOUT).</i>

<i>The following SQL data types are supported:

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).</i>

Do the needful and it will work.

Regards,

Bhavesh

Former Member
0 Kudos

Hi Bhavesh and vishal,

I included the output parameter in the request data type with isOutput = true. But while doing mapping, i disabled that field as i don't have any thing to map it(It's return parameter). Still the same problem.

If i removed the output parameter from the storedprocedure then it's working fine. But i need to get response back as i need to update it into SAP.

Thanks.

Former Member
0 Kudos

Hi,

One more question: Can i execute the function insted of storedprocedure in the oracle. If so how can i do that...?

Thanks in adv.

bhavesh_kantilal
Active Contributor
0 Kudos

Hi Murali,

ASFAIK,I dont think an Oracle Function can be called from your JDBC adapter.

Regards,

Bhavesh

Former Member
0 Kudos

Hi bhavesh,

Still problem persists.

Can you pls.give me your yahoo id or any personal id so that i can contact you.

Thanks.

bhavesh_kantilal
Active Contributor
0 Kudos

hi murali,

my email id is in my Business Card

Regards,

Bhavesh

Former Member
0 Kudos

Hi Bhavesh,

It's working now...thanks for the info.

I assigned the points too...

Regards,

bhavesh_kantilal
Active Contributor
0 Kudos

Hi murali,

Great to know that it worked. Can you let us know wat was the reason for the problem that you faced?

Regards,

Bhavesh

Former Member
0 Kudos

Hi Murali,

I am also facing a similar issue and no one has clue about this. As you have solved it, please provide a solution.

the oracle stored procedure has three parameters i.e, two input paramters and 1 output paramter.

I am passing all the parameters in the target structure, but i still get the same error.

i have use isInput = "true" for two of the input paramters and isOutput = "true" for my output paramter. For the output parameter, I have mapped it to an empty constant and sending it blank.

Please suggest.

Thanks.

Krishnan

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi Murali

Include output of stored Procedure(pvRETURN) in your request datatype (set its attribute: isOutput='true') and then proceed.

Reply in case the problem persists.

Hope this helps.

bhavesh_kantilal
Active Contributor
0 Kudos

Hi Murali,

The error in your JDBC adapter states that the the input variables that are expected by the Stored Procedure do not match with the input actually given..

<b>PvAmount in JDBC Datatype and PnAmount in Stored Procedure.</b>

Make a change in either of the 2 and it should work fine.

Regards,

Bhavesh

Former Member
0 Kudos

Hi Bhavesh,

Thanks for your response.

I changed the jdbc data type to numeric but no use. Getting the same error.

Thanks.

bhavesh_kantilal
Active Contributor
0 Kudos

Hi Murali,

The variable name declared for your stored procedure and the same defined in your JDBC adapter do not match.

Just make sure that the input being passed to the stored procedure are same. ( same column / field name ).

Regards,

Bhavesh

Former Member
0 Kudos

Hi Bhavesh,

I got overlooked the name. Now i changed according to the storedprocedure. Still the problem is persisting.

Should i need to have return parameter in the request data type..??

Thanks.