Oracle Stored procedure response

[Former Member]

Hi all,

I want to know response structure of Oracle Stored procedure.

I am able to insert the records into a table using a Stored procedure. Now i wanted to return some parameters from it, like insert_count and update_count.

In SAP help Link on [document format of Receiver JDBC Adapter|http://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/frameset.htm]

For Stored procedure types it says:

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.

So how can i return update_count from stored procedure and make appropriate response structure. Kindly help

[Former Member · Accepted solution]

Hi ,

For Oracle database you can use out parameter in u r stored procedure e.g .

Create or Replace Procedure Dtable.update_flag ( COUNT OUT varchar , Var1 IN varchar2 ) .

And after you witer u r update query in store procedure you can assign update count to the out parameter using SQL%Rowcount. e.g.

COUNT := SQL%ROWCOUNT ;

When this procedure is callled it will send back the update count in the COUNT parameter.

Regards,

Ganesh.

[Former Member · Accepted solution]

Hi Ganesh,

Very helpful answer. I did exactly as you said.

My stored procedure is inserting records one-by-one. So my response is comping in this format while inserting 2 records

<?xml version="1.0" encoding="UTF-8"?>

+<ns0:MT_FileOutput xmlns:ns0="urn:JDBCScenario">

<row>

<insert_count>1</insert_count>

</row>

<row>

<insert_count>1</insert_count>

</row>

</ns0:MT_FileOutput>+

But i want this format

<row>

<insert_count>2</insert_count>

</row>

So can stored procedure handle this?

Regards

[Former Member · Accepted solution]

Simplest solution would be to have a simple Message Mapping for the response which does a count on all insert_count node values.

[Former Member · Accepted solution]

In u r response mapping use standard function Count on insert_count field .

Kindly close the thread if answered.

Regards,

Ganesh

Edited by: Ganesh Khandekar on Oct 23, 2008 7:23 AM

[Former Member]

Hi Arpil Gupta,

chk the below link it will be helpfull

to get the response from the we need to use BPM

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