AnsweredAssumed Answered

What's the secret to using Stored Procedure Read profiles?

Question asked by KevinDodds7181 on Nov 6, 2013
Latest reply on Oct 4, 2017 by ultimo.user1275519
What is the trick to using a database stored procedure for reading data?  I have tried several variations within my stored procedure, and reduced my parameter list to just a couple of character parameters.  I have no input parameters (after the reducing).  And every time I run a test I get:

Test execution of DB Read Process completed with errors. Embedded message: Errors occurred while building or executing SQL statement: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'BOOMI_READ_PROJECT' ORA-06550: line 1, column 7: PL/SQL: Statement ignored ; Caused by: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'BOOMI_READ_PROJECT' ORA-06550: line 1, column 7: PL/SQL: Statement ignored

I am using Oracle 11.2.0.1.0.  I am a long-time database and Java developer, and I am sure my DB Profile fields and types match my procedure.  However, since I seem unable to view the actual procedure call from Boomi, and the documentation has 0 examples of using a Stored Procedure reads, I am probably misinterpreting their minimal word description which conveniently uses general terms to gloss over and omit technical details.

I can get a Select query to work pretty easily.  This is ridiculously frustrating.  My procedure name is BOOMI_read_project, it has 9 parameters, all character strings.  Can anyone describe what my profile should look like to get the Read to work?

I have tried putting 9 fields to match the output parameters from the procedure.  I have tried putting 9 parameters to match the output parameters from the procedure.  I have tried changing the "Stored Procedure" field to include question marks (?) for the parameters (that actually changed the error message, but still never worked)

This is something I can write myself in Java in a matter of minutes.  So can someone please explain what I'm doing wrong with this?  And please don't point me to the useless documentation in the Reference Guide unless there is an actual example that applies to my exact situation that I have missed (which I HIGHLY doubt).

Outcomes