receiver JDBC oracle Stored Procedure.issue

[vijay_kumar133]

HI ,

In scnario sync soap to jdbc i am using oracle stored procedure at receiver end.

i have configured receiver side stored prodecure and it executing correctly with out any error.

But the output is not getting in the response.

when i test the stored procedure in sql plus i require to set this parameter as

SET SERVER OUTPUT ON

then i can see the output.

now do i need to configure any thing to get the response into PI.

Regards

Vijay G

[Former Member · Accepted solution]

Hi Vijay,

I am just giving one suggestion about the JDBC response.

For example : Request message type for JDBC stored procedure is "MT_StoredProcedure"

Response message type should be "MT_StoredProcedure_response"

Kindly check with your message type you correctly created or not, and please check the stored procedure you mentioned Output parameters are there.

Thank you very much.

Sateesh

[vijay_kumar133 · Accepted solution]

Hi kumar,

I have taken care of the structure ...

input to stored procedure

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

- <ns0:MT_P_AFCReq2 xmlns:ns0="http://xyz.com/ws002">

- <Statement>

- <GET_PAGE_DATA ACTION="EXECUTE">

<TABLE>GET_PAGE_DATA</TABLE>

<W_CS_DATA_ID type="VARCHAR">50001</W_CS_DATA_ID>

</GET_PAGE_DATA>

</Statement>

</ns0:MT_P_AFCReq2>

output from stored procedure

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

- <ns0:MT_P_AFCReq2_response xmlns:ns0="http://xyz.com/ws002">

<Statement_response />

</ns0:MT_P_AFCReq2_response>

you can have a look at the structure ..

now all i need is a sample code of oracle stored procedure that actualy returns values when we use at receiver side sync.

so that i can explan to oracle team to develop in such a way to full fill my requirement.

Regards

Vijay

[vijay_kumar133 · Accepted solution]

Hi kumar,

I have taken care of the structure ...

input to stored procedure

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

- <ns0:MT_P_AFCReq2 xmlns:ns0="http://xyz.com/ws002">

- <Statement>

- <GET_PAGE_DATA ACTION="EXECUTE">

<TABLE>GET_PAGE_DATA</TABLE>

<W_CS_DATA_ID type="VARCHAR">50001</W_CS_DATA_ID>

</GET_PAGE_DATA>

</Statement>

</ns0:MT_P_AFCReq2>

output from stored procedure

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

- <ns0:MT_P_AFCReq2_response xmlns:ns0="http://xyz.com/ws002">

<Statement_response />

</ns0:MT_P_AFCReq2_response>

you can have a look at the structure ..

now all i need is a sample code of oracle stored procedure that actualy returns values when we use at receiver side sync.

so that i can explan to oracle team to develop in such a way to full fill my requirement.

Regards

Vijay

[Former Member · Accepted solution]

Hi Vijay,

Where is response output paramters

There is output parameters required for request messages.if there is no output parameters it can't get the response back.

isInput ,isOutput parameters are required for stored procedure structure.

kindly follow this link

http://help.sap.com/saphelp_nw04/helpdata/en/4d/8c103e05df2e4b95cbcc68fed61705/frameset.htm

kindly tell me your response field/

Thank you

Sateesh

Edited by: sateesh kumar .N on Jul 6, 2010 9:51 AM

[vijay_kumar133 · Accepted solution]

Hi kumar,

thanks for reply...

there are no output parameters for the stored procedure given from legacy system.

currenlty we have a input parametrs and after executing the procedure if we print we can see out put in sql plus. current code of stored procedure is enclosed in the above message.

Do we require a stored procedure with out put paramets or with a resultset out put .

if you any sample code that would be great help.

Regards

Vijay

[vijay_kumar133 · Accepted solution]

Hi kumar,

thanks for reply...

there are no output parameters for the stored procedure given from legacy system.

currenlty we have a input parametrs and after executing the procedure if we print we can see out put in sql plus. current code of stored procedure is enclosed in the above message.

Do we require a stored procedure with out put paramets or with a resultset out put .

if you any sample code that would be great help.

Regards

Vijay

[Former Member · Accepted solution]

Hi Vijay,

For example your JDBC stored proc request message like this below:

Request Message:

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

<ns0:MT_P_AFCReq2 xmlns:ns0="http://xyz.com/ws002">

<Statement>

<GET_PAGE_DATA ACTION="EXECUTE">

<TABLE>GET_PAGE_DATA</TABLE>

<W_CS_DATA_ID isinput= "true" type="VARCHAR">50001</W_CS_DATA_ID>

<XYZ isOutput="true" type="VARCHAR"/>

</GET_PAGE_DATA>

</Statement>

</ns0:MT_P_AFCReq2>

Your Response message should be below:

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

- <ns0:MT_P_AFCReq2_response xmlns:ns0="http://xyz.com/ws002">

<Statement_response >

<XYZ >VIJAY</XYZ>

</Statement_reponse>

</ns0:MT_P_AFCReq2_response>

Your microsoft SQL Server stored procedure:

USE [SCMT]

GO

/****** Object: StoredProcedure [dbo].[XI_SP_NAME] Script Date: 07/0/2010 10:32:55 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[XI_SP_NAME]

(

@W_CS_DATA_ID [varchar](30),

@XYZ [varchar] (30) OUTPUT

)

AS

SET NOCOUNT ON;

Insert Statement.........

select Statement..........

Thanks

Sateesh

[vijay_kumar133 · Accepted solution]

Hi kumar,

thanks a lot at finaly i finished the interface ...

the main issue is with stored procedure which i used. so i have changed the code and finaly it starting working fine..

now i got struck with few steps in oracle coding which i will take help from oracle team...

i am enclosing the code of the stored procedure here..

Create or replace procedure page_get_data_test2(
w_cs_data_id in number,
o_cs_data_id out page_op_validation.cs_data_id%type,
o_RECORD_DATE out page_op_validation.RECORD_DATE%type,
o_VALIDATION_TYPE out page_op_validation.VALIDATION_TYPE%type,
o_station_id out page_op_validation.station_id%type) as

begin 

  SELECT CS_DATA_ID, RECORD_DATE,VALIDATION_TYPE,station_id 
into o_cs_data_id,o_RECORD_DATE,o_VALIDATION_TYPE ,o_station_id
FROM page_op_validation 
WHERE CS_DATA_ID = w_CS_DATA_ID and rownum = 1;

end page_get_data_test2;

here the problem is with number of records if any one have idea on oracle can help how we can use cursurs in oracle and fetch multiple records into out put parameters..

Regards

Vijay G

[rajasekhar_reddy14]

Hi,

are you sure you have configured the JDBC rECEIVER CHANNEL as Sync mode??

Regards,

Raj

[vijay_kumar133]

hi

Yup sure i have done and check also i am receiving the resultalso i mean response but its blank.

Even i can test complete scnario ther is no error.

for the input value which i am giving has to return values from stored procedure but it is executing but not returning the stored procedure is as below.

procedure get_page_data  ( w_cs_data_id in number )

as
w_sqlcode varchar(10) ;
begin
for rec in ( select * from page_op_validation )
 loop
if ( rec.cs_data_id = w_cs_data_id )

then
dbms_output.put_line(rec.cs_data_id||' '||
rec.record_date||' '||
rec.validation_type||' '||
rec.station_id) ;
end if;
end loop ;
end ;

when i execute the same in sqlplus i can see stored procedure executed sucessfuly

but i can see output when i set output parameter

regards

Vijay G

[vijay_kumar133]

hi

Is there any limitation in using oracle stored procedure in sap pi if so what are those ...

can we use this at receiver end of oracle system

Regards

Vijay G

Edited by: vijay Kumar on Jul 2, 2010 9:01 PM

[Former Member]

Vijay,

Is there any limitation in using oracle stored procedure in sap pi if so what are those ...

No, there are no restrictions / limitations. For further reading, refer -

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

can we use this at receiver end of oracle system

Yes, it can be used at the receiving side as well. Refer -

http://help.sap.com/saphelp_nw04/helpdata/en/64/ce4e886334ec4ea7c2712e11cc567c/content.htm

Regards,

Neetesh

[vijay_kumar133]

Hi Raj,

'Thanks raj i even configured and designed same way as it has to be.

But when i execute the stored procedure it says executed sucessfully but the output of the stored procedure is not captured in response message .

I can see response as blank ....to use stored procedure in sync mode what way it has to be coded..

if possible give me sample code for stored procedure to be used at receiver end to select few records on base of input passed value.

Regards

Vijay G

Edited by: vijay Kumar on Jul 3, 2010 7:37 AM

[Former Member]

Vijay,

Have you executed the query for the stored procedure on SQL developer (or similar tool)? Do you get the response back?

A sample XSL mapping code for SP:-

.
.
.
 <MT_xxx>
         <Call_SProc>
             <push action="EXECUTE">
                  <table>push</table>
                        <MORE LOGIC, if required/>
                               .
                               .
                               .
               </push>
            </Call_SProc>
  </MT_xxx>
.
.
.

Regards,

Neetesh