So a request comes in from a colleague…
”Hi Connor. I’ve created this new table, and I need to populate it with some data. I’ve got it in Excel – can you help me load it”
Now, at this point, my heart normally sinks, because whilst the Oracle tools to load flat file data are comprehensive, I know that I now have to
- go find out the table definition, column names and data types
- write up a SQL Loader control file, or
- write up an external table definition script
- repeat this 10 times until I get the syntax right
all of which is going to be a drain on my time…
What I really would like, is to do this:
1) Check that my table is ready for loading
SQL> desc EMP_TAB
Name Null? Type
----------------------------------------- -------- -------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL> select * from EMP_TAB;
no rows selected
2) Have a quick squizz at the data, make sure it looks sensible
7369,SMITH,CLERK,7902,17-DEC-80,800,,20
7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30
7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30
7566,JONES,MANAGER,7839,02-APR-81,2975,,20
7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,1400,30
7698,BLAKE,MANAGER,7839,01-MAY-81,2850,,30
7782,CLARK,MANAGER,7839,09-JUN-81,2450,,10
7788,SCOTT,ANALYST,7566,09-DEC-82,3000,,20
7839,KING,PRESIDENT,,17-NOV-81,5000,,10
7844,TURNER,SALESMAN,7698,08-SEP-81,1500,0,30
7876,ADAMS,CLERK,7788,12-JAN-83,1100,,20
7900,JAMES,CLERK,7698,03-DEC-81,950,,30
7902,FORD,ANALYST,7566,03-DEC-81,3000,,20
7934,MILLER,CLERK,7782,23-JAN-82,1300,,10
3) And then just load it !
I mean seriously…why can’t I just run sqlldr, pass the table name, and just have the thing work !
Well…maybe….just maybe….
C:\temp>sqlldr userid=scott/tiger data=emp.dat table=emp_tab
SQL*Loader: Release 12.1.0.2.0 - Production on Mon Aug 17 11:08:33 2015
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Express Mode Load, Table: EMP_TAB
Path used: External Table, DEGREE_OF_PARALLELISM=AUTO
Table EMP_TAB:
14 Rows successfully loaded.
Check the log files:
emp_tab.log
emp_tab_%p.log_xt
for more information about the load.
Yup…Once you get to version 12c, there is now express mode for SQL Loader, which for all those simple loads that dont need all the bells and whistles, you can just jump straight in… and load your data. Very cool indeed.
The log file reveals what is going on “under the covers”
creating external table "SYS_SQLLDR_X_EXT_EMP_TAB"
CREATE TABLE "SYS_SQLLDR_X_EXT_EMP_TAB"
(
"EMPNO" NUMBER(4),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8MSWIN1252
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'emp_%p.bad'
LOGFILE 'emp_tab_%p.log_xt'
READSIZE 1048576
FIELDS TERMINATED BY "," LRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
"EMPNO" CHAR(255),
"ENAME" CHAR(255),
"JOB" CHAR(255),
"MGR" CHAR(255),
"HIREDATE" CHAR(255)
DATE_FORMAT DATE MASK "DD-MON-RR",
"SAL" CHAR(255),
"COMM" CHAR(255),
"DEPTNO" CHAR(255)
)
)
location
(
'emp.dat'
)
)REJECT LIMIT UNLIMITED
executing INSERT statement to load database table EMP_TAB
INSERT /*+ append parallel(auto) */ INTO EMP_TAB
(
EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO
)
SELECT
"EMPNO",
"ENAME",
"JOB",
"MGR",
"HIREDATE",
"SAL",
"COMM",
"DEPTNO"
FROM "SYS_SQLLDR_X_EXT_EMP_TAB"
dropping external table "SYS_SQLLDR_X_EXT_EMP_TAB"
Table EMP_TAB:
14 Rows successfully loaded.
Run began on Mon Aug 17 11:08:33 2015
Run ended on Mon Aug 17 11:08:36 2015
Elapsed time was: 00:00:03.21
CPU time was: 00:00:00.09
Learn more about SQL Loader Express here
Learning is not a spectator sport 
Got some thoughts? Leave a comment