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.
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.
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
...
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.
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é.