OraMatt: YABAOracle

I just read this in this month’s Ask Tom article in Oracle Magazine.

I didn’t know you could do this with SQL*Loader and I thought I’d share.

For those who don’t know what SQL*Loader is, it was a utility that ships with the database that loads data from external files into tables of an Oracle database.
It has a powerful data parsing engine that puts little limitation on the format of the data in the data file.

And External Tables are a feature that complements the SQL*Loader functionality.
External Tables are FILEs that are not under the control of the database but allows for users to access the files from regular SQL.

External Tables can do EVERYTHING that SQL*Loader can and more….but A LOT of customers have legacy data loading processes that use SQL*Loader…so how do we get them to External Tables????

Here’s the SUPER easy way to do it if they have pre-existing SQL*Loader control files and target tables…

Say we have a table PATIENTS with the following columns:

CREATE TABLE PATIENTS
(
 "PATIENT_NAME" VARCHAR2(30),
  "V_YYYY" VARCHAR2(255), "V_MM" VARCHAR2(255),
  "V_DD" VARCHAR2(255),   "V_HH" VARCHAR2(255),
  "V_MI" VARCHAR2(255),   "V_SS" VARCHAR2(255),
  "SERVICE_DATE" VARCHAR(4000)
)
;

And we have a file that contains the data we want to load as such:

"Tom Tommy",2008,03,18,23,59,4
"Rob Robertson",2011,10,31,18,00,59
"Bill Williamson",2012,02,31,01,10,5
"Bob Bobbington",2012,02,31,01,10,5
"Matt Dee",2013,01,11,01,11,4

And we have a control file (this is the file that tells SQL*Loader how to load the data and stuff…):

LOAD DATA
INFILE '/home/oracle/PATIENTS.dat'
INTO TABLE PATIENTS
REPLACE
FIELDS TERMINATED BY ','
trailing nullcols
(
patient_name
,v_yyyy boundfiller char
,v_mm boundfiller char
,v_dd boundfiller char
,v_hh boundfiller char
,v_mi boundfiller char
,v_ss boundfiller char
,service_date "to_date(:v_yyyy
|| :v_mm || :v_dd || :v_hh || :v_mi
|| :v_ss ,'YYYYMMDDHH24MISS')"
)

So….we all that said (here’s the easy part)…migrating them to External Tables is EASY

Just have them invoke SQL*Loader and pass the switch external_table=generate_only log=make_ext_table.log like this

sqlldr matt/matt patient.ctl external_table=generate_only log=make_ext_table.log

That will generate a file that creates ALL the stuff needed for creating the External Table!!!!!
Here’s a snippet…

CREATE DIRECTORY statements needed for files
------------------------------------------------------------------------
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00001 AS '/home/oracle'
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle/'

CREATE TABLE statement for external table:
------------------------------------------------------------------------
CREATE TABLE "SYS_SQLLDR_X_EXT_PATIENTS"
(
  "PATIENT_NAME" VARCHAR2(30),
  "V_YYYY" VARCHAR2(255),
  "V_MM" VARCHAR2(255),
  "V_DD" VARCHAR2(255),
  "V_HH" VARCHAR2(255),
  "V_MI" VARCHAR2(255),
  "V_SS" VARCHAR2(255),
  "SERVICE_DATE" VARCHAR(4000)
)
ORGANIZATION external
(
  TYPE oracle_loader
  DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
    BADFILE 'SYS_SQLLDR_XT_TMPDIR_00001':'patient.bad'
    LOGFILE 'make_ext_patient.log_xt'
    READSIZE 1048576
    FIELDS TERMINATED BY "," LDRTRIM
    MISSING FIELD VALUES ARE NULL
    REJECT ROWS WITH ALL NULL FIELDS
    (
      "PATIENT_NAME" CHAR(255)
        TERMINATED BY ",",
      "V_YYYY" CHAR(255)
        TERMINATED BY ",",
      "V_MM" CHAR(255)
        TERMINATED BY ",",
      "V_DD" CHAR(255)
        TERMINATED BY ",",
      "V_HH" CHAR(255)
        TERMINATED BY ",",
      "V_MI" CHAR(255)
        TERMINATED BY ",",
      "V_SS" CHAR(255)
        TERMINATED BY ",",
      "SERVICE_DATE" CHAR(255)
        TERMINATED BY ","
    )
  )
  location
  (
    'patient.dat'
  )
)REJECT LIMIT UNLIMITED

The file even gives you INSERT STATEMENTS to LOAD FRAKING DATA!!!!! WHAT!?!?!!? WHAT!?!!??!?!! DID THAT JUST HAPPEN?!?!?!??!?!?

INSERT statements used to load internal tables:
------------------------------------------------------------------------
INSERT /*+ append */ INTO PATIENTS
(
  PATIENT_NAME,
  SERVICE_DATE
)
SELECT
  "PATIENT_NAME",
  to_date("V_YYYY"
|| "V_MM" || "V_DD" || "V_HH" || "V_MI"
|| "V_SS" ,'YYYYMMDDHH24MISS')
FROM "SYS_SQLLDR_X_EXT_PATIENTS"