AnsweredAssumed Answered

Using a Map Function SQL lookup to obtain Output Parameters from an Oracle Stored Procedure with a Ref Cursor

Question asked by BjarkeHansen4421 on Aug 10, 2015
Latest reply on May 25, 2016 by BjarkeHansen4421

According to https://community.boomi.com/docs/DOC-1141#jive_content_id_How_can_I_call_a_Stored_Procedure  a Map Function SQL loopup should be used to access Output Parameters from a stored procedure. However, I get an error when performing the lookup (presumably because I cannot define one of the out parameters as a ref cursor (which should be empty)):

Caused by: java.sql.SQLException: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'EXPORT_TO_PROJECT' ORA-06550: line 1, column 7: PL/SQL: Statement ignored.


The stored procedure called is described in further detail below:
PROCEDURE Export_To_Project(Parm_Transmission_Commit    IN NUMBER,
                             Parm_Transmission_Commit_Ok OUT NUMBER,
                             Parm_Rows_Found             OUT NUMBER,
                             Parm_Ref_Data               OUT Ref_Cursor_Project)
The procedure is multi-purpose:

  1. If called with no input parameter, data is returned through the ref cursor. This is working through a database connector. (However, if no cursor is returned due to no rows being found, the process fails).
  2. If called with the input parameter (Parm_Transmission_Commit), the rows are committed and a status is returned (Parm_Transmission_Commit_Ok).

It is purpose 2. that is causing problems.

 

The settings are as follows:
0EM40000000brvG

 

Any help is appreciated, thank you.

Outcomes