Mapping structure to call stored procedure with parameter as table type in jdbc receiver adapter

[Former Member]

Hi All,

We have SOAP to JDBC Syncronous interface requirement. In receiver PI has to call Stored Proc which has output param as table type (OUT pram3 SAP.VIEW_TABLE) Procudure code

--------------------------

PROCEDURE XYZ_prc" ( IN parm1 NVARCHAR(100), IN parm2 NVARCHAR(100), OUT pram3 SAP.VIEW_TABLE, OUT param4 NVARCHAR(100), OUT param5 NVARCHAR(100))

-----------------------------

As The following SQL data types are supported as type for input and out prameters how can we achieve this in PI

-----------------------------------------------------------------------------------------------------------------------INTEGER, BIT, TINYINT, SMALLINT, BIGINT, FLOAT, REAL, DOUBLE, NUMERIC, DECIMAL, CHAR, VARCHAR, STRING, LONGVARCHAR, DATE, TIME, TIMESTAMP, BINARY, VARBINARY, LONGVARBINARY, BLOB (input and output), CLOB (input and output), CURSOR (output; only in connection with the Oracle JDBC driver)

------------------------------------------------------------------------

I assigned varchar/string to "pram3"-type in message mapping ,tested the scenario and end up with following error as SAP.VIEW_TABLE is custom type in procedure and i assigned different type in mapping

9/24/2017 10:57:45.071 PMErrorException caught by adapter framework: Error processing request in sax parser: Error when executing statement for table/stored proc. "XYZ_prc" (structure Statement): com.sap.db.jdbc.exceptions.JDBCDriverException: SAP DBTech JDBC: [1281]: wrong number or types of parameters in call: too many arguments: line 1 col 72 (at pos 71)

Please let me know is there any way to call above stored procedure from receiver jdbc adapter.

Thanks.

Regards,

Ramesh M

[former_member190293]

Hi Ramesh!

What is your database provider and what actions does your stored procedure perform exactly?

Regards, Evgeniy.

[Former Member]

Hi Evgeniy,

Here DB Provider is SAP HANA and we are using SAP PI 7.4 single stack to connect DB.

The mentioned procedure takes param1,param2 as input and do some validation(null check etc) on these param's ,based on the output of validation (true of false) it will fetch records(each record contains 6 columns) from one of the tables and write to param3 which is table type.

It assign some constant values to param4,param5 and return param3,param4,param5 as ouput.

Thanks.

Regards,

Ramesh M.

[former_member190293]

Hi Ramesh!

The only way to get result of table type that I'm aware of is using table-type stored procedure returning table data.

Regards, Evgeniy.