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

Passing an Array from PI JDBC to Oracle stored Procedure

Good Morning All,

I have one scenario which data need to inserts into Oracle databases. currently Insert query takes long time to insert into database ( for ex: 75000 records it took around 2 hrs 45 min)

My user complained about performance and wants to use Oracle stored procedure but not XMLstring as input, He wants PI pass an array to Stored procedure as in put.

I am n't sure how PI pass array to stored procedure as a Input. Do you please let me about suggestion.?

I appreciate your help.

Thanks & Regards,
Sateesh

Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • Best Answer
    Posted on Mar 23, 2015 at 02:43 PM

    Sateesh,

    There is an option to send text to database. It's primarily for test purposes, but you can use it.

    Defining XML Documents for Message Protocol Native SQL Format - SAP Library

    Output of PI should produce JDBC query (it should be either done in map or using MTB in receiver channel).

    First, take out OM in interface determination and send the text end to end (without mapping).

    Then develop map to produce same text output.

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Dear All,

      Thanks for all your suggestions.

      What I got impression after my several researches, PI could not able to call ARRAY type stored procedure.

      I tried to follow Mr.Inaki response , collect the values from rows and separted by character value and pass it to Stored procedure input parameter, and even this approach is not suitable for me, because what I got impression stored proc input parameter allows to maximum 4000 characters and special cases 32k and even my user doesn't like this approach.

      So I took up my final decision is going by my old style like call Stored procedure with XML string as a input parameter.

  • Posted on Mar 23, 2015 at 12:02 PM

    Hi Sateesh,

    First of all i've never tried to pass an array to SP i've always rather to construct an XML. However, i have done a little research that it could be helpful for you.

    In this thread Raja Sekhar Reddy gives an alternative. Try to use that separators between values.

    In this link you can't find any ARRAY type Defining an EXECUTE Statement - Advanced Adapter Engine - SAP Library, i think you will need to do a workaround, like to separate inside a varchar the values by a determinate character and later the SP splits the content according that character.

    Regards.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Mar 23, 2015 at 11:56 AM

    Dear All,

    Any hints/suggestions how to pass ARRAY string as input to Stored procedure from PI?

    Thank you.
    Sateesh

    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.