DT for calling stored procedure

[Former Member]

Hi All,

i have a scenario where sender is as400 db2 and receiver is oracle db stored procedure(SP).

the problem im facing is when i try to call the SP synchronously im getting an 'incorrect number of arguments or types' as the error in the adapter.

We tried calling the SP from a java prog and it was successful

CallableStatement pstmt = con.prepareCall("{call p_run_moca(?,?)}");

System.out.println("SP string is set");

String xmldoc = xmlfile.toString();

pstmt.setString(1,xmldoc);

System.out.println("DB SP 1st argument set");

System.out.println("Check if this is printing2");

pstmt.registerOutParameter(2, Types.VARCHAR);

System.out.println("registered output parameters");

pstmt.executeUpdate();

System.out.println("Function Call made");

String o_retInt = pstmt.getString(2);

System.out.println("Returned Code is: " +o_retInt);

is the crux of the program where im calling the SP.

In XI for calling this i created a DT with the following structure

Statement

>p_run_moca

>action --- execute

P_XML --- mapped to data

>isInput --- true

>type -- varchar

FLAG -- mapped to Null

>isOutput --- true

>type --- varchar

response DT is

Statement_response

>response_1

>row

> FLAG

other options we tried are

removed the FLAG element(with attributes) from Statement

tried giving CHAR instead of VARCHAR

removed response_1(node only) from Statement_response

but still got the same error

Should we try giving any other value in type or make any other changes in the response DT

Please give your suggestions.

[former_member187339 · Accepted solution]

Hi,

Check this link:

http://help.sap.com/saphelp_nw04/helpdata/en/e2/cb610b230c9c4cb4b800989196b63a/frameset.htm

<i>All return values are returned in an XML structure. The results within the stored procedure are returned either as a table or as the element <update_count>. This depends on the SQL statements executed within the stored procedure. The return parameters of a stored procedure are attached in a separate structure.</i>

Check your SP... What value is it returning??

Regards

Suraj

[Former Member]

txs

[Former Member]

Hi Bhavesh,

isOutput itself is an attribute right?

Jai AFAIK it is only with the sender adapter we have the cursor problem and not with the reciever adapter.I still dont have an idea why.

[Former Member]

>>Jai AFAIK it is only with the sender adapter we have the cursor problem and not with the reciever adapter.I still dont have an idea why

I would like to know the answer as well...

May be Bhavesh can help us in this...??

I beleieved cursors can not be processed by XI and hence the probelm with sender adapter. If this statement holds good, then receiver adapter (synch) also receiving cursor into XI is not possible.

But from Bhavesh's reply I seem to be missing some thing...

Let us wait to hear from our expert...:-)

Regards,

Jai Shankar

[bhavesh_kantilal]

Jai,

Like I told, I have worked on making an Asynch Call to a Oracle Stored Procedure and it worked fine.

The Synch Call I had made using Stored Procedures was to a SQL Server and so cannot confirm if a Synch Call to a Oracle Stored Procedure works. But, Technically the same problem also should hold true for Sender and Receiver JDBC adapters

Meanwhile, Jayaraman, can you actually post your target response XML here so that I can try to decipher if something is missing.

Also, if possible can you try to make an Asynch call to the Stored Procedure to check what is happening.

Thanks,

Regards

Bhavesh

[Former Member]

Hi Bhavesh,

Asynch call is working for us we are facing Problem in Synch call

Thanks

Jayaraman

Message was edited by:

Jayaraman P

[Former Member]

Hi Bhavesh,

We have given exactly the same structure as SP.

say in SP we have an IN parameter of type VARCHAR name P_XML

and OUT parameter of type VARCHAR name FLAG

we have created the input DT with the two parameters and mapped isInput->true for P_XML and isOutput->true for FLAG

>>Just note that the element for Output, with isOutput=true , should have been mapped to a blank constant with the attrributes filled in the request mapping.

i understand that isOutput field should be mapped to a null constant but what do u mean by "with the attrributes filled in the request mapping"

can you please explain

[bhavesh_kantilal]

Jayaraman,

What I meant was IsOutput fields should be mapped to the constants including the attributes when the request mapping is triggered and message reaches the JDBC adapter.

Jai~ Have made an Asynch call to a Oracle Stored Procedure and it worked fine. Have not tried a synch call to a Oracle Stored Procedure though.

Regards

Bhavesh

[Former Member]

Hi All,

Is it possible to call a stored procedure in ORACLE? I beleive ORACLE stored procedure returns "cursors" which can not be processed by XI.

Is this possible in the latest Service packs?

Kindly update me...

Regards,

Jai Shankar

[former_member187339]

Hi,

Have a look at these notes: 941317

and forum link

Regards

Suraj

[bhavesh_kantilal]

Jai,

It is possible to call oracle stored procedures irrespective of the service pack using Receiver JDBC adapter.

the problem with the cursors / resultset comes only for sender JDBC adapter.!

Regards

Bhavesh

[Former Member]

Bhavesh,

>>It is possible to call oracle stored procedures irrespective of the service pack using Receiver JDBC adapter

Is it the same with synchronous call?

Regards,

Jai Shankar

[Former Member]

Hi Suraj,

We are not able to call the SP itself, so we are not able to see what it is returning.

The adapter says wrong number of arguments or types.

But as you can see in my question i have the same number of arguments in the DT and stored procedure

[bhavesh_kantilal]

Jayaram,

The output of your mapping , ie the input to the JDBC adapter should also contain the same strucuture you have listed above.

Just note that the element for Output, with isOutput=true , should have been mapped to a blank constant with the attrributes filled in the request mapping.

Is this done?

Regards

Bhavesh