SQL*Loader allows you to load data from an external file into a table within the database. It can parse many delimited file formats like CSV, tab-delimited, and pipe-delimited.

SQL*Loader provides the subsequent methods to load data:

Conventional path loads – construct INSERT statements from the contents of the input datafile supported the predefined specification and execute the inserts.
Direct path loads – creates data blocks in Oracle database block format from the data file and directly write the data block to the database. this manner is far faster than the traditional path but subject to some restrictions.
External table loads – create an external table for the data stored within the datafile and execute INSERT statements to insert the data from the datafile into the target table. The external table loads support parallel loading if datafile is large enough.

To execute the SQL*Load tool, you would like a minimum of three files:

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

After that, you execute the command sqlldr from the instruction on Windows or Terminal on GNU/Linux:

>sqlldr parfile=parameter_file.par

The following picture illustrates the SQL*Loader process:

Let’s take the instance of using the SQL*Load tool.

SQL*Loader example

We will load email data CSV file format into the emails table within the database.

Prepare the input files

the subsequent is that the content of the e-mail .dat file:
1,john.doe@example.com
2,jane.doe@example.com

3,peter.doe@example.com

The contents of the control file (email.ctl) is as follows:
load data into table emails
insert
fields terminated by “,”
(
email_id,
email

)

In the control file:

The load data into table emails insert instruct the SQL*Loader to load data into the emails table using the INSERT statement.
The fields terminated by “,” (email_id,email) specifies that every row within the file has two columns email_id and email separated by a comma (,).

Here is that the content of the parameter file (email.par):
ot@pdborc/Abcd1234
control=email.ctl
log=email.log
bad=email.bad
data=email.dat

direct=true

In this parameter file, we specify the user (true instructs the SQL*Loader to use the direct path load method.

Note that there’s no space between the parameter and value, for example:

control=email.ctrl

After having three files, you’ll place it during a directory e.g., C:\loader.

Here is that the link to download the three files during a zip file format:

Download the data , control and parameter files

Load data from a file into the table

First, create a replacement table to store the e-mail data from the input datafile:

CREATE TABLE emails(

email_id NUMBER PRIMARY KEY,

email VARCHAR2(150) NOT NULL

);

Second, launch the SQL*Loader program from the directory that you simply store the files using the sqlldr command:

C:\loader> sqlldr parfile=dept_loader.par

Here is that the output:

Path used: Direct

Load completed – logical record count 3.

Table EMAILS:

3 Rows successfully loaded.

Check the log file:

email.log

for more datarmation about the load.

Third, log in to the Oracle and verify the contents of the emails table:

SELECT * FROM emails;

Here is that the result set:

Finally, review the log file:

SQL*Loader: Release 12.1.0.2.0 – Production on Tue Jul 23 08:04:46 2019

Control File: email.ctl

Data File: email.dat

Bad File: email.bad

Discard File: none specified

(Allow all discards)

Number to load: ALL

Number to skip: 0

Errors allowed: 50

Continuation: none specified

Path used: Direct

Table EMAILS, loaded from every logical record.

Insert option in effect for this table: INSERT

Column Name Position Len Term Encl Datatype

—————————— ———- —– —- —- ———————

EMAIL_ID FIRST * , CHARACTER

EMAIL NEXT * , CHARACTER

The following index(es) on table EMAILS were processed:

index OT.SYS_C0010446 loaded successfully with 3 keys

Table EMAILS:

3 Rows successfully loaded.

0 Rows not loaded thanks to data errors.

0 Rows not loaded because all WHEN clauses were failed.

0 Rows not loaded because all fields were null.

Bind array size not utilized in direct path.

Column array rows : 5000

Stream buffer bytes: 256000

Read buffer bytes: 1048576

Total logical records skipped: 0

Total logical records read: 3

Total logical records rejected: 0

Total logical records discarded: 0

Total stream buffers loaded by SQL*Loader main thread: 2

Total stream buffers loaded by SQL*Loader load thread: 0

Run began on Tue Jul 23 08:04:46 2019

Run ended on Tue Jul 23 08:04:48 2019

Elapsed time was: 00:00:01.47

CPU time was: 00:00:00.07

So, this brings us to the end of blog. This Tecklearn ‘Oracle SQL Star Loader’ blog helps you with commonly asked questions if you are looking out for a job in Oracle DB. If you wish to learn Oracle DB and build a career in Database domain, then check out our interactive, Oracle 12c DBA Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details

https://www.tecklearn.com/course/oracle-12c-dba-course/

Oracle 12c DBA Course Training

About the Course

Oracle Database Administration training will help you master the Oracle DBA. As part of the training, you will master the Oracle Database architecture, the database processes, memory structures, schema objects, security, data recovery and backup through hands-on projects and case studies. Oracle DBA Training helps students gain the skills and knowledge required to install, configure, and administer Oracle databases. Besides, this training also develops the full skill set required to become a successful Oracle Database Administrator.

Why Should you take Oracle DBA Training?

The Average salary of an Oracle DBA is $108,837 per annum – GlassDoor.com
According to Gartner, Oracle is the market leader in the Relational Database with 48.35% market share globally.
IBM, TCS, Tech Mahindra, Oracle, Wipro & other MNCs worldwide use Pl-SQL for their database deployments.

What you will Learn in this Course?

Introduction to Oracle Database

What is the Oracle Database
Pre-requisites for oracle database
Oracle database 12c installation
Multi-tenant architecture

Exploring the Oracle 12c Database Architecture

Explain the Memory Structures
Describe the Process Structures
Overview of Storage Structures

Preparing the Database Environment

Identify the tools for Administering an Oracle Database
Plan an Oracle Database Installation
Install the Oracle Software by using Oracle Universal Installer (OUI)
Create a Database by using the Database Configuration Assistant (DBCA)

Managing the Oracle Instance

Setting database initialization parameters
Describe the stages of database startup and shutdown

Database Storage Structures and Schema Objects

Managing Database Storage Structures
Overview of table space and data files
Overview of Primary Components
Identify the importance of checkpoints, redo log files, and archived log files
Configure ARHIVELOG mode

Database Startup & User Requests and Oracle Network Environment

Describe the stages of database startup and shutdown
Using alert log and trace files
Configure and Manage the Oracle Network
pFile and spFile
Tablespace Management
Storage and Relationship Structure

Oracle Backup & Recovery

Redo log File Management
Performing Database Recovery
Overview of Data Recovery Advisor
Use Data Recovery Advisor to Perform recovery (Control file, Redo log file and Data file)

Deep Dive into Oracle Recovery Manager (RMAN)

Using the RMAN Recovery Catalog
Identify situations that require RMAN recovery catalog
Create and configure a recovery catalog
Synchronize the recovery catalog
Create and Use RMAN stored scripts
Back up the recovery catalog
Create and use a virtual private catalog

Configuring Backup Specifications

Using RMAN to Create Backups
Create image file backups
Create a whole database backup
Enable fast incremental backup
Using RMAN to Perform Recovery
Perform complete recovery from a critical or noncritical data file loss using RMAN
Perform incomplete recovery using RMAN

Data Movement and Oracle Data Pump

Moving Data
Describe and use methods to use move data (Directory objects, SQL* Loader, External tables)
Explain the general architecture of Oracle Data Pump

Data Dictionary & Dynamic Performance Tables

Oracle Data Dictionary contains information related to database privileges
Data dictionary views
Media failure
Flashback CDB

Database Tuning

What is database tuning
How to tune the database
Tuning the data dictionary

445