1
votes

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
1

1 Answers

0
votes

Do you need the flat file source at all? Does it contain any paramters for the Stored Procedure? If not:

  • remove the Flat File source
  • create Source and Source Qualifier for your table
  • run the Stored Procedure as part of the Pre-SQL

Otherwise create two separate flows in your mapping, like:

Flat File Source -> Stored Procedure call -> Flat File Target

Source table -> Expression -> Flat File Target