SQL Loader and External Table

SQL Loader:

• Oracle SQL Loader is a utility provided by Oracle Corporation for loading data from external files into Oracle Database tables.
• It is a command-line tool that uses control files to define the format of the data file and the target database table.
• It is a powerful and efficient tool designed to handle large volumes of data loading in a fast.
• SQL Loader is particularly useful when you need to import data from flat files, such as CSV (Comma-Separated Values) or other delimited text files, into Oracle Database tables.
  

 

Log File: The log file provides a detailed record of the SQL Loader session, offering information on the progress, statistics, and any errors encountered during the loading process.

Bad File: The bad file is used to store records that SQL Loader determines cannot be loaded into the target table due to formatting errors, data type mismatches, or other issues. These records are considered "bad" and are not loaded into the database.
Discard File: Similar to the bad file, the discard file stores records that were not loaded into the database. However, records in the discard file are typically rejected due to conditions specified in the control file, such as SKIP or WHEN clauses.
Modes:
• Insert(Load Empty Table)
• Append (Load Existing Table)
• Replace (Delete records and then load, user must have DELETE permission)

Data File: filename: employee.txt
John Doe, 28, New York, Software Engineer, 80000
Alice Smith, 35, Los Angeles, Data Scientist, 90000
Bob Johnson, 42, Chicago, Project Manager, 75000
Emily Davis, 30, San Francisco, UX Designer, 85000
Michael Williams, 25, Houston, Sales Representative, 70000
Olivia Brown, 31, Seattle, Marketing Specialist, 82000
Daniel Lee, 38, Miami, Financial Analyst, 95000
Sophia Miller, 27, Boston, Graphic Designer, 78000
Matthew Anderson, 33, Dallas, HR Manager, 88000
Emma Wilson, 29, Atlanta, Operations Coordinator, 76000

Control File: emp.ctl
load data
infile '/home/oracle/employee.txt'
insert into table employee
fields terminated by "," optionally enclosed by " "
(name, age, city, occupation, salary)

load data
infile '/home/oracle/employee.txt'
append into table employee
fields terminated by ","
(name, age, city, occupation, salary)

load data
infile '/home/oracle/employee.txt'
infile '/home/oracle/employee_new.txt'
append into table employee
fields terminated by ","
(name, age, city, occupation, salary)

LOAD DATA
INFILE '/home/oracle/employee.txt'
REPLACE INTO TABLE Employee
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(name, age, city, occupation, salary)

Create a table:

CREATE TABLE Employee (
name VARCHAR2(100),
age NUMBER,
city VARCHAR2(50),
occupation VARCHAR2(100),
salary NUMBER
);

Run SQl Loader Command:
sqlldr scott/tiger control=emp.ctl

Use Parameter File: pmfile.par
control=email.ctl
log=email.log
bad=email.bad
data=email.dat
direct=true

Run SQl Loader Command:
sqlldr user_name/password parfile=pmfile.par

External Table:

External table is a database object that allows you to access and query data stored outside the Oracle database, such as in flat files or other external sources. External tables are read-only, and it does not describe any data that is stored in the database.

create a directory in the file system and put your CSV file inside:
mkdir -p /db/testdir
CSV File: emp.csv
John Doe, 28, New York, Software Engineer, 80000
Alice Smith, 35, Los Angeles, Data Scientist, 90000
Bob Johnson, 42, Chicago, Project Manager, 75000
Emily Davis, 30, San Francisco, UX Designer, 85000
Michael Williams, 25, Houston, Sales Representative, 70000
Olivia Brown, 31, Seattle, Marketing Specialist, 82000
Daniel Lee, 38, Miami, Financial Analyst, 95000
Sophia Miller, 27, Boston, Graphic Designer, 78000
Matthew Anderson, 33, Dallas, HR Manager, 88000
Emma Wilson, 29, Atlanta, Operations Coordinator, 76000

Create Directory (extdir) and give permission:

CREATE OR REPLACE DIRECTORY extdir AS '/db/testdir';

GRANT READ,WRITE ON DIRECTORY extdir TO user_name;

check external directory List:

select * from dba_directories where directory_name='extdir';

external table command: method 1

CREATE TABLE ext_table (
name VARCHAR2(100),
age NUMBER,
city VARCHAR2(50),
occupation VARCHAR2(100),
salary NUMBER
)
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY extdir
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
)
LOCATION (‘emp.csv’)
)
REJECT LIMIT UNLIMITED;

external table command: method 2:

CREATE TABLE ext_table (
name VARCHAR2(100),
age NUMBER,
city VARCHAR2(50),
occupation VARCHAR2(100),
salary NUMBER
)
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY extdir
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
(name, age, city, occupation, salary)
)
LOCATION ('emp.csv')
)
REJECT LIMIT UNLIMITED;