A simple example of using Direct Path loading with Oracle Sql* Loader

For this example, we will do the following:

1)Create a Table :
create table schools_admissions(schools_adm_id number not null primary key,
school_name varchar2(150) not null ,
school_category varchar2(3) default ‘A’ not null,
school_adm_last_date date not null
) ;

Execution of this:
******************

SQL> create table schools_admissions(schools_adm_id number not null primary key,
2  school_name varchar2(150) not null ,
3  school_category varchar2(3) default ‘A’ not null,
4  school_adm_last_date date not null
5  ) ;

Table created.

Sample Data in csv file(this is the simplest-comma separated but can be tab separated, vertical pipe (|) etc. too =>

File named: ptrikha12_data.csv
—————————————————————
schools_adm_id,school_name,school_category,school_adm_last_date
—————————————————————
11,’Summerfield Higher School ‘,’A+’,08-SEP-2016
5,’Gyandeep Intl. School’,’A+’,02-SEP-2016
7,’Arvind Kumarasamy Sr. Sec. School’,’B’,27-AUG-2016

Then, we create a control file to load the data with the direct path. Why we use Direct Path is because Direct path loads can be significantly faster than conventional path loads. Direct path loads achieve this performance gain by eliminating much of the Oracle database overhead by writing directly to the database files.

Controlfile: ptrikha12_control.ctl:

OPTIONS (DIRECT=TRUE)
LOAD DATA
INFILE ‘D:\ora12c_works\data\ptrikha12_data.csv’
INTO TABLE schools_admissions
TRUNCATE
FIELDS CSV WITH EMBEDDED
DATE FORMAT “DD-MON-YYYY HH24:MI:SS”
(schools_adm_id ,
school_name ,
school_category,
school_adm_last_date DATE)

Then, we create a Sql* Loader file with the Sql* Loader command in a file: Execute_Sqlldr_orcl12.bat

sqlldr userid= c##ptrikha_12/ptrikha_12@orcl12 control= ‘D:\ora12c_works\control\ptrikha12_control.ctl’
log = D:\ora12c_works\log\ptrikha_12_load.log

Next, we execute this batch file, either through Windows or other O/S scheduling or directly. We expect to see 3 records loaded.

Yet here is what happens(as checked from the log file):

______________________________________________________________

SQL*Loader: Release 12.1.0.2.0 – Production on Wed Aug 17 16:02:50 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Control File:   D:\ora12c_works\control\ptrikha12_control.ctl
Data File:      D:\ora12c_works\data\ptrikha12_data.csv
Bad File:     D:\ora12c_works\control\ptrikha12_data.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 SCHOOLS_ADMISSIONS, loaded from every logical record.
Insert option in effect for this table: TRUNCATE

Column Name                  Position   Len  Term Encl Datatype
—————————— ———- —– —- —- ———————
SCHOOLS_ADM_ID                      FIRST     *   ,  O(“) CHARACTER
SCHOOL_NAME                          NEXT     *   ,  O(“) CHARACTER
SCHOOL_CATEGORY                      NEXT     *   ,  O(“) CHARACTER
SCHOOL_ADM_LAST_DATE                 NEXT     *   ,  O(“) DATE DD-MON-YYYY

Record 1: Rejected – Error on table SCHOOLS_ADMISSIONS, column SCHOOL_CATEGORY.
ORA-12899: value too large for column SCHOOL_CATEGORY (actual: 4, maximum: 3)

Record 2: Rejected – Error on table SCHOOLS_ADMISSIONS, column SCHOOL_CATEGORY.
ORA-12899: value too large for column SCHOOL_CATEGORY (actual: 4, maximum: 3)

The following index(es) on table SCHOOLS_ADMISSIONS were processed:
index C##PTRIKHA_12.SYS_C0010001 loaded successfully with 1 keys

Table SCHOOLS_ADMISSIONS:
1 Row successfully loaded.
2 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

Date cache:
Max Size:      1000
Entries :         1
Hits    :         0
Misses  :         0

Bind array size not used 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:         2
Total logical records discarded:        0
Total stream buffers loaded by SQL*Loader main thread:        1
Total stream buffers loaded by SQL*Loader load thread:        0

Run began on Wed Aug 17 16:02:50 2016
Run ended on Wed Aug 17 16:03:03 2016

—

So, we alter the table : SCHOOLS_ADMISSIONS and change the column school_category to a column size of say 10

ALTER TABLE schools_admissions MODIFY school_category VARCHAR2(10);

SQL> ALTER TABLE schools_admissions MODIFY school_category VARCHAR2(10);

Table altered.

SQL>

Now, we execute the Sqlldr again:

D:\ora12c_works\Sqlldr_scripts>sqlldr userid= c##ptrikha_12/ptrikha_12@orcl12 control= ‘D:\ora12c_works\control\ptrikha12_control.ctl’ log = D:\ora12c
_works\log\ptrikha_12_load.log

SQL*Loader: Release 12.1.0.2.0 – Production on Wed Aug 17 17:41:38 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Path used:      Direct

Load completed – logical record count 3.

Table SCHOOLS_ADMISSIONS:
3 Rows successfully loaded.

Check the log file:
D:\ora12c_works\log\ptrikha_12_load.log
for more information about the load.

D:\ora12c_works\Sqlldr_scripts>

Now, let us see the log file and the table:

SQL> select schools_adm_id || ‘,’||school_name || ‘,’|| school_category || ‘,’ ||school_adm_last_date
2  from schools_admissions ;

SCHOOLS_ADM_ID||’,’||SCHOOL_NAME||’,’||SCHOOL_CATEGORY||’,’||SCHOOL_ADM_LAST_DAT
——————————————————————————–
11,’Summerfield Higher School ‘,’A+’,08-SEP-16
5,’Gyandeep Intl. School’,’A+’,02-SEP-16
7,’Arvind Kumarasamy Sr. Sec. School’,’B’,27-AUG-16

SQL>
———————-

Source(s):
___________

Oracle site documentation=>
 
https://docs.oracle.com/cd/A57673_01/DOC/server/doc/SUT73/ch8.htm
and
Oracle-base site:

https://oracle-base.com/articles/12c/sql-loader-enhancements-12cr1#sql-loader-command-line-changes