Oracle 9i Utilities Page - 3

Points to Ponder

It is possible to import dump created using an earlier (version 8.1.7 utility) version can be imported using the later version utility (Version 9.0.1 utility). We should not use later version utilities to export data from earlier database versions. But an earlier utility can be used to export later versions of database. For example you can export data from Oracle9i using 8.1.7 utility and can import that exported file into oracle 8i database using import utility 8.1.7 .

SQL * Loader

It is an Oracle utility used for moving bulk data from external files into the Oracle database. Data from any text file can be loaded into database. SQL*Loader reads data from an external file and loads data into an existing table while the Oracle database is open.

SQL*Loader Datatypes: SQL*Loader uses the following datatypes.

1. CHAR
2. DECIMAL
3. INTEGER

SQL*Loader require two input files a control file and another data file. The control file is a text file details the task to be carried out by the SQL*Loader. It tells the SQL*Loader where data is available how to parse and interpret it also where to insert it. The data file contains the data to be loaded.
A control file may be vaguely divided into 3 sections

1.First section contain INFILE clause in this we specify where input data is located.
2. The second section have INTO TABLE block that details the table and column names into which data is stored.
3. Third section is optional, If present it contain input data.

SQL* Loader assumes that data in data file is organized as records. Based on the record type data files could be categorized into

Fixed record files: All the records are of same(fixed) length
Variable record files: Records are of varying length and Streamed record files.

Note: If the data is specified in the control file, then we write INFILE * and the data is treated as streamed record format and the records separated by default record terminator.

Examples

The following example explains how to load data from file with fixed record format.

Creating Control File

Using DOS editor or notepad editor you can create data file as shown below.

LOAD DATA
INFILE ‘mydata1.dat ‘ “fix 18”
fields terminated by ‘,’
(sno ,sname , course )

Save this file under the name myctrl1.ctl into the current directory.

Creating Data File

In the DOS editor or Notepad you can create data file as shown below.

1001,RAJAN, ASP,
1002,KISHAN, J2EE,
1003,PRABHU, JSP,
1004,PRANAY, ORACLE,
1005,JOHN, APPS,
1006,MARTIN, ORACLE

Save the above content as file with the name “mydata1.dat”
The following figure explains how data can be loaded into a table. The data loaded here is of fixed-length records

To cross check whether loading was proper we execute simple SELECT statement as given below.


For More examples follow the links below.

http://www.pafumi.net/sql_loader.htm
http://www.oracleutilities.com/OSUtil/sqlldr.html