10 Oracle SQLLDR Command Examples (Oracle SQL*Loader Tutorial)

by Ramesh Natarajan on June 25, 2012

If you are using Oracle database, at some point you might have to deal with uploading data to the tables from a text file.

This article provides 10 practical examples on how to upload data from a flat file to Oracle tables.

Input data file for SQL*Loader

This is the input text file that contains the data that needs to be loaded into an oracle 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, we’ll use the following employee.txt file to upload the data to the employee table.

$ cat employee.txt
100,Thomas,Sales,5000
200,Jason,Technology,5500
300,Mayla,Technology,7000
400,Nisha,Marketing,9500
500,Randy,Technology,6000
501,Ritu,Accounting,5400

SQL*Loader Control File

This contains the instructions to the sqlldr utility. 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.

$ cat example1.ctl
load data
 infile '/home/ramesh/employee.txt'
 into table employee
 fields terminated by ","
 ( id, name, dept, salary )

The above control file indicates the following:

  • infile – Indicates the location of the input data file
  • into table – Indicates the table name where this data should be inserted
  • fields terminated by – Indicates the delimiter that is used in the input file to separate the fields
  • ( id, name, dept, salary ) – Lists the name of the column names in the table into which the data should be uploaded

1. Basic Upload Example Using SQL*Loader

First, create the employee table as shown below.

SQL> create table employee
(
  id integer,
  name varchar2(10),
  dept varchar2(15),
  salary integer,
  hiredon date
)

Next create the control file that explains what needs to be upload and where.

$ cat sqlldr-add-new.ctl
load data
 infile '/home/ramesh/employee.txt'
 into table employee
 fields terminated by ","
 ( id, name, dept, salary )

Note: If you have the values inside the data file enclosed with double quote, use this in your control file: fields terminated by “,” optionally enclosed by ‘”‘

Note: If you don’t have the table created, you’ll get the following error message:

SQL*Loader-941: Error during describe of table EMPLOYEE
ORA-04043: object EMPLOYEE does not exist

You can pass the userid and password to the sqlldr command using any one of the following format. As you see below, both of these will prompt you for control file location, as it was not given in the command line.

$ sqlldr scott/tiger

(or)

$ sqlldr userid=scott/tiger
control =
SQL*Loader-287: No control file name specified.

Execute the sqlldr command to upload these new record to the empty table by specifying both uid/pwd and the control file location as shown below.

$ sqlldr scott/tiger control=/home/ramesh/sqlldr-add-new.ctl
Commit point reached - logical record count 5

Verify the the records are created in the database

SQL> select * from employee;

        ID NAME       DEPT                SALARY HIREDON
---------- ---------- --------------- ---------- -------
       100 Thomas     Sales                 5000
       200 Jason      Technology            5500
       300 Mayla      Technology            7000
       400 Nisha      Marketing             9500
       500 Randy      Technology            6000

This will create the output log file in the same name as the data file, but with the .log extension (instead of .ctl). Partial output shown below.

$ cat sqlldr-add-new.log

Control File:   /home/ramesh/sqlldr-add-new.ctl
Data File:      /home/ramesh/employee.txt

Table EMPLOYEE:
  5 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.

Elapsed time was:     00:00:00.04
CPU time was:         00:00:00.00

If you are new to Oracle database, and like to install it, follow this Oracle 11g installation guide.

2. Inserting Additional Records

Let us say you want to add two new employees to the employee table from the following newemployee.txt file.

$ vi newemployee.txt
600,Ritu,Accounting,5400
700,Jessica,Marketing,7800

If you create a similar control file like the previous example, you might get the following error message.

$ sqlldr scott/tiger control=/home/ramesh/sqlldr-add-more.ctl
SQL*Loader-601: For INSERT option, table must be empty.  Error on table EMPLOYEE

The above indicates that the table should be empty before you can upload data using sql*loader.

If you like to insert more data to the tables without having to delete the existing rows, use the “append’ command as shown in the following control file.

$ vi sqlldr-append-more.ctl
load data
 infile '/home/ramesh/newemployee.txt'
 append
 into table employee
 fields terminated by ","
 ( id, name, dept, salary )

Now, if you do sqlldr this will append the data.

$ sqlldr scott/tiger control=/home/ramesh/sqlldr-append-more.ctl
Commit point reached - logical record count 2

Verify that the records are appended successfully

SQL> select * from employee;

        ID NAME       DEPT                SALARY HIREDON
---------- ---------- --------------- ---------- -------
       100 Thomas     Sales                 5000
       200 Jason      Technology            5500
       300 Mayla      Technology            7000
       400 Nisha      Marketing             9500
       500 Randy      Technology            6000
       600 Ritu       Accounting            5400
       700 Jessica    Marketing             7800

3. Data inside the Control File using BEGINDATA

You can also specify the data directly inside the control file itself using BEGINDATA keyword. i.e Anything that comes after BEGINDATA will be treated as data to be uploaded to the table as shown below.

$ cat sqlldr-add-new-with-data.ctl
load data
 infile *
 into table employee
 fields terminated by ","
 ( id, name, dept, salary )
begindata
100,Thomas,Sales,5000
200,Jason,Technology,5500
300,Mayla,Technology,7000
400,Nisha,Marketing,9500
500,Randy,Technology,6000

Note: The infile will say ‘*’ in this case, as there is no input data file name for this example.

Execute sqlldr to upload the data from the control file.

$ sqlldr scott/tiger control=/home/ramesh/sqlldr-add-new-with-data.ctl

4. Date format and Different Delimiter

This example shows how to specify a date format in the control file and how to handle different delimiters in a data file

The following example has different delimiters ($ after name, ^ after department).

$ cat employee-date.txt
100,Thomas$Sales^5000,31-JAN-2008
200,Jason$Technology^5500,01-Feb-2005
300,Mayla$Technology^7000,10-Aug-2000
400,Nisha$Marketing^9500,12-Dec-2011
500,Randy$Technology^6000,01-JAN-2007

Create the following control file and indicate the field delimiters for each and every field using “terminated by” as shown below.

$ cat sqlldr-date.ctl
load data
 infile '/home/ramesh/employee-date.txt'
 into table employee
 fields terminated by ","
 ( id, name terminated by "$", dept terminated by "^", salary, hiredon DATE "dd-mon-yyyy" )

Load the data using sqlldr as shown below.

$ sqlldr scott/tiger control=/home/ramesh/sqlldr-date.ctl

Verify that the data got loaded properly as shown below.

SQL> select * from employee;

        ID NAME       DEPT                SALARY HIREDON
---------- ---------- --------------- ---------- ---------
       100 Thomas     Sales                 5000 31-JAN-08
       200 Jason      Technology            5500 01-FEB-05
       300 Mayla      Technology            7000 10-AUG-00
       400 Nisha      Marketing             9500 12-DEC-11
       500 Randy      Technology            6000 01-JAN-07

5. Fixed Length Data Upload

If you have a data file without data that are fixed length (i.e without any delimiter), you can use this example to upload this data.

For this example, let us use the following file which has data that are of fixed length. For example, 1st three characters are always employee number, Next 5 characters are always employee name, etc.

$ cat employee-fixed.txt
200JasonTechnology5500
300MaylaTechnology7000
400NishaTechnology9500
500RandyTechnology6000

Create the following control file, where you specific the position of each and every field as shown below usig the “Position(start:end)” syntax.

$ cat sqlldr-fixed.ctl
load data
 infile '/home/ramesh/employee-fixed.txt'
 into table employee
 fields terminated by ","
 ( id position(1:3), name position(4:8), dept position(9:18), salary position(19:22) )

Load this fixed length data using the sqlldr as shown below.

$ sqlldr scott/tiger control=/home/ramesh/sqlldr-fixed.ctl

Verify that the data got loaded.

SQL> select * from employee;

        ID NAME       DEPT                SALARY HIREDON
---------- ---------- --------------- ---------- ---------
       200 Jason      Technology            5500
       300 Mayla      Technology            7000
       400 Nisha      Technology            9500
       500 Randy      Technology            6000

6. Change the data during upload

You can also massage the data and change it during upload based on certain rules.

In the following control file:

  • id is incremented by 999 before uploading. i.e if the emp id is 100 in the data file, it will be loaded as 1099
  • Convert the name to upper case and load it. This uses the upper function.
  • If the department contains the value “Technology” change it to “Techies”. This uses decode function
$ cat sqlldr-change-data.ctl
load data
 infile '/home/ramesh/employee.txt'
 into table employee
 fields terminated by ","
 ( id ":id+999",
   name "upper(:name)",
   dept  "decode(:dept,'Technology','Techies', :dept)",
   salary
  )

Load the data using this control file which will massage the data before uploading it.

$ sqlldr scott/tiger control=/home/ramesh/sqlldr-change-data.ctl

Verify that the data got changed while loading as per our rules.

SQL> select * from employee;

        ID NAME       DEPT                SALARY HIREDON
---------- ---------- --------------- ---------- ---------
      1099 THOMAS     Sales                 5000
      1199 JASON      Techies               5500
      1299 MAYLA      Techies               7000
      1399 NISHA      Marketing             9500
      1499 RANDY      Techies               6000

7. Load data from multiple files

To load data from multiple files, you just have to specify multiple infile in the control file.

The following control file loads data from two different data files (employee.txt and newemployee.txt) to the employee table.

$ sqlldr-add-multiple.ctl
load data
 infile '/home/ramesh/employee.txt'
 infile '/home/ramesh/newemployee.txt'
 into table employee
 fields terminated by ","
 ( id, name, dept, salary )

Load the data using this control file which will upload data from multiple data files as shown below.

$ sqlldr scott/tiger control=/home/ramesh/sqlldr-add-multiple.ctl
Commit point reached - logical record count 5
Commit point reached - logical record count 7

8. Load data to Multiple Tables

Create another table called bonus which will have employee id and bonus columns.

create table bonus
  ( id integer,
    bonus integer
  );

Create the employee-bonus.txt data file that contains the fields: id, name, department, salary, bonus

$ cat employee-bonus.txt
100 Thomas Sales      5000 1000
200 Jason  Technology 5500 2000
300 Mayla  Technology 7000 2000
400 Nisha  Marketing  9500 1000
500 Randy  Technology 6000 3000

Create the control file as shown below, which will upload the data from the above file to two different tables. 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.

$ cat sqlldr-multiple-tables.ctl
load data
 infile '/home/ramesh/employee-bonus.txt'
 into table employee
 ( id position(1:3),
   name position(5:10),
   dept position(12:21),
   salary position(23:26))
 into table bonus
 ( id position(1:3),
   bonus position(28:31))

Load the data to multiple tables using this control file as shown below.

$ sqlldr scott/tiger control=/home/ramesh/sqlldr-multiple-tables.ctl

Verify that the data got loaded to multiple tables successfully.

SQL> select * from employee;

        ID NAME       DEPT                SALARY HIREDON
---------- ---------- --------------- ---------- ---------
       100 Thomas     Sales                 5000
       200 Jason      Technology            5500
       300 Mayla      Technology            7000
       400 Nisha      Marketing             9500
       500 Randy      Technology            6000

SQL> select * from bonus;

        ID      BONUS
---------- ----------
       100       1000
       200       2000
       300       2000
       400       1000
       500       3000

9. Handling Bad (Rejected) Records

In the following example, we have two bad records. Employee id 300 and 500 has salary column which is not numeric.

$ cat employee-bad.txt
100,Thomas,Sales,5000
200,Jason,Technology,5500
300,Mayla,Technology,7K
400,Nisha,Marketing,9500
500,Randy,Technology,6K

Use the following control file for this example.

$ cat sqlldr-bad.ctl
load data
 infile '/home/ramesh/employee-bad.txt'
 into table employee
 fields terminated by ","
 ( id, name, dept, salary )

Load the data (including the invalid records) using this control file as shown below.

$ sqlldr scott/tiger control=/home/ramesh/sqlldr-bad.ctl
Commit point reached - logical record count 5

As you see from the abvoe output, it still says “logical record count 5″, but you should check the log files to see if it has rejected any records.

The log file indicates that 2 records are rejected as shown below:

Control File:   /home/ramesh/sqlldr-bad.ctl
Data File:      /home/ramesh/employee-bad.txt
  Bad File:     /home/ramesh/employee-bad.bad
  Discard File:  none specified

Table EMPLOYEE:
  3 Rows successfully loaded.
  2 Rows not loaded due to data errors.

By default the rejected records are stored in a file that has the same name as the data file (but with .bad extension)

$ cat employee-bad.bad
300,Mayla,Technology,7K
500,Randy,Technology,6K

As you see below, the employee table has only 3 records (as 2 of them were rejected).

SQL> select * from employee;

        ID NAME       DEPT                SALARY HIREDON
---------- ---------- --------------- ---------- ---------
       100 Thomas     Sales                 5000
       200 Jason      Technology            5500
       400 Nisha      Marketing             9500

10. Load Specific Rows from a datafile

If you want to load only a specific records from a data file use the WHEN in the control file.

Add the line “when” next to “into table” line. In the following control file, the when clause indicates that it will load only the records that have dept as “Technology”.

$ cat sqlldr-when.ctl
load data
 infile '/home/ramesh/employee.txt'
 into table employee
 when dept = 'Technology'
 fields terminated by ","
 ( id, name, dept, salary )

Load the selective data (only the “Technology” records) using this control file as shown below.

$ sqlldr scott/tiger control=/home/ramesh/sqlldr-when.ctl
Commit point reached - logical record count 5

As you see from the above output, it still says “logical record count 5″, but you should check the log files to see how many records were loaded, and how many records were discarded because it didn’t match the when condition.

The following from the log file shows that 5 records were read, and 2 of them were discarded as it didn’t match the when condition.

Discard File:  none specified
Total logical records read:             5
Total logical records discarded:        2

Verify that only the selective records were loaded into the table.

SQL> select * from employee;

        ID NAME       DEPT                SALARY HIREDON
---------- ---------- --------------- ---------- ---------
       200 Jason      Technology            5500
       300 Mayla      Technology            7000
       500 Randy      Technology            6000

Linux Sysadmin Course Linux provides several powerful administrative tools and utilities which will help you to manage your systems effectively. If you don’t know what these tools are and how to use them, you could be spending lot of time trying to perform even the basic administrative tasks. The focus of this course is to help you understand system administration tools, which will help you to become an effective Linux system administrator.
Get the Linux Sysadmin Course Now!

If you enjoyed this article, you might also like..

  1. 50 Linux Sysadmin Tutorials
  2. 50 Most Frequently Used Linux Commands (With Examples)
  3. Top 25 Best Linux Performance Monitoring and Debugging Tools
  4. Mommy, I found it! – 15 Practical Linux Find Command Examples
  5. Linux 101 Hacks 2nd Edition eBook Linux 101 Hacks Book

Bash 101 Hacks Book Sed and Awk 101 Hacks Book Nagios Core 3 Book Vim 101 Hacks Book

{ 56 comments… read them below or add one }

1 Nice June 25, 2012 at 9:42 am

I will be reading Oracle & PL/SQL next month.
Thanks for the article.

2 hdaz June 25, 2012 at 10:16 am

Great Post.. thanks for the examples :)

3 Prasath June 25, 2012 at 9:54 pm

This is Nice….

We Need More Examples Like This……..

Thanks……….

4 pathum June 26, 2012 at 1:02 am

excellent article i love your all post
thanks for all

5 Lin Thein Naing July 13, 2012 at 2:22 am

Really awesome!!!! Appreciate ….

6 Manjula July 23, 2012 at 2:18 pm

Question. My data file has 30 columns. The table has 38 columns. 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). How do I do this? Any Idea?

7 Prithviraj July 30, 2012 at 7:31 am

@Ramesh: Awesome article. Very simple language and understandable examples. 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 ‘BAD=file.extension’
Which one is correct.
3) On link below:
http://stackoverflow.com/questions/8039012/disable-bad-discard-file-log-on-sql-loader
it is mentioned that, bad file can be diabled by setting BAD=NUL (in windows) or redirecting bad output to /dev/null (on linux). Is it possible? I do not see any references on oracle site for this.

@Manjula:
Ramesh has explained answer to your question in example 8.

8 Prithviraj July 30, 2012 at 7:46 am

Hello Ramesh,
Regarding disabling bad files i confirmed. In windows it can be done using: BADFILE NUL
And in linux it can be done using: BADFILE /dev/null

9 Rohit K August 5, 2012 at 3:00 am

Hi Ramesh,

This is simply superb :D

I need your help in loading the single file where the scenario quite clumsy for me:

The data file sale_exec.dat:
_____________________________________________
CUST_TYPE | DATE | AMT | DISCOUNT_AMT
X |20120101 | 200 | 20
X |20120101 | 400 | 30
Y |20120303 | 300 |40
Z |20120303 | 20 |50
_______________________________________________

The table structure is :
TABLE SALES
{
VENDORID NUMBER,
REPORT_DATE DATE,
LOAD_DATE DATE,
AMOUNT NUMBER
}

Scenario:
1. The data within the file sales_exec.dat has to be loaded into the table SALES
2. When CUST_TYP is X the Vendorid should be 1 , when Y it should load vendor ID with 2 and similarly when it is Z then Vendor ID should be 3
3. When the CUST_TYP 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 CUST_TYP is Z then I need to load the records with the DISCOUNT_AMT in the data file into the SALES table AMOUNT FIELD

The table data from the Data file after load should look like below:
_____________________________________________
VENDORID | REPORT_DATE| AMOUNT
1 |20120101 | 200
1 |20120101 | 400
2 |20120303 | 300
3 |20120303 | 50
_______________________________________________

Please let me know how should I proceed further and what should be my cntl file.

Thanks in advance!

Regards,
Rohit

10 Prithviraj August 6, 2012 at 5:45 am

@Rohit,
This is not possible using SQL loaders. 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.
Refer:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1710164700346004127

11 Rohit K August 6, 2012 at 9:36 am

Thank You Prithviraj .

We can do it using a control file this way.

I tried using the boundfiller,decodes and my CONTROL file will look like this:
INFILE=’sale_exec.dat’
APPEND
PRESERVE BLANKS
INTO TABLE SALES
FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘ TRAILING NULLCOLS
(
c1 BOUNDFILLER,
REPORT_DATE,
c2 BOUNDFILLER,
c3 BOUNDFILLER,
VENDORID “to_number(DECODE(:c1,’X',1,’Y',2,3))”,
AMOUNT “to_number(DECODE(:c1,’Z',c3,c2))”
)

This worked good for me.

Thank You once again.

Regards,
Rohit K

12 SantoshCA September 4, 2012 at 5:06 am

Hi Ramesh,
this is a very good writeup! Is there anything more advanced which SQLLDR can handle? Would be great if you could write something on this too.

13 Jurgen October 26, 2012 at 4:04 am

HI all,
I wanted too load mulitple files into the same tables from different ctl files for test work. The problem is i need to be able too identify the different files loaded in de database.
For example :
file1.ctl needs to be de name in de database under for example column ‘filename’
file2.ctl needs to be de name in de database under for example column ‘filename’

VENDORID | REPORT_DATE| AMOUNT|Filename

1 |20120101 | 200 |file1.ctl
1 |20120101 | 400 |file2.ctl

Anyone knows how i can get this accomplished.

Kind regards,

Jurgen

14 Mahes Tripathi November 6, 2012 at 5:44 am

Thanks for giving such valuable examples. 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.

15 souji November 23, 2012 at 7:01 pm

Very nice article.
Better understandable format. Explained well.
We need more examples like this.

16 souji November 23, 2012 at 7:16 pm

Hi All,

I have a flatfile(notepad), which has data not in order, fields separated by space, that too not orderly separated. Between fields there is space, but not ordered one. like

consultant name vendor details email contact end client
David Raj jason Roy jasonroy@example.com (010) 110-1101 CAAM

above line, you can notice that there is no specified space between fields. i have nearly 7000 rows of data in notepad. 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.

In this case is there any kind of solution/control file format to load the data into tables. It would be great if anyone can solve my problem.

Thanks
souji

17 sanchit December 13, 2012 at 7:52 pm

very nice tutorial…. great job

18 Kenneth Y January 10, 2013 at 1:34 pm

Wow! Excellent guide!

19 Dhawal Limbuwala January 24, 2013 at 5:33 am

Hi I Am Doing Computer Science And This Helps Me To Lot Thank You So Much.

20 ashok March 6, 2013 at 4:36 am

nice tutorial ….simple to understand..

21 Imteyaz March 14, 2013 at 2:48 pm

Very good post! Appreciate it!!!

22 Naveen March 29, 2013 at 1:54 pm

Great Explanation , simple and clear.

23 Naresh April 5, 2013 at 11:18 am

I have a excel sheet, with no comma separated, and not enclosed in “..
its just normal excel sheet with data for 7 columns..

Can anyone tell me how to load it…

24 Ashok May 13, 2013 at 11:26 pm

Nic explanation

25 Praveen Kumar July 23, 2013 at 8:45 pm

Thanks.
The article is very good and easily understandable.

26 praveen July 31, 2013 at 6:42 am

was really helpfull!

27 rajesh August 21, 2013 at 12:05 am

Nice explanation…, thank you so much!

28 Muhd Islam August 24, 2013 at 1:25 pm

Its great explanation ….

29 Gauthama P August 28, 2013 at 4:41 am

Thanks!

30 charan September 11, 2013 at 12:40 am

its very useful helpful for starters… thanks for a very simple explanation

31 Shivanand September 11, 2013 at 6:35 am

Nice article ..Thanks !

32 Vivek V September 27, 2013 at 6:06 am

Really nice article

33 Aabid October 17, 2013 at 1:30 am

very-2 nice example

34 CRP October 23, 2013 at 1:24 pm

Greate article, thank you for sharing.

35 Prasad October 29, 2013 at 12:27 am

After executing this below command from oracle forms6 sqlldr80 does not resume back to form, it remains there cursor blinking after Commit point reached – logical record count 10. It started from last week only, never happend before… dont know what made to act like this?

sqlldr80 USERID=kaps/kaps@kaporacle CONTROL=I:\load.CTL LOG=I:\LoadLOG.LOG BAD=I:\LoadBAD.BAD

Is there any way to terminate the control file i mean to exit sqlldr and come back to DOS prompt? It is not coming out of sqlldr mode… but inserting data is done perfectly.. any help appreciated…

36 Satya October 31, 2013 at 4:31 am

Very Nice !!!!!! BUT how to load default value to a field.

37 Uday November 28, 2013 at 5:29 am

I have a different scenario. 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. I would like to load c1 to c5 columns data from the csv file to c1 to c5 columns in the table. Can we skip columns any columns from csv or can we map csv columns to table columns in the loader control file ?

38 Uday November 28, 2013 at 5:55 am

I got it. Keeping FILLER keyword to the right of column name would skip the column:

For e.g.:

OPTIONS (SKIP=1)
LOAD DATA
INFILE ‘source.csv’
BADFILE ‘source.csv.bad’
DISCARDFILE ‘source.csv.dsc’
APPEND
INTO TABLE tab_name
FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘
TRAILING NULLCOLS
( a FILLER
,c1
,c2
,c3
,c4
,c5
)

39 Rajiv January 9, 2014 at 10:42 am

Nice doc

40 annuj January 10, 2014 at 8:25 am

how to insert last n rows from a data file through control file in sql loader?

41 N S KRISHNA February 5, 2014 at 11:48 pm

Hi,
How to insert alternate rows into two different tables. I mean to insert 1,3,5,7,9,…… Records into Table1 and 2,4,6,8,10,….. into Table 2. Is there any option to build control to achieve this? Please let me know.
Thank You

42 vikram March 4, 2014 at 2:33 pm

simply great !!! Thank u so much….

43 anudeep March 14, 2014 at 3:41 am

nice article

44 Prasanna Suri March 17, 2014 at 1:33 am

Nice way to explain the things….great work:)

45 phani April 9, 2014 at 6:56 am

Very good!!!! good knowledge…

46 Tushar Sharma April 16, 2014 at 8:10 am

Hi,

Thanks for great article, Is there any way to write control file with
update statements. I want to update few records. Is there any way around ?

Thanks
-Tushar

47 M.Ayaz April 18, 2014 at 9:05 am

very nice and informative.

48 Mayur June 2, 2014 at 8:14 am

If I have too many columns which is not a feasible option to write each and every… how can this be done??
Can anyone please suggest

49 Pratibha June 9, 2014 at 3:03 am

Article was really helpful. Easy and simple examples to understand. Please post such articles on daily basis.

50 Tushar June 20, 2014 at 3:18 pm

Thanx. I learned a lot here.

51 Vinoth June 30, 2014 at 6:02 am

Simple and Best ..
Easy to Learn

52 MANOJ REDDY July 4, 2014 at 10:32 am

i am learned with half an hour.
thank you very much

53 Sudhakar July 11, 2014 at 4:26 am

Hi All,

Thanks for the wonderful sharing. I am stuck here. hope someone can help me.
I am trying to upload a flat file(824 rows) in an oracle db but i am getting an error.

CONTROL FILE
====================
load data
infile ‘/sit/bcpr/bcpdata/ETL/SUDS/hk_stg_finiq_cust_acct.txt’
into table ABC123
fields terminated by “,” optionally enclosed by ‘”‘ TRAILING NULLCOLS
(BUSINESS_DATE,
ACCT_NO,
ID_DOC_TYPE_PRIM,
ID_DOC_NO_PRIM,
ACCT_REL_TYP_PRIM,
ID_DOC_TYPE1,
ID_DOC_NO1,
ACCT_REL_TYP1,
ID_DOC_TYPE2,
ID_DOC_NO2,
ACCT_REL_TYP2,
ID_DOC_TYPE3,
ID_DOC_NO3,
ACCT_REL_TYP3,
ID_DOC_TYPE4,
ID_DOC_NO4,
ACCT_REL_TYP4,
ACCT_STATUS,
ACCT_OPEN_BRANCH_NO,
ARM_CODE,
AGRMT_DT,
ACCT_OPN_DT,
ACCT_CL_DT,
PRD_MAP,
COUNTRY_CD,
CREATE_DATE,
CREATE_USER,
SRC_STM_SHORT_NM)

ERROR
==========
SQL*Loader: Release 10.2.0.1.0 – Production on Fri Jul 11 17:56:59 2014

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL*Loader-350: Syntax error at line 16388.
Expecting keyword INTO, found “)”.
)
^
..
plase help me

54 siva July 16, 2014 at 6:25 am

Thanks..it easy to understand to New guys also….

55 shakeer July 24, 2014 at 4:31 am

U forgot to mension insert command before into.

56 Anonymous July 30, 2014 at 12:31 pm

Good Example on SQL Loader.

Leave a Comment

Previous post:

Next post: