Rubriques Unix et Oracle
Accueil du siteOraclePL/SQL
Manipuler des fichiers en PL/SQL
samedi 16 octobre 2004
par Laurent
popularité : 29%

Introduction

Je me propose dans cet article d’expliquer briévement comment manipuler les fichiers sous Oracle. Cet article nous expliquera comment lire un fichier sur le système de fichiers et manipuler les données qu’il contient dans Oracle. Ou encore l’inverse, à savoir écrire des données d’une vue dynamique du système dans un fichier.

Ces opérations utiliseront le package PL/SQL UTL_FILE.

Enfin, je tiens à préciser que tout ce que je raconte est fonctionnel sous Oracle 9iR2.

Avant d’y aller !

Juste avant d’attaquer. Il faut se situer. En effet, nous travaillons au niveau SGBD qui est déjà supporté par une couche OS qui gère les aspects fichiers. En manipulant les fichiers dans Oracle, nous sommes donc tributaire de l’organisation des fichiers sous-jascente. Les procédures écrites seront généralement OS dépendantes. De plus, la notion de droit sera integrée dans Oracle. C’est ce que nous verrons au point Droits, à la fin de cet article.

Enfin, cette caractéristique va nous amener à travailler avec des objets Oracle spécifiques : les répertoires (DIRECTORY). Ainsi, nous n’aurons qu’à définir le ou les répertoire(s) de travail dans la base de données ; les objets DIRECTORY seront ensuite manipulés dans le code. Ainsi on sépare bien la gestion de fichiers au niveau SGBD du niveau OS.

Allons-y

Ecriture

Voici donc une procédure PL anonyme qui va nous permettre d’écrire le contenu des options installés sur le serveur. Pour cela, nous allons parcourir la vue v$option. J’ai commenté le code pour signaler ce qui est fait.


set serveroutput on

-- On créé un répertoire nommé DST qui pointe sur /u01 (repértoire existant au niveau de l'OS)
CREATE OR REPLACE DIRECTORY "dst" AS '/u01';

DECLARE
/* Création du descripteur de fichier */
monFichier  UTL_FILE.FILE_TYPE;
cursor c_options is
  select parameter, value
  from v$option;
buffer varchar2 (1000);

begin
 /* Ouverture du fichier options.txt en ecriture (w)
    si situant dans le répertoire contenu dans l'objet DIRECTORY
    dst.
    Les modes sont r (Read), w (Write) et a (Append)
    En mode W si le fichier n'existe pas il sera créé
  */
 monFichier := UTL_FILE.FOPEN ('dst','options.txt', 'w');
 
 for r_options in c_options loop
   /* Création du buffer en fonction du contenu du curseur */
   buffer := to_char(r_options.parameter) || ' -> ' || to_char(r_options.value);
   /* Ecriture du buffer dans le fichier */
   utl_file.put_line(monFichier, buffer);
 end loop;
 /* Fermeture du descripteur de fichier */
 UTL_FILE.FCLOSE (monFichier);

 /* Bloc d'exceptions
    Notez qu'il existe tout un ensemble d'exceptions.
    Par fénéantise (j'avoue) je ne les ai pas toute mises
  */
 EXCEPTION
   WHEN UTL_FILE.INVALID_PATH then
   dbms_output.put_line ('Chemin Invalide');
   RAISE;
   WHEN OTHERS then
      RAISE;
end;
/

Voyons maintenant le résultat du fichier généré. Je n’en met qu’une partie :


[oracle@local u01]$ cat /u01/options.txt
Partitioning -> TRUE
Objects -> TRUE
Real Application Clusters -> FALSE
Advanced replication -> TRUE
Bit-mapped indexes -> TRUE
Connection multiplexing -> TRUE
Connection pooling -> TRUE
Database queuing -> TRUE
Incremental backup and recovery -> TRUE
...

Lecture

Maintenant, nous allons réaliser une procédure anonyme complétement inutile car elle va lire les données de notre fichier options.txt et insérer les données dans une table OPTS dont la structure ressemble à cela :


SQL> desc opts;
Name             Null?    Type
---------------- -------- ----------------
VALEUR                    VARCHAR2(1000)

Complétement inutile, je vous avez prévenu ;o)

Allons-y ....


-- On créé ou on remplace un répertoire nommé DST qui pointe sur /u01 (repértoire existant au niveau de l'OS)
CREATE OR REPLACE DIRECTORY "dst" AS '/u01';

DECLARE
/* Création du descripteur de fichier */
monFichier  UTL_FILE.FILE_TYPE;
buffer varchar2 (1000);

begin
/* Ouverture du fichier options.txt en lecture (r)
   si situant dans le répertoire contenu dans l'objet DIRECTORY
   dst.
 */
monFichier := UTL_FILE.FOPEN ('dst','options.txt', 'r');

loop
  begin
    utl_file.get_line (monFichier, buffer);
  exception
    when no_data_found then exit;
  end;
  INSERT INTO opts(valeur) VALUES(buffer);
end loop;
commit;

/* Fermeture du descripteur de fichier */
UTL_FILE.FCLOSE (monFichier);

/* Bloc d'exceptions
   Notez qu'il existe tout un ensemble d'exceptions.
   Par fénéantise (j'avoue) je ne les ai pas toute mises
 */
EXCEPTION
  WHEN UTL_FILE.INVALID_PATH then
  dbms_output.put_line ('Chemin Invalide');
  RAISE;
  WHEN OTHERS then
     RAISE;
end;
/

Après exécution du bloc, une petite verification s’impose :


SQL> select valeur from opts where rownum<10;

VALEUR
----------------------------------------
Partitioning -> TRUE
Objects -> TRUE
Real Application Clusters -> FALSE
Advanced replication -> TRUE
Bit-mapped indexes -> TRUE
Connection multiplexing -> TRUE
Connection pooling -> TRUE
Database queuing -> TRUE
Incremental backup and recovery -> TRUE

9 rows selected.

Droits

Comme nous l’avons vu plus haut, nous nous situons au niveau du SGBD qui n’est pas censé gérer les droits d’accès au fichiers, gérés par la couche sous jascente et inhérente au système d’exploitation. Cependant, nous avons vu qu’Oracle se simplifiait les choses quant à l’accès aux fichiers par des objects DIRECTORY.

Par défaut, seuls les utilisateurs SYS et SYSTEM (et plus globalement un utilisateur disposant du rôle DBA) ont la possibilité de créer de tels objets. Cependant, ils peuvent octroyer ce privilège avec les ordres suivants :


-- pour accorder le privilege
GRANT CREATE ANY DIRECTORY TO utilisateurMachin;
GRANT DROP ANY DIRECTORY TO utilisateurMachin;
-- pour révoquer ce même privilège
REVOKE CREATE ANY DIRECTORY FROM utilisateurMachine;
REVOKE DROP ANY DIRECTORY FROM utilisateurMachine;

Cependant, les détenteurs du rôle DBA peuvent se la jouer plus finaude pour l’octroi des droits. En effet, on peut donner le droit de lecture et/ou d’écriture sur un objet de type DIRECTORY. La création étant une affaire de DBA.

Les ordres pour procéder sont les suivants :


GRANT READ ON DIRECTORY dir TO utilisateur;
GRANT WRITE ON DIRECTORY dir TO utilisateur;

idem pour la révocation :


REVOKE READ ON DIRECTORY dir FROM utilisateur;
REVOKE WRITE ON DIRECTORY dir FROM utilisateur;

Enfin, en ce qui concerne les droits système, il faut que l’utilisateur système détenant les processus (ou thread) oracle ait les droits d’accès sur les répertoires/fichiers visés. Auquel cas, une jolie exception du type ACCESS_DENIED en cas d’accès refusé.

 
Articles de cette rubrique
  1. Manipuler des fichiers en PL/SQL
    16 octobre 2004

  2. Planifier des Jobs (calculs des stats par exemple)
    17 novembre 2004