JDBC sender to call a stored Procedure

[sahithi_moparthi]

Hi Guys,

We have Scenario from JDBC to IDOC.We are using Oracle DB,at the database side we have a stored Procedure and inside the Procedure we are accessing multiple table.

  My query is how can we excecute a stored procedure by using EXECUTE at the sender JDBC.I wrote the query as :

EXECUTE name.Packagename.Storedprocedurename.

But it is giving error like: Invalid SQL statememnt.

Can anyone please on this??

[former_member190681]

Dear Sahiti,

Jdbc to Idoc we have map the fields according to the requirement but i will show you sample image files and given fileds are also mapping fields in the table or sender side structure.

Select Query:

select ERDAT,VBELN,VBELN1,MATNR,TRAID,KWMENG,KBETR,KUNNR,WERKS, TO_CHAR(PRINTSTAMP,'MM/DD/YYYY HH:MI:SS AM') PRINTSTAMP,EXTRA1,EXTRA2,EXTRA3,EXTRA4,EXTRA5 from ORDERTABLE where STATUS='N'

Update Query:

UPDATE ORDERTABLE SET STATUS='E', XI_READ=to_date(to_char(sysdate,'DD/MM/YYYY HH24:Mi:SS'),'DD/MM/YYYY HH24:MI:ss')  WHERE STATUS='N'

Thanks

Regards,

Kiran Polani

[Former Member]

Hi ,

You can't achieve this with normal stored procedure for Oracle DB.

  Please refer to the following threads......might be helpful to you.

    http://scn.sap.com/thread/330212

    http://scn.sap.com/thread/1574165

  BR,

Anirban

[former_member624750]

Hi,

Use the below link to write stored procedure UDF if u know Java..

Regards

Naren

[former_member802575]

hi

        in cc channel parameter  query sql statement u can wrote select statement Like

  select * from table ( stored procedure name)

   And

Update Sql statement means use <TEST>

IT will be work....

Rerards

srikanth k

[MichalKrawczyk]

Hi

have a look how you can achieve what you want:

http://scn.sap.com/community/pi-and-soa-middleware/blog/2011/10/04/pixi-sender-jdbc-adapter-for-orac...

Regards,

Michal Krawczyk

[abhradeepbasu]

Hi Santhoshi,

Please check whether your procedure has multiple select statements or not. Multiple Selects in a stored procedures are not probably allowed to call from Sender JDBC driver.

/AB

[ambrish_mishra]

Hi Santhoshi,

Please check this link:

http://scn.sap.com/community/pi-and-soa-middleware/blog/2013/03/06/executing-stored-procedure-from-s...

Hope it helps!

Ambrish

[former_member184681]

Hi,

Have a look to the documentation below, under "Query SQL Statement":

http://help.sap.com/saphelp_nw04/helpdata/en/7e/5df96381ec72468a00815dd80f8b63/content.htm

Regards,

Greg

[former_member200386]

HI Santhoshi,

Which Oracle version you are using? Oracle versions(8,9) not supports stored procedure.  oracle 10g support stored procedure.but  you have to use table function. to execute stored procedure.

http://scn.sap.com/people/jegathees.waran/blog/2007/03/02/oracle-table-functions-and-jdbc-sender-ada...

Thanks & Regards,

Pavan

[sahithi_moparthi]

Hi,

We are using Oracle 10g.Alreday at the database side they had wote the stored procedure.But from PI side they were asking to execute the stored procedure.And from the block i get to know that : we Cannot pass dynamic values as input to the stored procedure.But in our scenario we have to pass the valjes dynamically so how could we acheive this.

[former_member200386]

Hi Santhoshi,

I worked earlier this type of scenario before, We tried to call the stored procedure from PI, but we failed (because their data base is Oracle). Then we suggested this approach.

1)Ask DB team to create view that the contains the output of Stored procedure.

2)Sender JDBC channel will pick the records from view.

Thanks & Regards,

pavan