This discussion is archived
9 Replies Latest reply: Mar 11, 2011 12:59 AM by BluShadow RSS

Help In loading data from a flat file

NikhilJuneja Newbie
Currently Being Moderated
Hi ALL,

Help required for loading data into Oracle Tables:

Following is the scenario:

--One time task of data migration ( received data in flat files) one having around 64K records another having around 8.5 Lac (0.85 m) records

--Tables already exists but the structure is a little different (2 tables present with foreign key contrainst dependancy among them)


Following is the solution which came to my mind at the start..:-

load the file in a new table with all columns as Varchar2 (there are some columns with date time) using sqlloader

and then insert into the existing tables using cursor for loop.

Just wanted to know of the approach used is correct and is there any good / new solution for this in terms of performance or any new feature.

Oracle Version: Oracle DB 10g Enterprise Edition Release 10.2.0.4.0 - 64 bit


Thanks,
Nik
  • 1. Re: Help In loading data from a flat file
    Hoek Guru
    Currently Being Moderated
    You should read about External Tables, which is a more flexible and powerful feature that SQL*Loader.

    http://www.oracle.com/pls/db102/search?remark=quick_search&word=external+table
  • 2. Re: Help In loading data from a flat file
    NikhilJuneja Newbie
    Currently Being Moderated
    Thanx Hoek for immediate response, will work on those for sure..

    Could you please let me know if I can use those for loading 0.8m rows into some other tables(fetching records using cursor)?
  • 3. Re: Help In loading data from a flat file
    theoa Pro
    Currently Being Moderated
    Looks to me that sql*loader is the right tool for the job.

    I would not use an intermediate table. Just specify in the .ctl file the date format used, and a bad file for all invalid records.
  • 4. Re: Help In loading data from a flat file
    Hoek Guru
    Currently Being Moderated
    I'm sorry, but I tend to believe that's bad advice in the year of 2011:
    I would not use an intermediate table.
    An external table is not, I repeat not, an 'intermediate table'.
    An external table facilitates you to treat a file as if it were a table.
    I guess you missed that point.
    It is a very sophisticated layer. It is 'SQL*Loader 2.0'.
    It enables you to 'query a file'. You can do DML from that easily.
    You can do SQL on a file if you use an external table, you can't do that when you use SQL*Loader.
    You'll have the power of SQL in order to load your data.
    You can even load multiple files at once,
    You'll have no problem whatsoever formatting/calculating and so on.
    You can do much, much more stuff using external tables instead of SQL*Loader.

    Do the right search on http://asktom.oracle.com and you'll see.
  • 5. Re: Help In loading data from a flat file
    theoa Pro
    Currently Being Moderated
    hoek wrote:
    I'm sorry, but I tend to believe that's bad advice in the year of 2011:
    I would not use an intermediate table.
    An external table is not, I repeat not, an 'intermediate table'.
    I know about external tables.
    I was referring to the intermediate table the original poster talked about in the first post:
    >
    load the file in a new table with all columns as Varchar2 (there are some columns with date time) using sqlloader
    >

    Since this is a one-time read-the-flat-files-into-an-existing-table, using sqlloader would be IMHO just a little bit easier than defining an external table (DDL), insert the records in the existing table (DML), and then delete the external table again (DDL).
    If it were a repeating job, then an external table would be the way to go.
  • 6. Re: Help In loading data from a flat file
    BluShadow Guru Moderator
    Currently Being Moderated
    theoa wrote:
    Since this is a one-time read-the-flat-files-into-an-existing-table, using sqlloader would be IMHO just a little bit easier than defining an external table (DDL), insert the records in the existing table (DML), and then delete the external table again (DDL).
    If it were a repeating job, then an external table would be the way to go.
    Why would using SQL*Loader, creating a control file for single use and then deleting that control file again, be any different to creating an external table for single use? It's just as much work and SQL*Loader offers less flexibility. The only 'advantage' SQL*Loader has is that it can upload data from a client machine to the server more easily than external tables can but, in that case, one should seriously question why the 'user' has got data on their client and it's not on the server.
  • 7. Re: Help In loading data from a flat file
    NikhilJuneja Newbie
    Currently Being Moderated
    Decided to go in for external table.. and performance is not that bad..

    One more query on this.. currently I am working on Oracle server itself (client on the same machine)

    But our UAT servers have a different configurations. we work through separate server having client only

    Will external table work on this scenario... or we need to modify the definition.
  • 8. Re: Help In loading data from a flat file
    Justin Cave Oracle ACE
    Currently Being Moderated
    Using an external table requires that the file is physically on the database server. Your middle tier would likely need to move the file to an appropriate location on the database sserver.

    Justin
  • 9. Re: Help In loading data from a flat file
    BluShadow Guru Moderator
    Currently Being Moderated
    Nikhil Juneja wrote:
    Decided to go in for external table.. and performance is not that bad..

    One more query on this.. currently I am working on Oracle server itself (client on the same machine)

    But our UAT servers have a different configurations. we work through separate server having client only

    Will external table work on this scenario... or we need to modify the definition.
    To add to what Justin said, if the file is not on the database server you have two main options:

    1) map a drive/directory from the database server to the machine where the file is located (_not_ typically the recommended options)
    2) transfer the file from the other machine to the database server using FTP. This can be scripted at the operating system, or you can use a 3rd party FTP package in Oracle to control the FTP process through PL/SQL (e.g. Chris Poole's XUTL_FTP package: http://www.chrispoole.co.uk/apps/xutlftp.htm)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points