on 03-22-2006 6:13 AM
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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.
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
80 | |
24 | |
11 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.