What is the need of a staging table or temporary table in between flat file and standard interface table while transferring data from flat file to interface table?

Showing Answers 1 - 9 of 9 Answers

Madhu Ravipati

  • Sep 8th, 2006

Once we have data in the interface table we are simply running the standard program and it automatically send data to the base table so in this time we can't do any validations in in between the interface table and base table that'y we use staging table to validate the data before putting into the interface table.Finally the staging tables are used to load the data from legacy systems what ever the client provided .after that we write a validation program to send proper valid data to interface table.

  Was this answer useful?  Yes

Preethi Kanjikar

  • Oct 16th, 2006


Before the data gets inserted in the actual database table the data gets validated...

by validation, i mean there are two files created called bad file and discarded file......first validation occurs in the bad file  and second in the discarded file....data in the discarded file is discarded and finally the data is entered in the database table......

Please correct me if I'm wrong....since i'm a beginner .....i may learn more if you correct my mistakes...



  Was this answer useful?  Yes


  • Dec 22nd, 2006


the basic need for a staging table is to serve as another step before loading into the interface tables after which we run oracle standard APIs to load them into the bas tables sometimes even after running APIS we may need some concurrent programmes to be run so for that data to be reflected across the base tables. The staging area serves for updates like date formats (MM-DD-RRRR) and other such critical data format changes neede before we load into interface tables and then running APIS in them . As explained above a bad file if formed while loading if some data format is not in consonance with the API or base file and hence the whole API/validation fails, In some case though it creates a corrupt log file which also indicates the failure of the validation.

Plz correct me if I am wrong.

  Was this answer useful?  Yes

neela manohar

  • Jan 8th, 2007

these staging tables   are   used   to  hold temporary data i.e data with in the temporary table will be automatically get deleted when we issue commit or when we close oracle session that depends on how we create table

eg: create global temporary table eg1(name varchar2(20),num number(10)) on commit delete rows;

this will delete the rows on temporary table when we issue a commit at sql prompt.

moreover these staging tables are used to do some primray validations before inserting data into actual tables.

so we need data to be erased after inserting into acutal tables. so we go for staging tables.

we can use normal tables also as staging table but we need to truncate table for each insert. thats why we go for temporary tables.

  Was this answer useful?  Yes


  • Jul 7th, 2015

Thank you so much

  Was this answer useful?  Yes


  • Dec 27th, 2015

Hi All,

I will give an brief description about, why we are using temporary tables based on my practical experience I had correct me if I am wrong.

In Oracle Apps we have inbuilt APIs, From flat files we receive the data from client. Data sometimes may mismatch or incorrect data will get. So, to avoid such confusions, before run the APIs, we had to validate the data to move to staging or temporary tables for each run.


  Was this answer useful?  Yes


  • Jun 1st, 2016

Hi sir,

The flat file generated by us should be based on staging table or interface table? and if there are no interface tables then based on what should we create the flat file?

Thanks and regards

  Was this answer useful?  Yes


  • Feb 27th, 2017

In the same instance of Oracle Apps, multiple organizations are their if we don't use staging table other organization data comes into scenario & it will create ambiguity.

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.


Related Answered Questions


Related Open Questions