Issue while calling stored procedure

[former_member216353]

Hi all,

We are trying to call a stored from a synchronous PI interface.

Below is the stored procedure code.

PROCEDURE SP1(

IUSERNAME IN VARCHAR2)

IS

VPASSWORD VARCHAR2(20);

BEGIN

SELECT

PASSWORD

INTO

VPASSWORD

FROM

EMPLOYEE@DLG_EMPLOYEE.AB.XVBY

WHERE

USERNAME = IUSERNAME;

DBMS_OUTPUT.PUT_LINE(VPASSWORD) ;

END;

The interface is working fine without any error but there is no output coming to PI from the database even after giving a valid username as input to the stored procedure.

The request message to the stored procedure looks like :

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

<ns0:MT_Employee xmlns:ns0="urn:test">

<Statement>

<SP1 action="EXECUTE">

<table>SP1</table>

<IUSERNAME isInput="true" type="VARCHAR">TEST</IUSERNAME>

</SP1>

</Statement>

</ns0:MT_Employee>

Any pointers on how to solve this issue.

Regards

younus

[Former Member]

Dear Mohammed

Can you share the response strucutre you are using to get the response from the Database.

What can you see in moni for this response

Have you declared Output parametrs properly

Please check

Sourabh

[former_member216353]

Hi Sourabh,

I am using the following message structure in PI to get the response.

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

<ns0:MT_Employee_Response xmlns:ns0="urn:test">

<Statement_Response>

<response_1>

<row>

<vpassword/>

</row>

</response_1>

</Statement_Response>

</ns0:MT_Employee_Response>

And in moni , i could see an empty message .

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

- <ns0:MT_ForecastDB_response xmlns:ns0="test">

<Statement_response />

</ns0:MT_ForecastDB_response>

Can you please tell me where do we need to declare the output parameters.

[Former Member]

When SP1 is executed independently, does it gives output?

If it does ans still you are facing issue then:

Check if your output parameter name and type is matching with that of stored procedure

[rajasekhar_reddy14]

Have you checked JDBC receiver channel log message in communication channel monitoring? meanwhile check with data base team about Stored procedure functionality is behaving Sync manner or not.

[former_member216353]

Hello Raja Shekar,

meanwhile check with data base team about Stored procedure functionality is behaving Sync manner or not.

Could you please give more details on this? How to check whether a stored procedure is behaving is synchronous way or not?