SQL - LOADER & EXTERNAL TABLE

SQL -LOADER


SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database. SQL*Loader is an Oracle-supplied utility that allows you to load data from a flat file into one or more database tables.

To execute the SQL*Load tool, you need at least three files:

  • The input data file stores delimited or raw data
  • The parameter file stores the location of the input/output files
  • The control file contains the specification on how data is loaded

Download a sample excel sheet(here the used excel sheet has 1000 rows) for uploading the data. convert the excel into csv file an d remove the header. Move the sheet into the shared folder and then copy the same to the ORACLE HOME path
Step 2: Connect to the user and create a table with columns matching the text file
Step 3: create control file with .ctl extension
Step 4: Use the sqlldr query 

	

	The values has not been loaded into the table.
Step 5: check for the log file. This is because of the data type error for the joindate column. Alter and set datatype as “varchar”
Step 6: load the data again using sqlldr query
Step 7: check for the count in the table


SQL-EXTERNAL TABLE


An Oracle Sequence is a database object, just like a table or view, that represents a sequence of integers that can be used by any table or view. A Sequence’s values can be accessed using the NEXTVAL, and CURRVAL pseudo-columns. A Sequence can be ascending or descending.

External table is a table but it does not store any data inside the database where the data will be residing in a form of external file. Data will be stored in an external file and it will be extract into the table while displaying.

External table will be using oracle loader for loading the data from data file into the table.,Download a sample excel sheet(here the used excel sheet has 1000 rows) for uploading the data. convert the excel into csv file an d remove the header. Move the sheet into the shared folder and then copy the same to the ORACLE HOME path
Step 1:

Here the csv file is copied into a directory “utl” . A directory is to be created in DB level which is similar to the directory in os level where the csv file is maintained and grant access on directory to the desired user.

	

	create table tab2(id int, firstname varchar(20), lastname varchar(20),
	gender varchar(20), country	varchar(20),age int, joindate varchar(20))
	organization external(type oracle_loader default directory fdir access
	parameters(records delimited by newline fields terminated by ','(id,
	firstname,lastname,gender,country,age,joindate)) location('sample.csv'))
	parallel 5 reject limit	unlimited;
Step 2: check for values in table
Step 3: a log file would be created which show the progress
Step 4: Now let us add one row in the csv file that we has uploaded and check for the count in the table 

	1234, Krishna Kithari, Female, India, 31, 18/05/2022

	SQL>  Select Count(*) from tab2;

	Count(*)
	----------
	1001
Step 5: we can see that the values has been added.

(SQL - DBMS Metadata)