I would like to load c1 to c5 columns data from the csv file to c1 to c5 columns in the table.

Each and every records needs to be in a separate line, and the column values should be delimited by some common delimiter character.

For some of the examples mentioned below, well use the following employee.txt file to upload the data to the employee table.

This tells sqlldr the location of the input file, the format of the input file, and other optional meta data information required by the sqlldr to upload the data into oracle tables.

As you see below, both of these will prompt you for control file location, as it was not given in the command line.

For example, 1st three characters are always employee number, Next 5 characters are always employee name, etc.

As shown below, you should have two into table commands, and specify the position of the data which needs to be used to upload the data to that column.

Employee id 300 and 500 has salary column which is not numeric.

In the following control file, the when clause indicates that it will load only the records that have dept as Technology.

I need to pick only 3 columns from the data file (5th, 10th and 25th column) and load into the table (column 3rd, 9th and 16th column of table).

Thanks for sharing.

I have few questions for you: 1) In example 8, terminated clause is not mentioned, because fields are terminated by space.

So by derfault spaces are considered as delimiters 2) I read that Badfile can be explicitly specified using clause BADFILE filename.extension.

But on some links I see it mentioned as BADfile.extension Which one is correct.

On link below: it is mentioned that, bad file can be diabled by setting BADNUL (in windows) or redirecting bad output to devnull (on linux).

Is it possible I do not see any references on oracle site for this.

When the CUSTTYP is X and Y I need to load the records with the AMT in the data file into the SALES table AMOUNT field 4.

When the CUSTTYP is Z then I need to load the records with the DISCOUNTAMT in the data file into the SALES table AMOUNT FIELD.

Because data type of VendorId is numeric and from infile you are getting characters.

You have two approches here: 1) Either you process your infile first and replace X by 1, y by 2, z by 3.

Then use SQL loader using when condition to check what vendoe id is it and amount column should take what value.

OR 2) You need to use external tables and SQL functions in this scenario.

Could you please give one example of control file to upload data in a file and then call a procedure to implement some logic and populate main table.

I tried using field terminated by space but, it has taken the entire row of data from notepad as a single column data in table, remaining fields in table are empty.

It started from last week only, never happend before dont know what made to act like this.

I have a table with 5 columns, c1, c2, c3, c4, c5 and a csv file has 6 columns, a,c1,c2,c3,c4,c5.

Sqlldr Control File Defaultif