Note: If the .ctl files are double clicked they will open in Visual Studio. These files can be created and edited in a text editor, to avoid using Visual Studio, right click and use open with (a text editor).
Once the control files and the text files containing the data are uploaded, we can use the loader. Before we do so we should explore the control file. The line numbers are used here to help explain how this file works.
Line 1 |
LOAD DATA is the command to populate the table |
Line 2 |
INFILE identifies the text file that contains the data to be uploaded. Remember, Linux/Unix operating systems are case sensitive, so be careful here |
Line 3 |
INTO TABLE <TABLENAME> identifies which table the data is to be loaded into (in this case INVOICEITEM. Remember, the tables must already have been created with the CREATE TABLE command |
Line 4 |
FIELDS TERMINATED BY ',' tells Oracle what the delimiter is between each field. The delimiter must be enclosed by single quotes. In this case it is a comma, which is generally used as the default delimiter by most applications |
Line 5 |
OPTIONALLY ENCLOSED BY '"' identifies strings (text datatypes) for Oracle. Once again the identifier must be enclosed by single quotes. In this case it is a double quote, which is generally used as the default text identifier by most applications |
Line 6 |
TRAILING NULLCOLS is used to tell Oracle that not all the fields contain data (NULL values are allowed). This line is not necessary if it is known that all fields in a record must contain a value that is NOT NULL |
Line 7 |
The list of the fields in the table. Fields must be listed in the same order as the table and must be enclosed by brackets |
Note: Tables must be created in the database prior to using SQL*Loader
Log on to redstart using SSH or PuTTY.
To run the control files use the following command at the prompt:
[username@redstart username]$ sqlldr sxxxxxx/password, control=filename.ctl;
If all goes well, your client screen should look like this:
There are a number of ways to check if all records were loaded successfully into the table.
If you know the number of records to be loaded (this is only suited to very small numbers of records), a quick method is to log on to SQL*Plus and run a SELECT command, for example:
SELECT COUNT(*) FROM INVOICEITEM;
