• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1743
  • Last Modified:

loading flat file to TEMP table in oracle then move data to real tables


desparate situation,can anyone suggest me on this,

my company database is getting data inserted from different exteral source systems,data will come as a flat file,
so,making use of sql loader,UNIX (for batch process) dump data into temp tables then  perform validations and load data into REAL tables,
it looked straight forward when i hear this,but what happend is we dont have any designer to do all the high level design,techinical design,
so,all the process from scrath to end will be done by me,as i am more into coding plsql n never had this sort of exp i need your valuable suggestion to make my job done.
can you please suggest me answers for following questions:

1,design flow diagrams,sequence diagrams to show how flow should follow to each step,

2,all the process should be automated,for this i am thinking off batch process,i have got some basic knowledge of UNIX,

3,writing data abstraction layer to make future changes easy without effecting whole application code(i got no idea how data abstraction layer works),

i am basically oracle PL/SQL guy,i didnt have exp on above requirement but i can do it with your help,i am in desparate situation as i work for financial company,tight timelines,
please help me,your help will be greatly appreciated,
Thanks for having a look into my question n thanks in advance to suggesting me answers

1 Solution
Mark GeerlingsDatabase AdministratorCommented:
Yes, it can work to get data from flat (ASCII) files into work tables in Oracle, then perform validations, conversions, etc. and load the results into REAL tables.

But, depending on which Oracle version you have (and maybe depending on whether the data files come from the same O/S as your Oracle server or not) there are different options available to you.

One thing you cannot do is to use SQL*Loader to load data into global temp tables.  That would be nice, but global temporary tables are session-specific, and I don't know of a way to run SQL*Loader, then do some PL\SQL processing in that same Oracle sessions.  As far as I know, SQL*Loader closes its session after it finishes the load, so if it loads a global temp table, there isn't an opportunity to use that data before the temp table gets cleared when the session finishes.

If you have Oracle9 or 10, you can consider using "external tables" to save the step of loading a work table.  External tables basically allow queries from an ASCII file using a control file very similar to what SQL*Loader uses.  We had some trouble with them though when we tried using ASCII data files from a Windows source with our Oracle10 dataabse on Linux, due to the different end-of-line character on Windows text files, compared to Unix/Linux text files.  (Maybe that is due to our relative inexperience with Linux, and no Unix experience.  Maybe a more-experienced Unix administrator would know how to convert the end-of-line character on the files from Windows to what Linux or Unix expects.)

It will work for you to use SQL*Loader to load the ASCII files into "work" tables, but these may need to have all varchar2 columns, even for the data that should be numbers and/or dates, since the incoming data may not be clean or consistent enough for SQL*Loader to handle the data type conversions without error.

Another option to consider is to use a PL\SQL procedure that uses utl_file to read the ASCII files directly, one line at a time, do the validations, conversions etc. that are required for each record, then insert the records directly into the "REAL" tables in one step.  This will not be as fast as SQL*Loader, but it may be faster than a two-step approach that invloves SQL*Loader plus work tables.

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now