STORED PROCEDURE

[Former Member]

Hi guys,

I am calling ORACLE stored procedure as :

UPDATE transactions t

SET t.flag = 'FALSE'

WHERE exists

(SELECT e.empcode

FROM empmaster e

WHERE e.comid= comid_in AND

e.locid = locid_in AND

e.empcode = t.empcode)

where comid_in , locid_in are input to stored procedures , but in this case no update operation is done.

If i hardcore the values then procedure is executed correctly , I am wondering why this is happening . Here comid_in and locid_in are of type varchar2(10)

Please help me out.

Regards,

Keith.

[bhavesh_kantilal · Accepted solution]

Keith,

Can you give us the Receiver Message that is being sent from XI to your JDBC adapter.

Also, look into the JDBC adapter and check if there is an issue on that front.

Regards,

Bhavesh

[Former Member · Accepted solution]

Hi Bhavesh,

My receiver message is :

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

<ns0:SAPR3_JDBC_STORED_PROC_MT xmlns:ns0="URN://ZICOM/XI/SAPR3Integration"><CallOracle_PROC><storedProcedureName action="EXECUTE"><table>UPDATE_FLAG</table><sdatetime1 isInput="1" type="VARCHAR"></sdatetime1><sdatetime2 isInput="1" type="VARCHAR"></sdatetime2><comid_in isInput="1" type="VARCHAR"></comid_in><locid_in isInput="1" type="VARCHAR">20</locid_in></storedProcedureName></CallOracle_PROC></ns0:SAPR3_JDBC_STORED_PROC_MT>

and there is no problem with JDBC adpter as for hardcore values in procedure it is working correct.

Regards,

Keith.

[bhavesh_kantilal · Accepted solution]

Keith,

<i><sdatetime1 isInput="1" type="VARCHAR"></sdatetime1><sdatetime2 isInput="1" type="VARCHAR"></sdatetime2><</i>

Am not sure I understand why you need these fields.

Am not able to understand what you mean when you say it works fine for HARD CORE values?

Apart from these, the datatype looks exactly perfect to me.

Regards,

Bhavesh

[Former Member · Accepted solution]

Hi Bhavesh,

sdatetime is another field i am using in proc, I am converting it in procedure using TO_DATE() function .

I am facing problem with comid_in and locid_in for which i think there is some datatype mismatch is happening , does VARCHAR as supported in XI is type compatible with VARCHAR2(10) .

And HARD CORE means when i am manually inserting values in COMID_IN and locid in my stored procedure.

Regards,

Keith.

[moorthy · Accepted solution]

Hi Keith,

<i>does VARCHAR as supported in XI is type compatible with VARCHAR2(10)</i>

Use Data type for String In XI and check the same.. And format of the data etc.. ie. any special characters etc..If so try to convert in the required format in the Mapping with the help of USer Defined Java functions.

Hope this helps,

Regards,

Moorthy

[bhavesh_kantilal · Accepted solution]

Keith,

Like mentioned by moorthy, try it with String. XI will internally coinvert it into varchar.

Regards,

Bhavesh

[bhavesh_kantilal · Accepted solution]

Keith,

<i>does VARCHAR as supported in XI is type compatible with VARCHAR2(10)</i>

Just checked,Yes it is compatible.

Just make the type of type to be STRING and assign the contant VARCHAR to it.

Regards,

Bhavesh