Skip to Content
author's profile photo Former Member
Former Member

How to handle Stored Procedure and Views

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

5 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Nov 30, 2007 at 05:39 AM

    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**

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 30, 2007 at 05:40 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 30, 2007 at 07:45 AM

    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,

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Nov 30, 2007 at 08:01 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 30, 2007 at 07:05 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.