Oracle DBA FAQ - Loading and Exporting Data

Home >> FAQs/Tutorials >> Oracle DBA FAQ

By: FYIcenter.com

Part: 1 2 3 4 5 6 7

(Continued from previous part...)

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.

What Is an External Table?

An external table is a table defined in the database with data stored outside the database. Data of an external table is stored in files on the operating systems. Accessing data of external tables are done through data access drivers. Currently, Oracle supports two data access drivers: ORACLE_LOADER and ORACLE_DATAPUMP.

External tables can be used to load data from external files into database, or unload data from database to external files.

How To Load Data through External Tables?

If you have data stored in external files, you can load it to database through an external table by the steps below:

Create an external table with columns matching data fields in the external file.
Create a regular table with the same columns.
Run an INSERT INTO ... SELECT statement to load data from the external file to the regular table through the external table.

What Are the Restrictions on External Table Columns?

When creating external table columns, you need to watch out some restrictions:

"PRIMARY KEY" is not allowed.
"NOT NULL" is not allowed.
"DEFAULT value" is not allowed.

What Is a Directory Object?

A directory object is a logical alias for a physical directory path name on the operating system. Directory objects can be created, dropped, and granted access permissions to different users. The following tutorial exercise shows you some good examples:

>sqlplus /nolog

SQL> connect SYSTEM/fyicenter

SQL> CREATE DIRECTORY test_dir AS '/oraclexe/test';
Directory created.

SQL> GRANT READ ON DIRECTORY test_dir TO hr;
Grant succeeded.

SQL> GRANT WRITE ON DIRECTORY test_dir TO hr;
Grant succeeded.

SQL> CREATE DIRECTORY temp_dir AS '/oraclexe/temp';
Directory created.

SQL> DROP DIRECTORY temp_dir;
Directory dropped.

(Continued on next part...)

Part: 1 2 3 4 5 6 7

More...