SQL*Loader with example
SQL*LOADER WITH EXAMPLE
========================
SQL*Loader is an Oracle supplied utility used to load data in Oracle tables easily with significantly
high performance. This utility is installed by default when you install Oracle Server software. It is
also installed when you install Administrative version of Oracle Client.
It is commonly used to load data from plain flat files (text files/plain data files) into database tables.
In it’s simplest form it requires a Control File, a Data File, the SQL*Loader executable and the Target
Table(s).
Control File: As the name suggests this file controls the loading of data in an Oracle table. Data type,
data length, data file separator (called field terminator), target table etc are specified in this file.
Data File: This is a plain text file separated by usually comma (“,”) or any other Oracle understandable
characters.
SQL*Loader: This is the sqlldr.exe file supplied by Oracle.
Target Tables(s): Data can be loaded into a single table or multiple tables.
Here is a demonstration of how SQL*Loader is used.
Suppose I have an Oracle table of structure
create table table1
(empno number,
fname varchar2(20),
lname varchar2(20),
age number
);
I want to load data from a text file named mydatafile.txt which has the following data
1000,David,Selby,40
1001,Thomas,Train,31
1002,Nick,Junior,22
1003,Samantha,Dale,24
1004,Roman,Catfield,39
1005,Punnet,Gupta
1006,Dummy, Suname name does not exists, 10
To load the content of file mydatafile.txt into table table1 I’ll need to have a control file
which will look somewhat like this:
UTL_FILE – How to read data from a file
UTL_FILE
========
This package is supplied by Oracle and is used to read an operating system file or write to
an operating system file.
So if you are thinking of reading and processing data fro man operating system file or writing
to a file to generate a report or something like that then you will definitely need to use this
handy Oracle supplied package.
ACCESSING OPERATING SYSTEM FILE STRUCTURE
=========================================
To access an operating system directory structure for read/write the directory need to be defined first.
In earlier versions of Oracle (i.e. Oracle 8.0.4) the init.ora parameter UTL_FILE_DIR is used define
the directories to be used in UTL_FILE. While using that parameter it is necessary to re-start Oracle
database so that your Oracle instance understands this parameter.
The parameter is defined as:
UTL_FILE_DIR=’/home/oracle/dave/’ — for single directory
or
UTL_FILE_DIR=’/home/oracle/dave/’, ‘/home/oracle/tom/’, ‘/home/oracle/jim/’ –for multiple directories
Or a command line facility is also available such as
alter system set utl_file_dir=’/home/oracle/dave/’ scope=spfile
However later versions of Oracle introduced CREATE DIRECTORY command where you can simply create a
directory and use it in UTL_FILE. For example to create such a directory run command which will look
like:
CREATE OR REPLACE DIRECTORY DIR_MYTEMP AS ‘/home/oracle/Dave/’;
Note: To be able to create a directory you will need CREATE ANY DIRECTORY privilege.
Then you can use the DIR_MYTEMP directory in your code with UTL_FILE:
SQL Tutorial
SQL Tutorial
SQL the Language
databases such as Oracle, Microsoft SQL Server, DB2, Sybase, MySQL and Microsoft Access. Any
modern relational database that you may be using, it is bound to support SQL as it’s standard
data qyery and manupulating language. It may differ slightly depending on the kind of Database
it needs to access (i.e. Oracle and SQL Server release their own SQL) but they all follow the
industry accepted standards cimpiled by ANSI and ISO.
SQL statements or through Oracle’s PL/SQL (Programming Language extention of SQL) or embedded
in C, C++, Java. Whatever programming language you use, as soon as you need to manipulate
or process relational data you will need some form of SQL to rectrive and manipulate the data.
The aim of this tutorial is to give a hands-on knowledge of this powerfull but still so simple
language to the beginers as well as to the experienced programmers who likes to revise their
understanding periodically. The totorial includes lots of example which are used by developers
and managers alike for their day to day functions in developing and supporting commercial
databases.
A few words on Oracle
installed for commercial purpose databases for medium to large businesses. I am not saying that
it can not be used for small business but I think there are much cheaper alternative in the
market for that purpose. But when it comes to scale and performance and ease of maintenance
as well as trained man-power I think Oracle is in the fore front among the league.
as a single database management system. Oracle is a relational database management system
(RDBMS) because it stores data in relational model which make it faster to query and maniputale.
Oracle stores data in an object called Table. There are different types of objects in oracle
such as
Table
Index
Contraint
Procedure
Function
Package
Trigger
Sequence
All these objects are logically stored in Oracle logical structures such as Tables, Segments, Extents
and Blobks.
Physically all these logical data are stored in o/s files called Oracle Data files. These files are
stored in o/s level in some directory which varies in different implementations.
Basic Requirement
machine or in a remote machine where you are able to connect.
Students and Majors. Then you will need to Insert data into these two tables using the data files
and SQL*Loader scripts.
Recent Comments