How to handle Stored Procedure and Views

[Former Member]

Dear All

While dealing with Oracle database related scenario. I came across Stored Procedures and Views. Which are complex in nature. Using SAP XI how we can handle them ?

Is JDBC adaptor is capable of that.? Can you help me Data type structure for oracle.

How max occur play important role in that. How to identify root and item level structure for oracle

I am dealing with stored procedures while inserting data. and using views i need to get data from oracle database.

What is the syntax of query we use to put while using JDBC adaptor?

Please help and provide bit detail information over this so that i can execute scenario

Thanks

Gaurav

[Former Member · Accepted solution]

Hi gaurav,

stored procedures r used to do modifications to database.

refer the following links..

http://www.functionx.com/sqlserver/Lesson16.htm

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

/people/jegathees.waran/blog/2007/03/02/oracle-table-functions-and-jdbc-sender-adapter

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

/people/jegathees.waran/blog/2007/03/02/oracle-table-functions-and-jdbc-sender-adapter

• pleaseReward points if Usefull**

[GabrielSagaya]

1) jdbc:oracle:thin:@xxx.xxx.xxx.xxx:1521:sid

2)Occurence==> o,1, >1 , Unbounded

Occurrence=> ready to accept 0 / 1 / more than 1/ multiple record (for source) and how it will be passed to target.

http://help.sap.com/saphelp_erp2004/helpdata/en/b6/0b733cb7d61952e10000000a11405a/frameset.htm

3)

<StatementName5>

<storedProcedureName action=” EXECUTE”>

<table>realStoredProcedureeName</table>

<param1 [isInput=”true”] [isOutput=true] type=SQLDatatype>val1</param1>

</storedProcedureName >

</StatementName5>

refer

http://help.sap.com/saphelp_nw04/helpdata/en/22/b4d13b633f7748b4d34f3191529946/frameset.htm

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

[Former Member]

Hi Gaurav,

1. I need the URl string we use for oracle database.

<b>--> jdbc:oracle:thin:@10.15.YYY.XXX:1521:Hoststring</b>

2. Importance of Occurrence while creating inbound or outbound data types.

<b>--> Occurrence will decide how you want to accept the data means Is your message is ready to accept multiple message/record (for source) and how it will be passed to target..</b>

For further clarifying the doubts in Occurrences :

http://help.sap.com/saphelp_erp2004/helpdata/en/b6/0b733cb7d61952e10000000a11405a/frameset.htm

Regards,

[Former Member]

Dear Keerthi & Chirag

Thanks for the supporting files. It gave me some idea. Still my requirements are

1. I need the URl string we use for oracle database.

2. Importance of Occurance while creating inbound or outbound data types.

3. I am not able to understand the structure we use to keep for executing stored procedure.

I describe more about my problem

stored procedure i am going to write will be like

update hs_comm_lock A

set active ='N'

where exists(select 'X' from HS_COMM_LOCK B

WHERE A.DEVICE_ID = B.DEVICE_ID

AND B.CREATION_DATE>A.CREATION_DATE

AND ACTIVE ='Y')

AND ACTIVE ='Y'

AND TYPE !='IMEI'

AND DEVICE_ID IN

(SELECT DEVICE_ID FROM HS_COMM_LOCK

WHERE ACTIVE ='Y' AND TYPE!='IMEI'

GROUP BY DEVICE_ID HAVING

COUNT(*)>1)

so for this kind of stored procedure I am looking for structure which i can execute and map in XI.

Thanks

Gaurav

[Former Member]

HI,

Please go through the below help for writing stored procedure.

Receiver JDBC scenario MS access - /people/sameer.shadab/blog/2005/10/24/connecting-to-ms-access-using-receiver-jdbc-adapter-without-dsn

Stored Procedures-

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

http://www.ics.com/support/docs/dx/1.5/tut6.html

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

http://www.ics.com/support/docs/dx/1.5/tut6.html

http://java.sun.com/docs/books/tutorial/jdbc/basics/sql.html

http://www.sqlteam.com/article/stored-procedures-an-overview

Thnx

Chirag