Tables externes

Description

Permet depuis oracle d'accéder par SQL a des données stockées dans des fichier sur disque.

Les données sont accessible en mode "read only". Pour modifier les données il faut modifier le fichier.

Format "SQL loader"

Les données sont stockées dans un fichier "plat". On peut définir librement le caractère de fin de champ et de fin de ligne.

Format datapump

Les données sont stockées dans un format propriétaire.

Attention ce format me semble incompatible avec les fichiers crées par les outils externes de datapump Data pump (impdp/expdp).

Création

Les fichiers devront être présent dans un répertoire définit dans oracle (exemple)

   CREATE DIRECTORY EXTERN_TABLES AS '/opt/datastore/datapump';
   GRANT READ, WRITE ON DIRECTORY EXTERN_TABLES to user;

Format "SQL loader"

On considère le fichier texte "nc.txt":

  001,Ouvea,20-39-08S,166-33-43E,98814
  002,Lifou,20-58-00S,167-14-00E,98820
  003,Tiga,21-06-25S,167-48-53E,98820
  004,Mare,21-31-00S,167-59-00E,98878
  005,Belep,19-45-00S,163-40-00E,98811
  006,Pins,22-37-00S,167-29-00E,98832

On le place dans le répertoire /opt/datastore/datapump (accessible par oracle). Et on entre la commande de création :

create table nc_ilds
  ( iuid               varchar2(3),
    name               varchar2(50),
    latitude   varchar2(12),
    longitude  varchar2(12),
    postal_code        varchar2(10)
  )
  organization external
  ( default directory EXTERN_TABLES
    access parameters
    ( records delimited by newline
      fields terminated by ','
    )
    location ('nc.txt')
);

On peut ensuite faire des requêtes sur cette nouvelle table:

SQL> select latitude,longitude from nc_ilds;

LATITUDE                             LONGITUDE
------------------------------------ ------------------------------------
20-39-08S                            166-33-43E
20-58-00S                            167-14-00E
21-06-25S                            167-48-53E
21-31-00S                            167-59-00E
19-45-00S                            163-40-00E
22-37-00S                            167-29-00E

6 rows selected.

Format datapump

On peut créer une table stockée dans un fichier au format propriétaire oracle a partir de données existant dans un table oracle (ou le résultat d'une requête complexe).

 create table export_ncilds
   organization external
   ( type oracle_datapump
     default directory EXTERN_TABLES
     location ('nc.dump')
   ) as select * from nc_ilds;

Ici j'ai utilisé la table nc_ilds crée plus haut dans l'article mais on peut remplacer le select par n'importe quelle requête.

La table export_ncilds est crées avec le résultat de la requete select * from nc_ilds.

On peut également aller consulter le fichier sur le serveur.

$ ls -l nc.dump
-rw-r----- 1 oracle oinstall 12288 Mar 24 16:29 nc.dump

Le fichier ainsi crée peut être déplacé sur un autre serveur et "monté" grâce à la commande.

create table nc_ilds
 ( iuid                varchar2(3),
   name                varchar2(50),
   latitude    varchar2(12),
   longitude   varchar2(12),
   postal_code varchar2(10)
 )
 organization external
 ( type oracle_datapump
    default directory EXTERN_TABLES
    location ('nc.dump')
  ) ;