Posted by 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 
Can I load XML data using this syntax?
Thanks,
Arun
Arun, using APEX 19.1 you can. Simply drag and drop a CSV, XLSX, XML, or JSON file and create a new table in less time than it took me to write this reply! Load it into a “load” table and then simply copy the data into the original table if necessary. Try it out on https://apex.oracle.com to see just how easy it is.
I don’t think so. But SQL Loader Express is documented well in the 12c doco – check the Utilities manual
That’s very good — I’d use it even for more complex loads that would not work though this method, just to get that script as a starting point.