SQL*Loader – Loading Data from Data Files

In this tutorial you will learn about SQL*Loader – Input Data and Datafiles, Fixed Record Format, Variable Record Format and Stream Record Format.

SQL*Loader is useful when you need to load the files in batch mode. SQL* Loader supports three different type of data files. You will need to specify the “INFILE” parameter with the file format and additional parameters required.

Fixed Record Format: This format is useful when you have a data file with fixed layout.

Variable Record Format: This format is used when you have different record lengths in the data file. You will need to specify the record length in the beginning of the each record. This format provided greater flexibility to have the data loaded compared to fixed record formatted files.

Stream Record Format: This format is used when the records are not in the fixed or specified size. Each record can be any length and records will be identified by using the record terminator.

You can specify terminator_string either in character or hexadecimal format. Char is enclosed in single or double quotes and hexadecimal should be used when nonprintable characters like new line feed or tab characters.

There are few types of hex characters which can be used. Please note that these may change based on the operating system you are using. In Unix/Linux based systems, the default to the line feed character will be n and Windows uses either n or rn as the default record terminator. Just to avoid any issues with various character sets, you may want to check NLS_LANG parameters for your session. Check to make sure that your record terminator is not part of the data record.

There are various options available, please use “sqlldr” for the parameters and usage.

Now let us take a look at the loading some sample data to a table using command prompt and OEM interface.

{mospagebreak title= SQL*Loader – Loading data using OEM}
  • Log into OEM. (Note: If you have followed default installation with starter database, you will have a link in Oracle menu to “Database Control – “database name”. )
  • Login and select Data Movement,


  • Select Load Data from user file option. (If you have a control file already then you can use or select "Automatically Generate Control File" option).

In this tutorials we are going to select the first option, then use the generated control file to load the data using command prompt.

Oracle needs access to the host, enter the server login and password, if you prefer you can check “Save as Preferred Credentials” otherwise leave it unchecked.

Step 1 – Load Data: Data Files


Here is the sample file we are going to use.
  1. FirstName1,LastName1,Address1,City1,Country1
  2. FirstName2,LastName2,Address2,City2,Country2
  3. FirstName3,LastName3,Address3,City3,Country3
  4. FirstName4,LastName4,Address4,City4,Country4

Step 2 – Load Data: Table and File Format

Enter Database name, Table Name, if you need to create select “ Create new table” option or just enter the table name which is already there.

  1. CREATE TABLE customer
  2. (First_Name CHAR(50),
  3. Last_Name CHAR(50),
  4. Address CHAR(50),
  5. City CHAR(50),
  6. Country CHAR(25));

Step 3 – Character Delimiters

Here you can change the settings. In our care Field delimiter is comma and optional filed enclosure is double quotes.


Verify the setting and click next to continue with Step 4

Step 4 – Load Data: Load Method

There are various methods you can use to load the data and it depends on the need and how much data you are loading . We will be discussing these methods in details later. For simplicity we are going to use Conventional path method.

Step 5 – Load Data: Options

If you would like any records to written to the rejected file, select Bad file option and enter the path for the file to be generated. Keep in mind all of these paths related to the server not your local PC.

Step 6 – Load Data: Schedule

If you would like to schedule the job to run later date, you can use this step else click next

Step 7 – Review 


Verify the setting, submit job

Click on the Job link to see the status

Control file created

  1. LOAD DATA
  2. INFILE 'D:APPEXFORSYSORADATAEXFORSYSexample1.dat' "STR 'rn'"
  3. APPEND
  4. INTO TABLE customer
  5. FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  6. (
  7. FIRST_NAME CHAR,
  8. LAST_NAME CHAR,
  9. ADDRESS CHAR,
  10. CITY CHAR,
  11. COUNTRY CHAR
  12. )

If you see the following error during the job submission , Here are few things you will need to verify

Make sure service is running


You will need to run the following command

Logon as SYSMAN and run

  1. EXECUTE MGMT_USER.MAKE_EM_USER(‘username’);

username is the username that you are using to load the data.

After you complete the above, return back and continue the same step again where you have received the error


{mospagebreak title= SQL*Loader – Loading data from Command Prompt}

Login to SQL Plus to remove the data we have loaded from OEM.

Launch Command Prompt

  1. sqlldr username/password@dbname control=commandload.ctl


Here is the copy of the control file used in the example.
  1. LOAD DATA
  2. INFILE 'E:oraclesqlloadercommandload.dat' "STR 'rn'"
  3. APPEND
  4. INTO TABLE customer
  5. FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  6. (
  7.   FIRST_NAME CHAR,
  8.   LAST_NAME CHAR,
  9.   ADDRESS CHAR,
  10.   CITY CHAR,
  11.   COUNTRY CHAR
  12. )

Here is the logfile generated from the above demo.
  1. SQL*Loader: Release 11.2.0.1.0 - Production ON Sun Mar 6 13:19:31 2011
  2.  
  3. Copyright (c) 1982, 2009, Oracle AND/OR its affiliates.  ALL rights reserved.
  4.  
  5. Control File:   commandload.ctl
  6. DATA File:      E:oraclesqlloadercommandload.dat
  7.   File processing OPTION string: "STR 'rn'"
  8.   Bad File:     commandload.bad
  9.   Discard File:  NONE specified
  10.  
  11.  (Allow ALL discards)
  12.  
  13. NUMBER TO LOAD: ALL
  14. NUMBER TO skip: 0
  15. Errors allowed: 50
  16. Bind array:     64 ROWS, maximum OF 256000 bytes
  17. Continuation:    NONE specified
  18. Path used:      Conventional
  19.  
  20. TABLE CUSTOMER, loaded FROM every logical record.
  21. INSERT OPTION IN effect FOR this TABLE: APPEND
  22.  
  23.    COLUMN Name                  POSITION   Len  Term Encl Datatype
  24. ------------------------------ ---------- ----- ---- ---- ---------------------
  25. FIRST_NAME                          FIRST     *   ,  O(") CHARACTER            
  26. LAST_NAME                            NEXT     *   ,  O(") CHARACTER            
  27. ADDRESS                              NEXT     *   ,  O(") CHARACTER            
  28. CITY                                 NEXT     *   ,  O(") CHARACTER            
  29. COUNTRY                              NEXT     *   ,  O(") CHARACTER            
  30.  
  31.  
  32. Table CUSTOMER:
  33.   4 Rows successfully loaded.
  34.   0 Rows not loaded due to data errors.
  35.   0 Rows not loaded because all WHEN clauses were failed.
  36.   0 Rows not loaded because all fields were null.
  37.  
  38.  
  39. Space allocated for bind array:                  82560 bytes(64 rows)
  40. Read   buffer bytes: 1048576
  41.  
  42. Total logical records skipped:          0
  43. Total logical records read:             4
  44. Total logical records rejected:         0
  45. Total logical records discarded:        0
  46.  
  47. Run began on Sun Mar 06 13:19:31 2011
  48. Run ended on Sun Mar 06 13:19:31 2011
  49.  
  50. Elapsed time was:     00:00:00.05
  51. CPU time was:         00:00:00.03 )

[catlist id=177].

Related posts