Loading file data … easier than you think

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

3 thoughts on “Loading file data … easier than you think

  1. 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s