Sql*Loader : Comment effectuer des chargements de données sous Oracle

Date de publication : 14 Septembre 2005

Par Jaouad (home)
 

Charger des données provenant d'un fichier
(Cet article couvre les versions 8 à 10G)

I. Préface

Dans un premier temps nous nous attellerons à détailler l'outil SQL*LOADER ainsi que ses différentes options ensuite nous verrons concrètement l'utilité de cet outil par un exemple simple.
Ici il s'agit d'un tutorial de prise en main rapide de l'outil. Pour les cas plus complexe, je vous renvoie à la documentation.

II. Introduction

SQL LOADER ou sqlldr est un exécutable binaire qui permet le chargement de données à partir de fichiers plats et à destination d'Oracle.
Il est présent dans le répertoire suivant :
Unix :
$ORACLE_HOME/bin/
Windows :
%ORACLE_HOME%\bin
Exemple sous Unix ( HP PA RISC )

$ cd $ORACLE_HOME/bin
$ ls -ltr sqlldr 
-rwxr-x--x   1 oracle     dba         572928 Nov 12  2003 sqlldr

Cependant en fonction de la plate-forme du système d'exploitation et de la version d'oracle le binaire SQL*LOADER peut avoir différents noms :

III. SQL*LOADER

III-A. Comment appeler sqlldr ?

SQL*Loader: Release 9.2.0.1.0 - Production on Lu Sep 12 15:03:01 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Syntaxe : SQLLDR keyword=value [,keyword=value,...]!

Notons que pour pouvoir appeler l'outil ainsi il est primordial que le chemin $ORACLE_HOME/bin apparaisse dans le PATH ou alors que ce dernier soit positionné avant l'appel.

III-B. Comment marche sqlldr

III-B-1. Le fichier de contrôle

• Décrire les actions que Sql*Loader doit effectuer.
• Trouver les données à charger
• Effectuer une analyser syntaxique et interpréter les données.
• Insérer les données.
• (... )

III-B-2. Le fichier de données

C'est un fichier plat ( csv, txt ... ) qui stocke les données et les séparateurs.

III-B-3. Le fichier : Bad Files

Il contient les enregistrements qui ont été rejeté soit par SQL*LOADER soit par Oracle. En effet le processus de chargement se déroule ainsi : Sql*Loader lit les lignes une à une puis les envoie à Oracle.
Si une ligne venait à ne pas être conforme pour SQLLOADER celle ci serait rejetée et un fichier bad file serait crée ou alimenté s'il est déjà crée.
Lorsque la ligne parvient à Oracle, celui ci va voir si son insertion est possible ou pas en fonction de clé primaire, contrainte, définition de la colonne ?
S'il apparaît que la ligne n'est pas en conformité avec cela alors Oracle va rejeter la ligne en suivant la même procédure que Sql*Loader.
Comme le fichier BAD est généré comme le fichier de données, vous pouvez après avoir déterminé et solutionné le problème effectué un nouveau chargement avec uniquement les données contenues dans le BAD FILE.

III-B-4. Le fichier : Discard Files

Le fichier DISCARD peut être spécifié lors l'appel de la commande ou alors directement dans le fichier contrôle.
Ce fichier est crée uniquement sur demande explicite et détaille les enregistrements qui n'ont pas été retenu par Sql*Loader.
En effet si dans le fichier de contrôle on spécifie une clause pour filtrer les données et que Sql*Loader détecte des lignes qui ne sont pas en conformité avec cette clause, elle les stocke dans le ficher DISCARD.

III-B-5. Le fichier Log : Log files

Le loader d'oracle va à chaque exécution du binaire produire un fichier log ou comme dans tout fichier log il va effectuer un résumé des actions :

• Information d'entête : Date d'exécution et numéro de version de l'outil.
• Information globale : Nom des fichiers en entrée et sortie. Arguments des commandes. Spécification de caractère de suite.
• Information sur la table : (nom de la table, condition de chargement, spécification d'insertion ( INSERT, APPEND, REPLACE ? ), détail des colonnes de la tables.
• Information sur les fichiers de données(erreurs lors des chargements, données écartées).
• Information sur les données insérées. ( Nombre d'insertion, nombre de rejet, nombre d'erreurs ).
• Quelques statistiques : Espace utilisé, Total elapsed time, Total CPU time...

III-C. Les paramètres de Sql*Loader

C:\>sqlldr

SQL*Loader: Release 9.2.0.1.0 - Production on Lu Sep 12 15:55:34 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Syntaxe : SQLLDR keyword=value [,keyword=value,...]

Mots-clÚs valides :

    userid -- ORACLE username/password
   control -- Control file name
       log -- Log file name
       bad -- Bad file name
      data -- Data file name
   discard -- Discard file name
discardmax -- Number of discards to allow          (Tous par dÚfaut)
      skip -- Number of logical records to skip    (0 par dÚfaut)
      load -- Number of logical records to load    (Tous par dÚfaut)
    errors -- Number of errors to allow            (50 par dÚfaut)
      rows -- Number of rows in conventional path bind array or between direct p
ath data saves
      (Par defaut: AccÞs conventionnel: 64, AccÞs direct: tous)
  bindsize -- Size of conventional path bind array in bytes  (256000 par dÚfaut)

    silent -- Suppress messages during run (header,feedback,errors,discards,part
itions)
    direct -- use direct path                      (FALSE par dÚfaut)
   parfile -- parameter file: name of file that contains parameter specification
s
  parallel -- do parallel load                     (FALSE par dÚfaut)
      file -- File to allocate extents from
skip_unusable_indexes -- disallow/allow unusable indexes or index partitions  (F
ALSE par dÚfaut)
skip_index_maintenance -- do not maintain indexes, mark affected indexes as unus
able  (FALSE par dÚfaut)
  readsize -- Size of Read buffer                  (1048576 par dÚfaut)
external_table -- use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE
 (NOT_USED par dÚfaut)
columnarrayrows -- Number of rows for direct path column array  (5000 par dÚfaut
)
streamsize -- Size of direct path stream buffer in bytes  (256000 par dÚfaut)
multithreading -- use multithreading in direct path
 resumable -- enable or disable resumable for current session  (FALSE par dÚfaut
)
resumable_name -- text string to help identify resumable statement
resumable_timeout -- wait time (in seconds) for RESUMABLE  (7200 par dÚfaut)

III-C-1. Le paramètre Direct

Nous allons détailler cette option qui peut parfois être utile :
L'insertion par chemin direct fait l'analyse syntaxique des données d'entrée selon les spécifications des champs, convertit les données d'entrée en colonne datatype et construit un tableau de colonne.
On passe le tableau de colonne au « bloc formatter », qui crée des blocs de données sur le format de bloc de base de données d'Oracle.
Les blocs de base de données nouvellement formatés sont écrits directement dans la base de données
Contournant ainsi la plupart de traitement de RDBMS( journalisation des données dans les REDO LOGS ? ) .
L'insertion par chemin direct est beaucoup plus rapide que l'insertion par chemin conventionnel, mais entraîne plusieurs restrictions.
Elle enlève également la possibilité d'avoir une restauration de ces données puisque que par extension les données ne seront pas présentes dans les fichiers REDO et d'archives.

Attention, L'option direct peut être également comporter quelques effets de bord. En effet lors de l'insertion par ce mode Oracle ne va pas chercher à déterminer quels sont les blocs libres mais va insérer les données au-dessus du High Water Mark (HWM). C'est pourquoi il faut, lorsque nous insérons les données dans une table de travail, utiliser l'option TRUNCATE.


Les restrictions à l'utilisation du chargement direct :

• Tables non clusterisées.
• Pas de transactions actives sur les tables concernées ( on peut vérifier cela on contrôlant les verrous sur la table ).
• Pas de restrictions SQL dans le fichier de contrôle

• LOBs
• VARRAY
• nested tables
• REF colonne
• Fonctions dans le fichier de contrôle
• (...)

IV. Exemple

IV-A. Préparation

IV-B. Création de la table

SQL> create table DVP_loader ( nom varchar2(20) , salaire  number ) ; 

Table cré

IV-C. Création du fichier de données

Jaouad;100
orafrance;200
léoanderson;300
bouyao;400
Nuke_y;500
sheikyerbouti;600
pomalaix;700
titides;800
aline;900
denisys;1000
niourk;1100

IV-D. Création du fichier de contrôle

LOAD DATA INFILE 'data.csv'
TRUNCATE
INTO TABLE DVP_LOADER 
FIELDS TERMINATED BY ';'
(  NOM ,          
 SALAIRE        
)

IV-E. Création de la commande de chargement

C:\load>sqlldr userid=formation/formation control=control.txt log=log.txt bad=bad.txt discard=disard.txt direct=y errors=0

Nous avons paramétrer le seuil de tolérance d'erreurs à 0 afin de s'assurer que nous insérons toutes les données.

IV-F. Résultat

SQL> SELECT bytes/1024 taille_ko   FROM dba_segments 
  2  WHERE segment_name ='DVP_LOADER' AND  owner ='FORMATION' 
  3  ;

 TAILLE_KO
----------
       192

C:\load>sqlldr userid=formation/formation control=control.txt log=log.txt bad=ba
d.txt discard=disard.txt direct=y errors=0

SQL*Loader: Release 9.2.0.1.0 - Production on Lu Sep 12 18:00:14 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Chargement terminÚ - calcul enregistrement(s) logique(s) 11.

C:\load>`

SQL> analyze table formation.DVP_LOADER compute statistics ; 

Table analysée.

SQL> select count (*) from formation.DVP_LOADER ;

  COUNT(*)
----------
        11

SQL> r
  1  SELECT bytes/1024 taille_ko   FROM dba_segments
  2* WHERE segment_name ='DVP_LOADER' AND  owner ='FORMATION'

 TAILLE_KO
----------
        64

SQL>

SQL*Loader: Release 9.2.0.1.0 - Production on Lu Sep 12 18:00:14 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Fichier de contrôle :   control.txt
Fichier de données :      data.csv
  Fichier BAD :     bad.txt
  Fichier DISCARD : disard.txt
 (Allouer tous les rebuts)

Nombre à charger : ALL
Nombre à sauter: 0
Erreurs permises: 0
Continuation :    aucune spécification
Chemin utilisé:      Direct

Table DVP_LOADER, chargé à partir de chaque enregistrement physique.
Option d'insertion en vigueur pour cette table : TRUNCATE

   Nom de colonne               Position   Long.  Séparat. Encadrem. Type de données
------------------------------ ---------- ----- ---- ---- ---------------------
NOM                                 FIRST     *   ;       CHARACTER            
SALAIRE                              NEXT     *   ;       CHARACTER            


Table DVP_LOADER :
  Chargement réussi de 11 Lignes.
  0 Lignes chargement impossible dû à des erreurs de données.
  0 Lignes chargement impossible car échec de toutes les clauses WHEN.
  0 Lignes chargement impossible car tous les champs étaient non renseignés.

Taille du tableau de liens non utilisée dans le chemin direct.
Lignes de tableau de colonnes :    5000
Octets de tampon de flux de données :  256000
Octets de tampon de lecture : 1048576

Nombre total d'enregistrements logiques ignorés :          0
Nombre total d'enregistrements logiques lus :            11
Nombre total d'enregistrements logiques rejetés :         0
Nombre total d'enregistrements logiques mis au rebut :        0
Nombre total de tampons de flux de données chargés par le thread principal de SQL*Loader :        2
Nombre total de tampons de flux de données chargés par le thread de chargement de SQL*Loader :        0

Le début de l'exécution a été effectué sur Lu Sep 12 18:00:14 2005
La fin de l'exécution a été effectuée sur Lu Sep 12 18:00:14 2005

SQL> select * from dvp_loader ;

NOM                     SALAIRE
-------------------- ----------
Jaouad                      100
orafrance                   200
léoanderson                 300
bouyao                      400
Nuke_y                      500
sheikyerbouti               600
pomalaix                    700
titides                     800
aline                       900
denisys                    1000
niourk                     1100

11 ligne(s) sélectionnée(s).

V. Sql*Loader FAQ

V-A. Comment éviter de charger la première ligne

Nom;Salaire
Jaouad;100
orafrance;200
léoanderson;300
bouyao;400

C:\load
>sqlldr userid=formation/formation control=control.txt log=log.txt bad=bad.txt discard=disard.txt direct=y errors=0 skip=1

Nombre total d'enregistrements logiques ignorés :          1
Nombre total d'enregistrements logiques lus :            11

V-B. Comment filtrer les chargements

LOAD DATA INFILE 'data.csv'
TRUNCATE
INTO TABLE DVP_LOADER 
when (1:6) <> 'Jaouad' 
FIELDS TERMINATED BY ';'
(  NOM ,          
 SALAIRE        
)

Table DVP_LOADER, chargé quand 1:6 != 0X4a616f756164(caractère 'Jaouad')
Option d'insertion en vigueur pour cette table : TRUNCATE

   Nom de colonne               Position   Long.  Séparat. Encadrem. Type de données
------------------------------ ---------- ----- ---- ---- ---------------------
NOM                                 FIRST     *   ;       CHARACTER            
SALAIRE                              NEXT     *   ;       CHARACTER            

Enregistrement 1 : Rejeté - échec de toutes les clauses WHEN.
1

V-C. Comment exporter des données d'Oracle

SQL> desc dvp_loader 
 Nom                                       NULL ?   Type
 ----------------------------------------- -------- ----------------------------
 NOM                                                VARCHAR2(20)
 SALAIRE                                            NUMBER

SQL> set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
SQL> spool c:\load\data2.csv 
SQL> select nom||';'||salaire from  dvp_loader ;
Jaouad;100
orafrance;200
léoanderson;300
bouyao;400
Nuke_y;500
sheikyerbouti;600
pomalaix;700
titides;800
aline;900
denisys;1000
niourk;1100
SQL> spool off ;
SQL>

V-D. Comment charger les mêmes données dans plusieurs tables

LOAD DATA INFILE 'data.csv'
INTO TABLE DVP_LOADER 
FIELDS TERMINATED BY ';'
 (  
NOM         
)  
INTO TABLE DVP2_LOADER 
FIELDS TERMINATED BY ';'
 TRAILING NULLCOLS
(   
      
 b  
)

SQL> select * from dvp_loader ; 

NOM                     SALAIRE
-------------------- ----------
Jaouad
orafrance
léoanderson
bouyao
Nuke_y
sheikyerbouti
pomalaix
titides
aline
denisys
niourk

11 ligne(s) sélectionnée(s).

SQL> select * from dvp2_loader  ; 

A                             B
-------------------- ----------
                            100
                            200
                            300
                            400
                            500
                            600
                            700
                            800
                            900
                           1000
                           1100

11 ligne(s) sélectionnée(s).

V-E. Comment charger des données et les valeurs d'une séquence

SQL> CREATE SEQUENCE dvp_seq
  2           START WITH 1
  3      INCREMENT BY 1;

Séquence créée.

SQL> create table dvp3_loader  ( id number , nom varchar2(20) , salaire number ) ; 

Table créée.

LOAD DATA INFILE 'data.csv'
INTO TABLE DVP3_LOADER 
FIELDS TERMINATED BY ';'
 (id  "dvp_seq.nextval",
 NOM ,          
 SALAIRE        
)

SQL> select * from dvp3_loader ;

        ID NOM                     SALAIRE
---------- -------------------- ----------
         1 Jaouad                      100
         2 orafrance                   200
         3 léoanderson                 300
         4 bouyao                      400
         5 Nuke_y                      500
         6 sheikyerbouti               600
         7 pomalaix                    700
         8 titides                     800
         9 aline                       900
        10 denisys                    1000
        11 niourk                     1100

V-F. Comment charger des données avec des colonnes vides

V-G. Comment Valider uniquement à la fin du chargement

V-H. Commet sauvegarder le schéma qui a chargé les données,et le SESSIONID.

LOAD DATA INFILE 'data.csv'
INTO TABLE DVP3_LOADER 
FIELDS TERMINATED BY ';'
 (id  "USER",
 NOM ,          
 SALAIRE        
)

LOAD DATA INFILE 'data.csv'
INTO TABLE DVP3_LOADER 
FIELDS TERMINATED BY ';'
 (id  "USERENV('SESSIONID')",
 NOM ,          
 SALAIRE        
)

V-I. Comment modifier les données insérées

LOAD DATA 
INFILE 'data.csv'
TRUNCATE
INTO TABLE DVP_LOADER 
FIELDS TERMINATED BY ';'
(  NOM "upper(:NOM)",
SALAIRE   ":SALAIRE+1000"   )

SQL> r
  1*   select * from dvp_loader

NOM                     SALAIRE
-------------------- ----------
JAOUAD                     1100
ORAFRANCE                  1200
LÉOANDERSON                1300
BOUYAO                     1400
NUKE_Y                     1500
SHEIKYERBOUTI              1600
POMALAIX                   1700
TITIDES                    1800
ALINE                      1900
DENISYS                    2000
NIOURK                     2100

11 ligne(s) sélectionnée(s).

Table DVP_LOADER, chargé à partir de chaque enregistrement physique.
Option d'insertion en vigueur pour cette table : TRUNCATE

   Nom de colonne               Position   Long.  Séparat. Encadrem. Type de données
------------------------------ ---------- ----- ---- ---- ---------------------
NOM                                 FIRST     *   ;       CHARACTER            
    chaîne SQL pour la colonne : "upper(:NOM)"
SALAIRE                              NEXT     *   ;       CHARACTER            
    chaîne SQL pour la colonne : ":SALAIRE+1000"

V-J. Charger des données en fonction de taille

LOAD DATA 
INFILE 'data.csv'
TRUNCATE
INTO TABLE DVP_LOADER 
(  NOM  position (1:5)    )

  1*   select * from dvp_loader

NOM                     SALAIRE
-------------------- ----------
Jaoua
orafr
léoan
bouya
Nuke_
sheik
pomal
titid
aline
denis
niour

11 ligne(s) sélectionnée(s).)

V-K. Les Index sont mis à jour lors du chargement

V-L. Comment charger différents fichiers dans la même table

LOAD DATA 
INFILE 'data.csv'
INFILE 'data2.csv'  -- deuxième fichier 
TRUNCATE
INTO TABLE DVP_LOADER 
FIELDS TERMINATED BY ';'
(  NOM , 
salaire    )

SQL> r
  1*   select * from dvp_loader

NOM                     SALAIRE
-------------------- ----------
Jaouad                      100
orafrance                   200
léoanderson                 300
bouyao                      400
Nuke_y                      500
sheikyerbouti               600
pomalaix                    700
titides                     800
aline                       900
denisys                    1000
niourk                     1100

NOM                     SALAIRE
-------------------- ----------
Jaouad                      100

12 ligne(s) sélectionnée(s).

V-M. Comment charger des données dans des colonnes " LONG string "

Les champs dont la longueur dépasse 255 caractères doivent être spécifier dans le fichier de contrôle. Par exemple un champ de type varchar2(2000) ou char(1000) doivent être déclarés dans le CTL ainsi.

LOAD DATA INFILE 'data.csv'
TRUNCATE
INTO TABLE DVP_LOADER 
when (1:6) <> 'Jaouad' 
FIELDS TERMINATED BY ';'
(  NOM CHAR(2000)       
 )

Notons que le déclaration du champ se fait toujours en CHAR(x) que la colonne soit effectivement du char ou même varchar2

V-N. Comment créer une table externe avec le fichier de contrôle.

SQL> r
SQL> create directory loader as 'c:\load' ;

Répertoire créé.

SQL> grant read, write on directory loader to DVP; 

Autorisation de privilèges (GRANT) acceptée.

conn dvp/dvp

SQL> CREATE TABLE dvp_ext 
  2  (
  3   nom varchar2(10),
  4   salaire number 
  5  )
  6   ORGANIZATION EXTERNAL
  7   (
  8     TYPE ORACLE_LOADER
  9     DEFAULT DIRECTORY loader
 10     ACCESS PARAMETERS
 11     (FIELDS TERMINATED BY ';'
 12  (  NOM ,          
 13   SALAIRE        
 14  )
 15     ) 
 16     LOCATION ('data.csv')
 17   )
 18   PARALLEL
 19   REJECT LIMIT UNLIMITED ;

Table créée.

SQL> select * from dvp_ext ;

NOM           SALAIRE
---------- ----------
Jaouad            100
orafrance         200
bouyao            400
Nuke_y            500
pomalaix          700
titides           800
aline             900
denisys          1000
niourk           1100

9 ligne(s) sélectionnée(s).

SQL> 

V-O. Comment charger des images avec Sql*Loader.

LOAD DATA INFILE 'image.dat'
INTO TABLE DVP_LOADER 
FIELDS TERMINATED BY ';'
( external_filename  FILLER CHAR(50),
"BLOBDATA"  LOBFILE(external_filename) TERMINATED BY EOF      
 )

V-P. Comment paralléliser les chargements.

C:\load>sqlldr userid=formation/formation control=control.txt log=log.txt bad=bad.txt discard=disard.txt direct=y errors=0 parallel=TRUE

SQL
 Chemin utilisé:      Direct - avec option parallèle.

Table DVP_LOADER, chargé à partir de chaque enregistrement physique.
Option d'insertion en vigueur pour cette table : APPEND