I created an Informatica mapping for which source is a text file and target is also a text file. I am calling an Oracle stored procedure by using connected stored procedure transformation and in the next step I am reading the data from the table which is loaded in the stored procedure.
The problem is table is getting loaded but the data from the table is not read and it writes zero record in the target file. I think the stored procedure is run in the end of the session.
How can I make the stored procedure run in the flow and not in the end of the session?
The stored procedure is working fine if I call from SQL Developer or from informatica.
Flow:
- Flat file source -> EXP trans -> Call stored procedure -> SQL trans (run query on table.) -> tgt file
Stored procedure flow:
- truncate the table
- load the table with some input data