Image Credit: Pixabay.com
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
What is the Main Cause of a Heart Attack? What is its Solution? A heart attack is the blockage of… Read More
In the vast economic arena, one term that often takes center stage, inciting extensive debates and discussions, is the "debt… Read More
De-Dollarization: The Changing Face of Global Finance The financial landscape is in a state of flux, with an intriguing economic… Read More
The curtains closed on a dramatic Bundesliga season with Bayern Munich standing tall once again, clinching their 11th straight title.… Read More
The Unfolding Story of Celine Dion's Health In recent news that has left fans across the globe stunned, iconic singer… Read More
As the echoes of the recent NBA season start to fade, the attention of enthusiasts is firmly glued to one… Read More