Mit dem SQL*Loader (sqlldr) (High speed, bulk data Loader) lassen sich Text Daten in die Datenbank laden.
Siehe ⇒ https://docs.oracle.com/database/121/SUTIL/GUID-8D037494-07FA-4226-B507-E1B2ED10C144.htm
Der SQL*Loader ist die schnellste Möglichkeit Daten in die Datenbank zu landen und kann neben dem klassischen CSV Formaten fast jedes noch so exotische Format verarbeiten.
Allerdings ist meist etwas Geduld gefragt, dem Werkzeug beizubringen, wie die Daten zu interpretieren sind.
Zwei Arbeitsweisen:
Der SQL*Loader (sqlldr) findet sich in der Client und in der Server Installation der Oracle Datenbank.
Übersicht SQL*Loader Funktion:
Aufruf zum Beispiel über ein Dos Script:
set ORACLE_HOME=c:\oracle\products\12.1.0.2\dbhome_1 set LOGIN_DATA=scott/tiger@gpidb %ORACLE_HOME%/bin/sqlldr %LOGIN_DATA% control=PARAM_IMPORTS.ctl bad=bad.log log=log.log discard=discard.log
In der Control Datei wird definiert, wie die zu ladende Datei aufgebaut ist und welche Daten wie in eine oder mehrere Zieltabellen geladen werden sollen.
Aufbau:
Funktionen
Einfüge-Optionen:
Grundaufbau der Datei (Kommentarzeichen ist „–“):
-- Aufrufoptionen vom SQLLDR hinterlegen Options ( ERRORS= 200000 ,SKIP=1 ) LOAD DATA -- Char set CHARACTERSET UTF8 --Quelldaten INFILE <textfile.dat> -- ob ersetzt, oder angehängt werden soll -- replace ersetzt die Daten ! REPLACE -- Wohin INTO TABLE <MY_DB_TABLE> -- Aufbau der Datei FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' TRAILING nullcols -- Spalten der Tabelle + Logik beim Einlesen der Daten ( PARAM_TABLE_NAME CHAR(32) , PARAM_01 CHAR(255) , PARAM_02 CHAR(255) , PARAM_03 CHAR(255) )
Option SKIP verwenden
Options ( SKIP=1 )
Soll beim Laden die ID Spalte einer Tabelle mit einer fortlaufenden Nummer gefüllt werden, kann bei der Spaltendefinition eine Oracle Sequence angegeben werden:
Mit EXPRESSION wird nun eine Funktion verwandt, in dem Fall der Aufruf des nächsten Wertes der Sequence
( ID_COLUMN EXPRESSION "parameter_id_seq.nextval" ...
Mit EXPRESSION wird nun eine Funktion verwandt die einen Wert lieft, in diesem Fall immer 1:
OUE_UMPID EXPRESSION "to_char(1)"
Ist ein Wert in der CSV leer, dann soll ein NULL Value in der Datenbank Tabelle eingefügt werden:
PARAMETER_30 CHAR(255) NULLIF PARAM_30=BLANKS
Die Spalte in der Datenbank Tabelle ist vom Format DATE, mit dem richtigen Format String in Daten zuvor wandeln;
-- Datumsformat in der Textdatei DD.MM.YYYY , OUE_DATUM_KONGRESS DATE "DD.MM.YYYY" NULLIF OUE_DATUM_KONGRESS=BLANKS -- Datumsformat in der Textdatei YYYYMMDD"T"HH24MISS , LUM_CREATEDATE DATE 'YYYYMMDD"T"HH24MISS',
Mit „:„ wird der Wert in der CSV Datei als Bind Variable referenziert und kann dann in einer normalen SQL Funktion verwendet werden.
Beispiele:
--EMail Adresse immer klein , OUE_E_MAIL_ADRESSE CHAR(255) "LOWER(:OUE_E_MAIL_ADRESSE)" -- Falls null den Wert 0 verwenden , OUE_GASTORINTESTINAL CHAR(1) "NVL(:OUE_GASTORINTESTINAL,0)" -- Passwörter beim laden maskieren , LUM_PASSWORD "translate(upper(substr(:LUM_PASSWORD,1,8)),'ABCDEFGIHUJKLMNOPQRSTUVWXYZ0123456789ÄÜÖß[].','***********************************************')", -- Nur einen Teil der Daten laden , LUM_ADDRESSINFO CHAR(4000) NULLIF LUM_ADDRESSINFO=BLANKS "substr(:LUM_ADDRESSINFO,1,4000)"
Zuvor den Zeichensatz der Datei mit in der Steuerdatei angeben:
..
LOAD DATA
CHARACTERSET UTF8
INFILE *
..
Keyword FILLER und BOUNDFILLER (wenn doch referenziert werden soll! (ab 9i)).
Beispiel:
..
PARAM1 FILLER
,PARAM2 BOUNDFILLER
,PARAM3 BOUNDFILLER,
PARAM4 ":PARAM2 + :PARAM3"
..
Geladen wird nur der Wert von PARAM2 und PARAM3 in die Spalte PARAM4.
Beispiel:
load data infile 'example1.dat' "fix 25" into table import fields terminated by ',' optionally enclosed by '"' (artNr , artName ) example.dat: 10010, "DDRAM 256MB" , 200103 , Mousepad, 3010 ,Papier "Din A4", 89999,SDRAM 64 MB,
load data infile 'example2.dat' "var 3" into table import fields terminated by ',' optionally enclosed by '"' (artNr , artName ) example.dat: 02010010,"DDRAM 256MB",016200103,Mousepad, 0213010,Papier "Din A4",01889999,SDRAM 64 MB,
load data infile 'example.dat' "str X'7c0d0a'" into table import fields terminated by ',' optionally enclosed by '"' (artNr , artName ) example.dat: 10010,"DDRAM 256MB",| 200103,Mousepad,| 13010,Papier "Din A4",| 89999,SDRAM 64 MB,|
Load data infile 'example.dat' "str X'7c0d0a'" into table import ( artNr position(1:6) , artName position(*+1:21) "replace(:artName,chr(34),'')" ) example.dat: 10010 "DDRAM 256MB"| 200103Mousepad| 13010 Papier "Din A4"| 89999 SDRAM 64 MB|
LOAD DATA INFILE * INTO TABLE MMS_DIRLOAD_FILES APPEND FIELDS TERMINATED BY '?' (ID SEQUENCE(COUNT), FILENAME, FILESIZE, FILE_LOADER, RES_FILE FILLER CHAR, FILE_DATA LOBFILE (RES_FILE) TERMINATED BY EOF NULLIF RES_FILE = 'NONE' ) BEGINDATA D:\data\sort.dat?59392?1?gpipperr?D:\data\sort.dat?
load data infile 'example5.dat' "str X'7c0d0a'" replace into table import (artNr position(1:7), artName position(8:22)) into Table lager ( artNrFK position(1:7), Laden position(23:30), anzahl position(31:33) "nvl(:anzahl,0)") into Table lager ( artNrFK position(1:7), Laden position(34:44), anzahl position(45:49) "nvl(:anzahl,0)") Example.dat: 10010 DDRAM 256MB Hamburg 10 Frankfurt 20 | 200103 Mousepad Hamburg 6 Frankfurt 20 | 13010 Papier Din A4 Hamburg 9 Frankfurt 2000| 89999 SDRAM 64 MB Hamburg 56 Frankfurt |
load data infile 'example6.dat' replace continueif this(1)='*' into table import (artNr position(1:6), artName position(7:21) ) Example6.dat: *12345 "DDRAM 256MB" *200103 Mousepad *13010 Papier "Din A4" *89999 SDRAM 64 MB
Die Daten direkt mit der Option DIRECT=TRUE in die Datenblöcke laden.
Einschränkungen
Kann parallel ausgeführt werden.
Mit der Option UNRECOVERABLE werden für die eigentlichen Datendateien keine Redos geschrieben (nur für einen Index).