JDBC Receiver Stored Procedure with Parameter as array object

[Former Member]

Hello Experts,

I have a scenario where I need to execute a Stored Procedure via JDBC receiver adapter. The property of the Stored Procedure is as follows,

1. It has 2 input and 1 output Parameters.

2. Input has following structure

     Header (1:1):

      -----------Field 1

      -----------Field 2

      -----------Field 3

     LineItem (1:1):

      --------------Item (0:n)

                    --------Field 3

                    --------Field 4

                    --------Field 5

3. Output is a simple field of type VARCHAR

I am seeking your help with the following queries,

a) Since the input parameters to the Stored Procedure is of complex type how to build the receiver JDBC XML structure?

b) Do I need to include Output parameter in the receiver JDBC XML structure?

c) What are all the limitations with PI on executing stored procedures.

I have gone through many blogs and discussions through google but couldn't find any solution for my case, please share your thoughts.

Thanks.

[Former Member · Accepted solution]

So I am concluding there are limitations in accessing Stored Procedure from PI.

1. If we use standard receiver JDBC XML structure we are limited to use only standard SQL data types i.e INTEGER, BIT, TINYINT, SMALLINT, BIGINT, FLOAT, REAL, DOUBLE, NUMERIC, DECIMAL, CHAR, VARCHAR, STRING, LONGVARCHAR, DATE, TIME, TIMESTAMP, BINARY, VARBINARY, LONGVARBINARY, BLOB (output only),CLOB (output only).

2. If we use "SQL_QUERY" in receiver JDBC XML structure we are limited to use only single line of query i.e we cannot use multi line SQL statements E.g if the stored procedure has output parameter then you have to declare it before calling the stored procedure so it is not a single line statement and the code usually looks as follows;

declare

out1 VARCHAR2(200);

begin

StoredProcedureName ("abc", out1);

end;

(or)

variable out1 VARCHAR2(200);

execute StoredProcedureName("abc",:out1)

3. If we use "native SQL" in receiver JDBC adapter we are limited to use only single line SQL statement (or) in case of PL/SQL block we can use multiline and we get only acknowledgement as result and not the output parameter values i.e the acknowledgement whether the PL/SQL block has executed or not i.e "SQL statement returned OK, 1 row(s) affected".

[Former Member]

Finally, I prepared a XSLT mapping to generate Native SQL script. The DB call is successful but I get single line response as "SQL statement returned OK, 1 row(s) affected” but when I execute the same script in Toad software it gives result (in DB output window). Any clue why I am not getting the result in PI?

[RaghuVamseedhar]

Hi,

Please try native SQL format. After successful message process in PI, construct input and output XML structure.

Defining XML Documents for Message Protocol Native SQL Format - Advanced Adapter Engine - SAP Librar...

[Former Member]

Thank you Raghu, I will try Native SQL Format. and you mean it is the only way to execute the stored procedure with 'nested tables' as input? is there a way to use "SQL-Query"in receiver JDBC XML structure?

[RaghuVamseedhar]

SAP PI JDBC XML structure is fixed. If that fixed structure is not sufficient for the requirement, native SQL format can be used.

[Former Member]

Can someone help? Is there any way to call up the stored procedure with nested table as input parameters

[iaki_vila]

Hi,

a) Since the input parameters to the Stored Procedure is of complex type how to build the receiver JDBC XML structure?

With a complex structures i usually set an input variable type varchar, i convert the XML in one tag,  and later inside th SP I try to work with that structure.

An example of working with XML in the SP: XML as parameter in stored procedure (sql server) - Stack Overflow

b) Do I need to include Output parameter in the receiver JDBC XML structure?

You can get exceptions about the number of parameters. There is no problem to define it, later if you don't want you simpy don't map it to your output.

c) What are all the limitations with PI on executing stored procedures.

Mainly, i've  found some SQL types don't supported

Raja Sekhar Reddy says in his blog http://scn.sap.com/community/pi-and-soa-middleware/blog/2012/09/07/jdbc-receiver-scenarios-best-prac...

that the batch mode call is not supported.

Hope this helps.

Regards.

[Former Member]

Thank you for your suggestions.

a) With a complex structures i usually set an input variable type varchar, i convert the XML in one tag,  and later inside th SP I try to work with that structure.

An example of working with XML in the SP: XML as parameter in stored procedure (sql server) - Stack Overflow

I do not have option to tell business to change exsisting Stored Procedure, so is there any other option from PI?

[Former Member]

XML structure should be in JDBC standard format as below..

<StatementName5>

<storedProcedureName action=” EXECUTE”>

  <table>realStoredProcedureeName</table>

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

</storedProcedureName >

  </StatementName5>

For structure creation you can use the blog -

refer the below blogs

FAQ -831162 - FAQ: XI 3.0 / PI 7.0 / PI 7.1 / PI 7.3 JDBC Adapter