LOAD DATA INFILE <dataFile> APPEND INTO TABLE <tableName> FIELDS TERMINATED BY '<separator>' (<list of all attribute names to load>)
LOAD DATA INFILE test.dat INTO TABLE test FIELDS TERMINATED BY '|' (i, s)
1|foo 2|bar 3| bazRecall that the attribute list of test specified in test.ctl is (i, s), where i has the type int, and s has the type char(10). As the result of loading test.dat, the following tuples are inserted into test:
(1, 'foo') (2, 'bar') (3, ' baz')
cat bad_myRel.dat | tr -d '\015' > myRel.dat
If you're an emacs fan, type in the following sequence to modify your current buffer:
ESC-x replace-string CTRL-q CTRL-m ENTER ENTER
sqlldr <yourName> control=<ctlFile> log=<logFile> bad=<badFile>Everything but sqlldr is optional -- you will be prompted for your username, password, and control file. <ctlFile> is the name of the control file. If no file name extension is provided, sqlldr will assume the default extension ".ctl". The name of the data file is not needed on the command line because it is specified within the control file. You may designate <logFile> as the log file. If no file name extension is provided, ".log" will be assumed. sqlldr will fill the log file with relevant information about the bulk load operation, such as the number of tuples loaded, and a description of errors that may have occurred. Finally, you may designate <badFile> as the file where bad tuples (any tuples for which an error occurs on an attempt to load them) are recorded (if they occur). Again, if no file extension is specified, Oracle uses ".bad". If no log file or bad file are specified, sqlldr will use the name of the control file with the .log and .bad extensions, respectively.
As a concrete example, if sally wishes to run the control file test.ctl and have the log output stored in test.log, then she should type
sqlldr sally control=test.ctl log=test.logReminder: Before you run any Oracle commands such as sqlldr and sqlplus, make sure you have already set up the correct environment by sourcing /afs/ir/class/cs145/all.env (see Getting Started With Oracle).
LOAD DATA INFILE * INTO TABLE test FIELDS TERMINATED BY '|' (i, s) BEGINDATA 1|foo 2|bar 3| bazThe trick is to specify "*" as the name of the data file, and use BEGINDATA to start the data section in the control file.
CREATE TABLE foo ( i int, d date );In the control file, when you describe the attributes of foo being loaded, you follow the attribute d by its type DATE and a date mask. A date mask specifies the format your date data will use. It is a quoted string with the following conventions:
LOAD DATA INFILE * INTO TABLE foo FIELDS TERMINATED BY '|' (i, d DATE 'dd-mm-yyyy') BEGINDATA 1|01-01-1990 2|4-1-1998Notice that, as illustrated by the second tuple above, a field can be shorter than the corresponding field in the date mask. The punctuation "-" tells the loader that the day and month fields of the second tuple terminate early.
CREATE TABLE foo (x VARCHAR(4000));
Then a sample control file should look like:
LOAD DATA INFILE <dataFile> INTO TABLE foo FIELDS TERMINATED BY '|' (x CHAR(4000))Note that the declaration takes the form CHAR(n) regardless of whether the field type was declared as CHAR or VARCHAR.
3||5 |2|4 1||6 ||7would result in inserting the following tuples in the relation:
(3, NULL, 5) (NULL, 2, 4) (1, NULL, 6) (NULL, NULL, 7)Keep in mind that any primary keys or other constraints requiring that values be non-NULL will reject tuples for which those attributes are unspecified.
Note:If the final field in a given row of your data file will be unspecified (NULL), you have to include the line TRAILING NULLCOLS after the FIELDS TERMINATED BY line in your control file, otherwise sqlldr will reject that tuple. sqlldr will also reject a tuple whose columns are all set to NULL in the data file.
If you do not wish to enter values for any row of a given column, you can, as mentioned above, leave that column out of the attribute list altogether.