Sql loader:-
Scenario:-
You have text
files or excel files or data files or any other flat files which you need to
upload in the database and do some manipulations. You need to upload data in a
table and use it for future. You will create index and primary key on it to
enhance the performance of the query. To
achieve this you can use sql loader oracle supplied tool to load data from
external files to oracle table.
What is sql loader?
Sql loader is
oracle supplied tool to load data from external files to oracle tables. To use
sql loader you would need to create a directory (since oracle does not interact
directly to windows files) which will work like a middle man to oracle and
external files.
Directory in oracle:-
Oracle
directory is an object pointing to an operating
system directory from the oracle server to read or write the data.
For example,
If you need to read data from oracle which is located on windows directory ‘c:
\test\exaple’ folder, while programming you cannot directly assign this path.
Instead, you can make oracle directory which will represent that path by a
specific name of the directory.
After
creating directory, grant privileges to the user to access the directory.
General syntax for creating directory
is:
CREATE OR
REPLACE DIRECTORY DIRECTORY_NAME AS ‘WINDOWS_PATH’.
Example :-
CREATE OR
REPLACE DIRECTORY TEST AS ’F: \CHANCHAL’;
After
creating directory successfully, grant the privileges to the required users:-
GRANT READ,
WRITE ON DIRECTORY TEST TO USER_NAME;
Now we have
created a directory and granted required privileges. So we are ready to use the
directory.
Let’s see an example how
to upload data from excel to oracle using sql * loader:-
A simple way
to upload data from Excel to Oracle is to use SQL* LOADER which is Oracle
supplied tool. It is highly preferred tool by programmers and DBA’s. It uploads
data very quickly and fast.
You cannot
upload data directly from Excel to Oracle. Therefore, save the excel sheet as “.csv” file. .csv stands for comma separated value.
While
uploading data from excel to oracle scenario would be like Excel -> Csv -> Oracle.
Suppose
you have a excel file called Test and you need to upload it in oracle. So you
will first convert it into the .csv file. So let’s rename Test excel file as
Test.csv file like shown below:-
And save the
file in F:\CHANCHAL Folder.
Now, In order
to upload data from excel to oracle the data which is coming from a file must
be in a table. So let’s create a table:-
Structure of
the table must be in the same sequence as in the excel file.
Now, in order
to load data from excel to oracle, we would require to create control file.
What is control file?
To load data
from .csv file to oracle we would require control file. Control file holds
all the information about execution of the sql*loader, Like Name of the csv
file, Name of the directory on which file it is present, Column list, Name of
the target table etc. The SQL*Loader control file is a repository that contains
the DDL instructions that you have created to control where SQL*Loader will
find the data to load, how SQL*Loader expects that data to be formatted, how
SQL*Loader will be configured.
How to create control file?
Creating
control file is very easy. Open a notepad and save it as “TEST.CTL” (In Double quotes). Like above we have save .csv file as
csv (comma delimited)(*.csv). The path where you need to save control file is
very important. You must know where you have to save the control file. In our
case we are saving it on ‘F: \chanchal’ windows directory which is also a
directory for oracle.
What control file will contain?
Generally
syntax for control file would be:-
LOAD
DATA
INFILE ‘WINDOWS_PATH’
TRUNCATE
/ REPLACE / APPEND
INTO
TABLE TABLE_NAME
FIELDS
TERMINATED BY “,”
OPTIONALLY ENCLOSED BY ‘” ’
TRAILING
NULLCOLS
(COLUMN_NAME,
COLUMN_NAME………)
Here:
Infile: - is a file from which, data will be
imported in the oracle table.
Truncate: - Is use for truncating the table
before inserting the records.
Replace: - All rows in the table are deleted and
the new data is loaded. This option requires DELETE privileges on the table.
Append: - If data already exists in the table,
SQL* Loader appends the new rows to it; if data doesn't already exist, then new
rows are simply loaded.
Trailing nullcols: - The
TRAILING NULLCOLS clause tells SQL* Loader to treat any
relatively positioned columns that are not present in the record as null
columns.
Fields Terminated by: - This clause tells SQL* LOADER that
fields will be terminated by.
Optionally Enclosed By: - This clause tell SQL* LOADER fields
can be optionally enclosed by.
In our case,
in order to upload data from Text.csv file to Test table, we would need to
create control file which is also called as Test.ctl. Write below code in the
control file and save it:-
LOAD
DATA
INFILE
F:\CHANCHAL\TEST.CSV
APPEND
INTO
TABLE TEST
FIELDS
TERMINATED BY “,”
OPTIONALLY
ENCLOSED BY ‘ ” ’
TRAILING
NULLCOLS
(EMPNO,
EMPTYPE, SALARY)
Now query the
test table before executing SQL* LOADER.
Now go to the
Command prompt. Command prompt will have path like ‘C:\DOCUMENTS AND
SETTINGS\USER_NAME’. User_Name will be the user by which you have login to the
server or machine.
For executing
the SQL* LOADER control file you must be on the path where your control file
resides. So we will change the path as:
Note: - In the blank space above, there would
be your user name.
Check
whether we have both the files TEST.CSV and TEST.CTL on F:\Chanchal Location as
we have specified same location for both the files.
Once you
reach the path F:\CHANCHAL in command prompt, execute the below statement:-
SQLLDR USERID=USER_NAME/PASSWORD@DATABASE_NAME
CONTROL=TEST.CTL
NOTE: IN THE BLANK SPACE ABOVE THERE WILL BE
YOUR DATABASE USER NAME / PASSWORD AND @DATABASE NAME.
As I have
only 6 records in .csv file, it clearly shows COMMIT POINT REACHED – LOGICAL
RECORD COUNT 6.
For
verification whether the data is inserted in the table or not we will query the
TEST table:
As you can
see we can find data in the table so it means we have performed all the steps
correctly.
While
Executing control file there are some files which automatically get create in
the location from where you are executing the control file or you can specify
the path for the same as follows:
2) Bad file
3) Discard file
·
Log File
The log file
contains information about the SQL*loader execution. It should be viewed after
each SQL*Loader job is complete.
Especially interesting is the summary information at the bottom of the
log, including CPU time and elapsed time.
The data below is a sample of the contents of the log file.
·
Bad File
A bad file is
created, when at least one record from the input file is rejected. The rejected records are placed in this
file. A record could be rejected for
many reasons, including a non-unique key or a required column being null.
·
Discard file
Discard file
contains information about the data which we have intentionally skipped while
uploading.
NOTE: - DON’T FORGET TO CHECK THE LOG FILE
AFTER EXECUTION OF CONTROL FILE.
No comments:
Post a Comment