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

Posted on 2006-04-29
Last Modified: 2008-02-07

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

Question by:mahee999
    1 Comment
    LVL 34

    Accepted Solution

    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

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
    This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
    This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
    This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now