Hi, I want to use a "Stored Procedure" to read data from an Oracle database. How do I return data from Oracle Stored proc to Boomi?
DO I use a Ref-cursor? Is there any code sample?? Thanks Ganesh
I accidentally bumped into this old thread and honestly I am not getting the final point here.
In order to make it easy for anyone new who looks into this thread looking for an answer, here is what I have to say about calling a stored procedure from Oracle.
Is it possible to extract a list of rows from an Oracle table via Boomi? The answer is YES and it can be done by making use of ref-cursor as the OUT parameter in the stored procedure.
Say for example I need to get a list of rows from an Oracle table, first I need to create a stored procedure in Oracle which returns a ref-cursor. In Boomi we can use Stored Procedure Read option and import the stored procedure. In this way the cursor which is returned to Boomi consists of all the records and we can extract the rows accordingly.
Can we get return parameter for write operation for Stored parameter?
I could not get Oracle OUT parameters to work with a database profile and an Oracle stored procedure (read).
I changed them to IN OUT and it worked fine.
For a cursor, OUT does work (e.g. OUT SYS_REFCURSOR), define the parameter as OUT with a Value Type of cursor.
In the Fields section set Linked Parameter to the name of the cursor OUT parameter.
You need to specify values for the IN OUT parameters, you can map them to static and leave them blank.
Retrieving data ...