Monthly Archives: July 2009

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:

Continue reading

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:

Continue reading

SQL Tutorial

SQL Tutorial

SQL the Language


Today Structured Query Lannguage or simply SQL is the standard query language for all relational
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 is definitely the most commonly used language in any oracle installtion. Be it through plain
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


Oracle is a relational database and factually speaking it is the most popular and most widely
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.

Oracle database server is nothing but a combination of different services which works together
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


To be able to run SQL you will need to be able login to some Oracle database located in your local
machine or in a remote machine where you are able to connect.

To be able to run the queries given in this tutorial you will need to create two tables named
Students and Majors. Then you will need to Insert data into these two tables using the data files
and SQL*Loader scripts.


Loading