RE: Import large dataset (table) on a CD into Oracle

Hi,

 Here's what I do when I have large data sets to be loaded ( if they are not available by way of exporting )

 Say for e.g. you have a file that contains 3 fields ( name tag id, name_ad , address_line )

  1. Have the data file ( flat file ) dilimited by a % sign for e.g.

         a sample file :

         1%Name 1%address 1%
         2%Name 2%address 2%

    Save this as  testdata.txt   ( for e.g. )

 2. Next create a control file as

       LOAD DATA
       INFILE '$HOME/dat/testdata.txt'
      APPEND 
      INTO  TABLE YOUR_TABLE_NAME
      FIELDS TERMINATED BY "%" OPTIONALLY ENCLOSED BY '"'
      ( name_id, name_ad, address_line)

  Save this as  say    ld_testload.ctl  

  You use 
        APPEND  - to append records if records are already existing
        INSERT    - to insert new 
        REPLACE  - to replace existing records ( which means the old data is gone and new ones are inserted)
      
 Note : 
  Here the YOUR_TABLE_NAME is the actual table name that you want the data loaded to.

  Also the file path and file that is on the second line is the actual Unix path where your data file is residing. Usually I ftp
   the data file onto the proper location.

  And with the optionally enclosed clause - all it means you can use that particular character to enclose each data value
  before the delimiter.

 3. Then lastly create a shell script file as

     sqlldr userid=your_db_uid/your_db_pwd control=$HOME/ctl/ld_testload.ctl log=$HOME/log/ld_testload.log errors=50000 bad=$HOME/bad/ld_testload.bad

  Save this as ld_testload.sh

  Note: 
     This entire command has to be in one line. DONOT split, as the instructions don't get through.

     Also your_db_uid  - is the Unix Logon User id to the box on which the database resides
       and your_db_pwd - is the Unix Pwd to the box on which the database resides.

     The ctl file path is the directory which resides in your home main directory on the Unix machine, same goes for the
   log file and the bad file ( this is in case if anything goes wrong other than the usual errors ).

 Once you have the 3 files from step 1, 2, & 3 . All you have to do is 

  type this below from the directory where the file 'ld_testload.sh' resides:

    . ld_testload.sh   

   ( i.e.   a dot, then a space then the file name and hit enter )

  Viola!! there you go the records from the flat file get loaded if the format of the data conforms to the data type of the columns 
of the table. 

  You can check by cd' ing to the appropriate log directory and look up for the log file . It will give in detail what it did with 
each record. Also in the control file you can specify for many formats to be applied to the data before loading, like make
the first character of every word upper case, or take only 30 characters from the data available for a particular column and so on.

 I hope this helps.

Thanks,
Pankaja


-----Original Message-----
From: David, Romeo B. (Govt) [mailto:[email protected]]
Sent: Monday, December 23, 2002 12:05 PM
To: Oracle
Subject: [oracle] RE: Import large dataset (table) on a CD into Oracle


It depends on what kind of files you have. Were they exported from an Oracle
database using the export utility, if so, you can use the import utility of
oracle. If they are just in some flat file format, you can use sqlloader
(you have to know the layout of the records, either they are delimited by
any character or they are of fixed record layout).

-----Original Message-----
From: Russell Moss [mailto:[email protected]]
Sent: Monday, December 23, 2002 11:13 AM
To: Oracle
Subject: [oracle] Import large dataset (table) on a CD into Oracle


Hi
Could anyone tell me how to import a large table (dataset)on a CD into 
Oracle! I have already created a table using SQl but of course with no 
data (rows). 
cheers
---
Change your mail options at http://p2p.wrox.com/manager.asp or 
to unsubscribe send a blank email to %%email.unsub%%.


---
Change your mail options at http://p2p.wrox.com/manager.asp or 
to unsubscribe send a blank email to %%email.unsub%%.


---
Change your mail options at http://p2p.wrox.com/manager.asp or 
to unsubscribe send a blank email to [email protected].