Gunther Oracle APEX und Datenbank Wiki

Diese Seite ist nicht editierbar. Sie können den Quelltext sehen, jedoch nicht verändern. Kontaktieren Sie den Administrator, wenn Sie glauben, dass hier ein Fehler vorliegt.
=====Der Oracle SQL*Loader - Tips und Tricks im Einsatz=====

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:
  * Convential Path
    * SQL Inserts werden mit den Daten durchgeführt
  * Direct Path
    * SQL wird nicht verwendet, die Datenblöcke werden direkt geschrieben

Der SQL*Loader (sqlldr) findet sich in der Client und in der Server Installation der Oracle Datenbank.


----


==== Aufruf vom SQL Loader ====

Übersicht SQL*Loader Funktion:

{{ :dba:oracle_sql_loader_sqlldr_v01.png |Oracle SQL*Loader}}


Aufruf zum Beispiel über ein Dos Script:

<code dos>
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
</code>


----

===== Den SQL Loader steuern=====

Verarbeitung der Daten:

{{ :dba:oracle_sql_loader_sqlldr_v02.png |Oracle SQL*Loader Verarbeitung der Daten}}

====Die Kontrol Datei für den SQL*Loader====


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:

  * Load Data Anweisung
  * Ort und Art der Import Datei vorgeben
  * Tabellenstruktur der Import Tabelle 
  * Aufbau der Import Daten
    * Position
    * Datentyp
    * Trennzeichen

Funktionen
  * Daten in der Import-Tabelle anfügen oder ersetzen.
  * SQL Funktionen können auf einzelne Daten durchführt werden.
  * Steuert das Importverhalten in Zusammenhang mit den Daten ( z.B. Import in 2 Tabellen je nach Daten).
  * Large Binary Objects (Dateityp BLOB) können über die File-Anweisung in die DB importiert werden.

Einfüge-Optionen:
  * Insert (default)
    * Tabelle muss leer sein
  * Replace
    * Daten in der Tabelle werden gelöscht und neu eingefügt
    * evtl. Delete Trigger und  delete cascade wird ausgeführt
  * Truncate
    * Tabelle mit SQL Truncate löschen -> zuvor Constraints ausschalten!
  * Append
    * Daten werden angehängt


Grundaufbau der Datei (Kommentarzeichen ist "--"):
<code sql>
-- 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)

)
</code>


====Die ersten Zeile einer CSV Daten NICHT importieren ====

Option **SKIP** verwenden

<code bash>
Options (
 SKIP=1
)
</code>


====Eine Sequence für das füllen der ID Spalte einer Tabelle verwenden====

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
<code bash>
(
 ID_COLUMN EXPRESSION "parameter_id_seq.nextval" 
 ...
</code>

====Einen festen Wert hinterlegen ====

Mit EXPRESSION  wird nun eine Funktion verwandt die einen Wert lieft, in diesem Fall immer 1:
<code bash>
 OUE_UMPID                 EXPRESSION  "to_char(1)"
</code>


====Oracle NULL Wert für leere Felder einfügen ====

Ist ein Wert in der CSV leer, dann soll ein NULL Value in der Datenbank Tabelle eingefügt werden:

<code bash>
  PARAMETER_30		CHAR(255) NULLIF PARAM_30=BLANKS 
</code>

====Datumsformat hinterlegen ====
Die Spalte in der Datenbank Tabelle ist vom Format DATE, mit dem richtigen Format String in Daten zuvor wandeln;

<code bash>
 -- 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', 
 
</code>

====SQL Funktionen zum Filtern der Daten verwenden ====

Mit **":"** wird der Wert in der CSV Datei als Bind Variable referenziert und kann dann in einer normalen SQL Funktion verwendet werden.

Beispiele:

<code bash>
--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)"
  
</code>

====UTF8 Text Dateien laden ====

Zuvor den Zeichensatz der Datei mit in der Steuerdatei angeben:
<code bash>
..
LOAD DATA
CHARACTERSET UTF8
INFILE *
..
</code>

==== Einzelne Spalten NICHT laden ====

Keyword FILLER und BOUNDFILLER (wenn doch referenziert werden soll! (ab 9i)).

Beispiel:

<code bash>
..
 PARAM1 FILLER 
,PARAM2 BOUNDFILLER
,PARAM3 BOUNDFILLER,
 PARAM4 ":PARAM2 + :PARAM3"
..
</code>

Geladen wird nur der Wert von PARAM2 und PARAM3 in die Spalte PARAM4.


===== Verschiedene Text Formate laden=====

====Fixed Record Format====

Beispiel:
<code bash>
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,

</code>

====Variable Record Format====

<code bash>
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,

</code>


====Stream Record Format====

<code bash>
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,|

</code>

====Stream Record Format mit Positionsangabe====

<code bash>
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|
</code>

====Laden von Binären Daten====

<code bash>
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?

</code>

====In 2 Tabellen laden====
<code bash>
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     |
   
</code>


====2-zeilige Einträge landen====

<code bash>
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
</code>

----


===== Direct Path Load =====


Die Daten direkt mit der Option DIRECT=TRUE in die Datenblöcke laden.

Einschränkungen
  * keine SQL Funktionen verwendbar
  * keine aktiven Transaktionen auf der Import-Tabelle
  * keine geclusterten Tabellen

Kann parallel ausgeführt werden.

Mit der Option UNRECOVERABLE werden für die eigentlichen Datendateien keine Redos geschrieben (nur für einen Index).


----

===== Quellen =====

Oracle:
  * http://docs.oracle.com/database/121/SUTIL/GUID-DD843EE2-1FAB-4E72-A115-21D97A501ECC.htm#SUTIL003


Netz:
  * [[http://www.orafaq.com/wiki/SQL*Loader_FAQ|http://www.orafaq.com/wiki/SQL*Loader_FAQ]]