Home >> FAQs/Tutorials >> Oracle Tutorials

Oracle Tutorials - Load Data with SQL*Loader

By: FYIcenter.com

(Continued from previous topic...)

How To Load Data with SQL*Loader?

Let's say you have a table defined as:

CREATE TABLE student (id NUMBER(5) PRIMARY KEY,
  first_name VARCHAR(80) NOT NULL,
  last_name VARCHAR(80) NOT NULL,
  birth_date DATE NOT NULL,
  social_number VARCHAR(80) UNIQUE NOT NULL);

There is an input data file stored at \oraclexe\text\student.txt with the following records:

1,Steven,King,17-JUN-77,515.123.4567
2,Neena,Kochhar,21-SEP-79,515.123.4568
3,Lex,De Haan,13-JAN-83,515.123.4569
4,Alexander,Hunold,03-JAN-80,590.423.4567
5,Bruce,Ernst,21-MAY-81,590.423.4568
6,David,Austin,25-JUN-87,590.423.4569
7,Valli,Pataballa,05-FEB-88,590.423.4560
8,Diana,Lorentz,07-FEB-89,590.423.5567
9,Nancy,Greenberg,17-AUG-84,515.124.4569
10,Daniel,Faviet,16-AUG-84,515.124.4169

You can create a control file at \oraclexe\test\student.ctl as:

LOAD DATA
  APPEND INTO TABLE STUDENT
  FIELDS TERMINATED BY ','
  (id, first_name, last_name, birth_date, social_number)

When you are ready to load data, you can run the SQL*Loader with the "sqlldr" command:

>cd \oraclexe\app\oracle\product\10.2.0\server\BIN
>sqlldr userid=hr/fyicenter, 
  control=\oraclexe\test\student.ctl, 
  data=\oraclexe\test\student.txt, 
  log=\oraclexe\test\student.log

SQL*Loader: Release 10.2.0.1.0 -
Commit point reached - logical record count 10

To see details of the loading process, you should check the log file \oraclexe\test\student.log.

(Continued on next topic...)

  1. What Is the Simplest Tool to Run Commands on Oracle Servers?
  2. What Is the Quickest Way to Export a Table to a Flat File?
  3. How To Export Data with a Field Delimiter?
  4. What Is SQL*Loader?
  5. What Is a SQL*Loader Control File?
  6. How To Load Data with SQL*Loader?
  7. What Is an External Table?
  8. How To Load Data through External Tables?
  9. What Are the Restrictions on External Table Columns?
  10. What Is a Directory Object?
  11. How To Define an External Table in a Text File?
  12. How To Run Queries on External Tables?
  13. How To Load Data from External Tables to Regular Tables?
  14. What Is the Data Pump Export Utility?
  15. What Is the Data Pump Import Utility?
  16. How To Invoke the Data Pump Export Utility?
  17. How To Invoke the Data Pump Import Utility?
  18. What Are Data Pump Export and Import Modes?
  19. How To Estimate Disk Space Needed for an Export Job?
  20. How To Do a Full Database Export?
  21. Where Is the Export Dump File Located?
  22. How To Export Your Own Schema?
  23. How To Export Several Tables Together?
  24. What Happens If the Imported Table Already Exists?
  25. How To Import One Table Back from a Dump File?
  26. What Are the Original Export and Import Utilities?
  27. How To Invoke Original Export/Import Utilities?

Oracle Tutorials:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...