The Java Developers Almanac 1.4


Order this book from Amazon.

   
Home > List of Packages > java.sql  [73 examples] > Importing and Exporting  [4 examples]

e294. Loading a Flat File to an Oracle Table

When loading a flat file into Oracle, the fields in the flat file should be separated by delimiters such as tabs or commas. The delimiter can be specified in the control file. The loader will map the fields in the file to the columns in the table. The first field is stored in the first column; the second field is stored in the second column, etc. If there are more fields than columns in the table, the remaining fields will be ignored. If there are less fields than columns in the table, the missing fields will be inserted as null values in the associated columns.

This example loads a flat file called infile.dat to an Oracle table named oracle_2_table with two columns of type INTEGER and VARCHAR(20). To load, run the sqlldr command from the shell as follows:

    shell> sqlldr userid=userid@sid, control=ctrl_file.txt, log=log, errors=100
    123\tString1
    234\tString2
The contents of the control file ctrl_file.txt are:
    load data
    infile 'infile'
    append into table oracle_2_table
    fields terminated by '  '
    trailing nullcols
    (col1 decimal(13,0),
    col2 varchar(40))
To specify a different terminator, such as a comma, change the fourth line above to:
    fields terminated by ','
    If oracle_2_table is already defined, you can use the following syntax for
    the control file ctrl_file.txt:
    
    load data
    infile 'infile'
    append into table oracle_2_table
    fields terminated by '  '
    trailing nullcols
    (col1,
     col2)

 Related Examples
e292. Loading a Flat File to a MySQL Table
e293. Exporting an Oracle Table to a Flat File
e295. Exporting a MySQL Table to a Flat File

See also: Batching    Connections    Database Meta Data    Deleting Data    Drivers    Inserting and Updating Data    Oracle OBJECTs    Oracle VARRAYs    Procedures and Functions    Retrieving Data    Scrollable Result Sets    Tables    Updatable Result Sets   


© 2002 Addison-Wesley.