Exforsys.com
 
Home Tutorials Oracle 9i
 

Tutorial 16: Oracle 9i: Oracle Utilities

 

Tutorial 16: Oracle 9i: Oracle Utilities - Page 3

Page 3 of 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
http://kia.etel.ru/lib/oradb/server.805/a58244/ch04.htm
http://www-rohan.sdsu.edu/doc/oracle/server803/A54652_01/part2.htm#138




First Page: Tutorial 16: Oracle 9i: Oracle Utilities


Read Next: Oracle 9i: SQL, PLSQL, and SQL *Plus - Introduction

 

 

Comments


pappu singh said:

  The section is very nice and easily understandable .
September 13, 2006, 4:27 am

PRAVEEN1 said:

  THE INFORMATION PROVIDED IS VERY USEFUL. THANKS A LOT
THERE IS ONE POINT LEFT WHILE CREATING CONTROL FILE IN NOTEPAD, YOU FORGOT TO MENTION INTO TABLE BLOCK.
May 7, 2007, 12:54 am

Salman Hunzai said:

  Thank you very much for this very informative sorce... It was very helpful indeed..
August 23, 2007, 3:11 pm

vijay said:

  great tutorial thanks a lot
November 7, 2008, 8:53 am

Kasi, Vijayakumar said:

  The section is easily understandable. Thanks in anticipation.
December 3, 2008, 3:54 am

rvnagarajan said:

  thanks a lot, very useful
January 2, 2009, 3:42 am

GM said:

  its explained step by step with screenshot, any layman can easily understand thanks
January 20, 2009, 10:25 am

Post Your Comment:

Members Please Login
Your Name:*
e-mail ID:(required for notification)*
Image Verification: 
 
 Subscribe    

Sponsored Links

 

Subscribe via RSS


Get Daily Updates via Subscribe to Exforsys Free Training via email


Get Latest Free Training Updates delivered directly to your Inbox...

Enter your email address:


 

Subscribe to Exforsys Free Training via RSS
 

 
Partners -  Privacy and Legal Policy -  Site News -  Contact   Sitemap  

Copyright © 2000 - 2009 exforsys.com. All Rights Reserved

Page copy protected against web site content infringement by Copyscape