SQL*Loader

SQL*Loader est, comme son nom l'indique un utilitaire de chargement spécifique pour les bases Oracle. Il permet d'initialiser une base de données, ou plus précisément une ou plusieurs tables avec des données issues d'un fichier texte.
Ainsi si l'on souhaite migrer des données d'un fichier Mainframe vers Oracle, on pourra extraire les données du fichier d'origine pour produire un fichier texte et ensuite utiliser SQL*loader pour effectuer le chargement automatique de la (ou des) table(s).

Principales caractéristiques

- charge des fichiers texte externes dans Oracle
- format des fichiers d'entrée fixe ou variable (avec séparateur)
- utilisation de fonctions SQL
- génération de clés uniques
- mode "direct" optimisé
- gestion des logs, des erreurs et possibilité de reprise

Principe général

La (ou les) table(s) destination sont créées dans le schéma cible.
On précise le format des entrées et des sorties dans un fichier de paramétrage, appelé fichier de controle, créé avec un éditeur de texte. Un fichier log donnant les résultats du chargement est généré. En cas d'erreur, les enregistrement rejetés sont stockés dans un fichier '.bad', pour être éventuellment retraités.

Commande minimale :
sqlldr nom_user/mot_de_passe@base control=nom_fic.ctl

la structure de la table cible doit être créée avant le chargement, SQL*Loader à la différence d'autres outils ne crée pas la table.

options de la ligne de commandes

option	description	Defaut
userid	username et mot de passe
control	nom du fichier de controle du loader
data	nom du fichier de données d'entrée
log	nom du fichier de trace
bad	nom du fichier des enregistrements rejetés
parfile	nom du fichier contenant les parametres de la commande...
skip n	nombre d'enregistrement logiques à sauter	0
load n	nombre à charger	all
errors n	nombre max d'erreurs autorisées	50
rows n	nb de lignes du tableau utilisé pour les entrées	64
bindsize	taille du tableau précédent en bytes	OSdep
silent	n'affiche plus les messages pendant l'execution
direct	utilise l'accès direct (direct path)	false
parrallel	chargement en mode parrallelisé	false
file	fichier d'allocation pour les chargement parralleles
discard	fichier des enregistrements non chargés intentionnellement (saut conditionnel)
discardmax	nombre maximums de ces enregistrements non chargés	all

Le contenu du fichier de controle, par étapes

spécification des entrées

load data infile nom_fic | *
nom du fichier d'entrée ou * si les datas sont à la suite :
Exemples
1) load data infile c:\temp\entrees.dat
2) load data *
...
begindata
1000;Martin;Essonne
2000;Dupont;Marne
...

mode de chargement

insert insère les datas dans une table vide
append insère les datas à la suite des données existantes
replace insère les datas en remplaçant les données existantes
truncate insère les datas après un TRUNCATE

table cible

into nom_table (TRAILING NULLCOLS) spécifie la ou les tables (si plusieurs INTO) à charger

description générale des champs
fields spécifie les délimiteurs
exemple : field terminated by ';' optionally enclosed by ' " '

specif colonnes

col par défaut de type CHAR
col [POSITION debut:fin | * ] type_col [longueur] [format] [NULLIF col = BLANKS]
exemple
nom CHAR
no INTEGER EXTERNAL

les types numériques (INTEGER, DECIMAL, FLOAT, ...) sans EXTERNAl sont des types binaires ! La plupart du temps les numérqiues en entrée sont des caractères !

remarque : le type de colonne peut être un type spécial
- RECNUM : no de ligne du fichier d'entrée
- SYSDATE : la date du jour
- CONSTANT valeur : une constante
- SEQUENCE (debut, increment) : un compteur automatique

clauses spéciales

continueif this permet de fusionner plusieurs enregistrement physique en cas d'incomplétude
exemple : continueif this (1) = '*' concatene la ligne" suivante si la ligne courante a '*' en colonne 1

WHEN condition specif colonne

fichier de contrôle avec entrées au format variable

-- param.ctl
LOAD DATA INFILE 'monfic' --fichier à charger (.dat)
REPLACE --ecrase la table
INTO TABLE matable
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"' --car. de séparation
( NUMERO, NOM, PRENOM , DATE_NAISSANCE date "DDMMYY", SEXE)

fichier de contrôle avec entrées au format fixe

LOAD DATA INFILE 'monfic.fix'
REPLACE
INTO TABLE matable ( NUMERO position(1-5) char,
-- position de début et de fin du champ
NOM position(6-35) char, PRENOM position (36-50) char,
DATE_NAISSANCE position (*) date "DDMMYY",
--indique la position suivante
SEXE position(*) char
--longueur iimplicite pour char : 1 )

Chargement de 2 tables à partir d’un fichier

Un fichier (3 champs) éclaté dans 2 tables (chacune 2 colonnes) une pour les employes, l'autre pour les projets avec une colonne de jointure sur le no d'employe...
fichier d'entree :

1000-deleglise-------10
2000-martin----------20
3000-dupont----------10
...

fichier de controle

...
into table employes
(no_emp position(1:4) integer external,
nom position (6:20) char)
into table projet
(no_emp position (1:4) integer external,
no_projet position (22:25) integer external)
...

Compte rendu d’execution

sqlldr ch/ch control=param.ctl
SQL*Loader: Release 7.0.15.4.0 - Production on Wed Mar 15 15:52:18 1995 Copyright (c) Oracle Corporation 1979, 1992. All rights reserved.
Commit point reached - logical record count 2
Fichier log : >more param.log SQL*Loader: Release 7.0.15.4.0 - Production on Wed Mar 15 15:52:18 1995 Copyright (c) Oracle Corporation 1979, 1992. All rights reserved.
Control File: param.ctl Data File: monfic.fix Bad File: monfic.bad Discard File: none specified (Allow all discards)
Number to load: ALL Number to skip: 0 Errors allowed: 50 Bind array: 64 rows, maximum of 65536 bytes
Continuation: none specified Path used: Conventional
Table MATABLE, loaded when SEXE = 0X46(character 'F')
Insert option in effect for this table: REPLACE
Column Name Position Len Term Encl Datatype
NUMERO 1:5 5 CHARACTER NOM 6:35 30 CHARACTER PRENOM 36:50 15 CHARACTER DATE_NAISSANCE NEXT 6 DATE DDMMYY SEXE NEXT 1 CHARACTER Table TATABLE, loaded when 57:57 = 0X4d(character 'M') Insert option in effect for this table: REPLACE Column Name Position Len Term Encl Datatype
NUMERO 1:5 5 CHARACTER NOM 6:35 30 CHARACTER PRENOM 36:50 15 CHARACTER DATE_NAISSANCE NEXT 6 DATE DDMMYY Table MATABLE:
1 Row successfully loaded. 0 Rows not loaded due to data errors.
1 Row not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.