I need to call a stored procedure from informatica SQL transformation. The stored procedure returns a reference cursor as output.
The SQL transformation as input ports which has link backward to the expression transformation which gets inputs from a .prm file. Also,I have declared the output ports as the output columns of the cursor from the stored procedure.
But this is not working.I was reading in a reference material for Informatica 10.0.0 that it's possible to call a stored procedure which returns a result set(ref cursor) from SQL transformation.
Please advise if this is achievable and what is the mistake I am doing that it's not working.
Result sets can be returned by a SQL Transformation, but reference cursors are something completely different. As far as I understand it (it's been quite a while since I've worked with cursors for the last time), you can NOT hand over a cursor via a SQL Transformation so that you can use this cursor later in the mapping.