Problem with Stored Procedure exection in Sender and Receiver side of JDBC

[Former Member]

Hi All,

I am facing problem while executing Stored Procedures using sender and receiver sides of JDBC adapter.

Here is my SP in Oracle DB :

PROCEDURE EMP

( ID IN VARCHAR2,NAME IN VARCHAR2,PROCESSED IN VARCHAR2 ) AS

BEGIN

INSERT INTO EMPLOYEE VALUES (ID, NAME, PROCESSED);COMMIT;END EMP;

Now I want to execute this SP using sender JDBC channel and receiver JDBC channel.

Can anyone please help me executing this SP?

Regards,

Soorya

[Former Member · Accepted solution]

HI Please find some reference Link

For this you can refer this link on how to create a DT for Executing a stored procedure:

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

Check it out..

For more info on StoredProcedure:

/people/siva.maranani/blog/2005/05/21/jdbc-stored-procedures

/people/sriram.vasudevan3/blog/2005/02/14/calling-stored-procs-in-maxdb-using-sap-xi

Thanks,

Bhupesh

[former_member187339]

Hi Soorya,

If you are planning to execute this SP through JDBC sender adapter then it might not be successful as it is not returning anything to PI

PROCEDURE EMP

( ID IN VARCHAR2,NAME IN VARCHAR2,PROCESSED IN VARCHAR2 ) AS

BEGIN

INSERT INTO EMPLOYEE VALUES (ID, NAME, PROCESSED);COMMIT;END EMP;

This SP code will work for Receiver JDBC and not for sender

Regards

Suraj

[Former Member]

Hi,

Thanks for your information.

Can you please modify this SP to use in Sender Side JDBC?

Also please help me in using the same SP in receiver side JDBC processing.

What kind of input file does Receiver JDBC expects in order to execute SP at receiver side?

Regards,

Soorya

[former_member187339]

Hi Soorya,

The receiver Data type should be like this:

<StatementName>
<storedProcedureName action=u201D EXECUTEu201D>
    <table>realStoredProcedureeName</table>
<param1 [isInput=u201Dtrueu201D] [isOutput=true] type=SQLDatatype>val1</param1>
</storedProcedureName > 
  </StatementName>

Check the link http://help.sap.com/saphelp_nw04/helpdata/en/64/ce4e886334ec4ea7c2712e11cc567c/frameset.htm

Which DB are you using?? The sender structure will be like

<resultset>
<row>
<field1></field1>
<field2></field2>
<field3></ field3>
</row>
</resultset>

Search SDN you will get lot of examples

Regards

Suraj

[Former Member]

Hi Suraj,

I have created the input file like this :

<root>

<statement>

<EMPLOYEE action="EXECUTE">

<ID type="Varchar2">123</ID>

<NAME type="Varchar2">ABC</NAME>

<PROCESSED type="Varchar2">NO</PROCESSED>

</EMPLOYEE>

</statement>

</root>

I am using Oracle DB.

I have already shown the SP.

Please help in this case. Its very critical.

Regards,

Soorya

[former_member187339]

Hi Soorya,

Are you trying to insert this employee into DB?? Your target structure should look like this

<StatementName>
<storedProcedureName action=u201D EXECUTEu201D>
    <table>EMP</table>
<ID isInput=u201Dtrueu201D type=VARCHAR2>val1</ID>
<NAME isInput=u201Dtrueu201D type=VARCHAR2>val1</NAME>
<PROCESSED isInput=u201Dtrueu201D type=VARCHAR2>val1</PROCESSED>
</storedProcedureName > 
  </StatementName>

Regards

Suraj

[Former Member]

Hi Suraj,

Thanks for your great help. I have solved the issue with SP at receiver side.

Now I want to use the same SP at sender side.

How we can call the SP from sender JDBC channel?

Please help me in this regard also.

Regards,

Soorya

[former_member187339]

Hi Soorya,

You cannot call the same SP from source side..

Check point 6 of note Note 941317 - XI / PI JDBC Adapter: Known Problems / Incompatibilities

for a help of sender SP

Regards

Suraj

[Former Member]

Hi,

How come that note is related to this issue?

I am having problem while executing a SP from Sender JDBC side.

Please help me in this ASAP.

Regards,

Soorya

[former_member187339]

Hi Soorya,

Did you checked the note??? IT is having an example for sender Stored Procedure see how it is created...

Likewise create a one for you.. You cannot insert an employee using sedner JDBC adapter.. YEs you can try fetching the details of the inserted employee...

Take help of SP from the note..

Regards

Suraj

[Former Member]

Hi Suraj,

Sorry to confuse you.

I have created one more SP as,

create or replace PROCEDURE SAPXI

(PROCESSED IN VARCHAR2 ) AS

BEGIN

SELECT * FROM EMPLOYEE WHERE PROCESSED ='NO';

UPDATE EMPLOYEE SET PROCESSED='YES' WHERE PROCESSED='NO';

COMMIT;

END SAPXI;

/

Now please help me executing this SP.

Regards,

Soorya

[former_member187339]

Hi Soorya,

create or replace PROCEDURE SAPXI
(PROCESSED IN VARCHAR2 ) AS
BEGIN
SELECT * FROM EMPLOYEE WHERE PROCESSED ='NO';
UPDATE EMPLOYEE SET PROCESSED='YES' WHERE PROCESSED='NO';
COMMIT;
END SAPXI;

Have you tested the above stored procedure in oracle DB? IS it returning something?, Say it returns A, B, C fields then your source Data type should be

<resultset>

<row>

<A>column-value</A>

<B>column-value</B>

<C>column-value</C>

</row>

<resultset>

Your select query in sender adapter will be will

select * from SAPXI;

Update query will be <TEST>

Try this

Regards

Suraj

[Former Member]

Refer for JDBC receiver with stored procedure

/people/siva.maranani/blog/2005/05/21/jdbc-stored-procedures