Create table external

Jeudi 2 septembre 2010 4 02 /09 /Sep /2010 00:13

Create table external

Create table external.

Comment accéder en lecture seule aux données dans une table externe ? Oracle nous permet de disposer de ces données comme s'il s'agissait de données résidant dans une table de la base de données. Les données externes peuvent être ensuite interrogés directement via SQL.
Aucune opération DML (UPDATE, INSERT ou DELETE) sont possibles sur des tables external.

Nous allons mapper un fichier dans C:\DIRECTORY_ORA\Fichier_TAB.txt en tant que table externe.

- séparateur = TAB définie par fields terminated by 0X'09'
- Champs texte avec ponctuation sont entre des " afin de ne pas confondre les points virgules, virgules comme d'éventuels séparateurs définie par OPTIONALLY ENCLOSED BY '"'

Aperçu du fichier Fichier_TAB.txt

ID NOM    PRENOM      VILLE   TEL   COM
1  Durand Jean        Paris      0165656565   "Commentaire, ; avec ponctuation "
2  Dupond Pierre      Versailles 0165656566   "Commentaire, avec; ponctuation ;"
3  Morin  Jean-Pierre Paris      0165656567   "Commentaire, avec ponctuation ;"

Nous allons créer simplement un DIRECTORY Oracle (indispensable).

Création du DIRECTORY + GRANT en SYSDBA.

SQL> CONNECT / AS SYSDBA;
SQL> CREATE OR REPLACE DIRECTORY mydirectory as 'C:\directory_ora';
SQL> GRANT READ, WRITE ON DIRECTORY mydirectory TO myuser;

Création de la table externe avec CREATE TABLE.

SQL> CONNECT myuser/****;
SQL> CREATE TABLE Ma_Table (ID     NUMBER(7),
                                 Nom    VARCHAR2(30),
                                 Prenom VARCHAR2(30),
                                 Ville  VARCHAR2(30),
                                 Tel    VARCHAR2(15),
                                 Com    VARCHAR2(50))
        ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER
                        DEFAULT DIRECTORY mydirectory
                        ACCESS PARAMETERS ( records delimited by newline
                                            skip 1
                                            badfile MYDIRECTORY:'Ma_Table.bad'
                                            logfile MYDIRECTORY:'Ma_Table.log'
                                            fields terminated by 0X'09'
                                            OPTIONALLY ENCLOSED BY '"'
                                            missing field values are null)
        LOCATION ('Fichier_TAB.txt'))
        PARALLEL REJECT LIMIT UNLIMITED;

Interrogation de la table via SQL

SQL> SELECT * FROM Ma_Table;

ID	Nom	Prenom	Ville	Tel	Com
1	Durand	Jean	Paris	0165656565	Commentaire, ; avec ponctuation
2	Dupond	Pierre	Versailles	0165656566	Commentaire, avec; ponctuation ;
3	Morin	Jean-Pierre	Paris	0165656567	Commentaire, avec ponctuation ;

Maintenant à partir de cette table externe vous pouvez insérer les lignes dans une table Oracle avec l'instruction INSERT INTO table(champs1, champs2,....) SELECT * FROM Ma_Table;

Liens en rapport avec l'article et qui sont susceptibles de vous intéresser

• Lire les alertes/erreurs ORA via TABLES EXTERNES