Oracle PL/SQL


précédentsommairesuivant

8. Le paquetage UTL_FILE

Les procédures et fonctions de ce paquetage vous permettent de lire et écrire dans des fichiers texte situés dans les répertoires du système d'exploitation.
Elles sont exécutées par le noyau Oracle, donc sur la partie serveur.

Pour bénéficier des mêmes fonctionnalités sur la partie cliente, il faut utiliser le paquetage TEXT_IO.

Sous Oracle 9i, l'accès à ces fichiers est restreint aux répertoires(DIRECTORIES) déclarés au niveau du serveur.

Dans les version antérieures, la description des répertoires autorisés était spécifiée dans le paramètre UTL_FILE_DIR du fichier d'initialisation INIT.ORA, dont toute modification imposait évidement un arrêt et relance de la base


Pour créer un répertoire, il faut avoir le privilège CREATE DIRECTORY, accordé par défaut aux seuls schémas SYS et SYSTEM

Pour accéder en lecture aux répertoires déclarés dans les répertoires, l'utilisateur doit avoir le droit READ sur le ou les répertoires

Pour accéder en écriture aux répertoires déclarés dans les répertoires, l'utilisateur doit avoir le droit WRITE sur le ou les répertoires


Créons sous l'utilisateur SYSTEM deux répertoires pour les fichiers en entrée et ceux en sortie

 
Sélectionnez

SQL> CREATE DIRECTORY FICHIERS_IN AS 'd:\fichiers\in' ;

Répertoire créé.

SQL> GRANT READ ON DIRECTORY FICHIERS_IN TO PUBLIC ;

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

SQL> CREATE DIRECTORY FICHIERS_OUT AS 'd:\fichiers\out' ;

Répertoire créé.

SQL> GRANT READ, WRITE ON DIRECTORY FICHIERS_OUT TO PUBLIC ;

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

Le répertoire contenant les fichiers en entrée se voit attribuer le droit de lecture (READ) pour tous les utilisateurs

Le répertoire contenant les fichiers en sortie se voit attribuer les droits de lecture (READ) et écriture (WRITE) pour tous les utilisateurs

Il n'y a pas de système de récursivité dans les sous-répertoires
CREATE DIRECTORY FICHIERS_IN AS 'd:\fichiers\in' n'autorise pas l'accès aux éventuels sous-répertoires

8.1. Procédures et fonctions du paquetage

8.1.1. Liste des procédures et fonctions version 8i

  • Fonction IS_OPEN Teste si le pointeur de fichier se réfère à un fichier ouvert
  • Procédure FCLOSE Fermeture d'un fichier
  • Procédure FCLOSE_ALL Fermeture de tous les fichiers ouverts
  • Fonction FOPEN Ouverture d'un fichier pour lecture ou écriture
  • Procédure FFLUSH Ecriture physique des tampons sur le disque
  • Procédure GET_LINE Lecture d'une ligne depuis un fichier ouvert
  • Procédure PUT Ecriture d'une ligne (sans caractère fin de ligne) dans un fichier ouvert
  • Procédure PUT_LINE Ecriture d'une ligne (avec caractère fin de ligne) dans un fichier ouvert
  • Procédure PUTF Ecriture d'une ligne formatée
  • Procédure PUTF Ecriture d'une ligne formatée

8.1.2. Liste des procédures et fonctions version 9i

  • Fonction IS_OPEN Test si le pointeur de fichier se réfère à un fichier ouvert
  • Procédure FCLOSE Fermeture d'un fichier
  • Procédure FCLOSE_ALL Fermeture de tous les fichiers ouverts
  • Fonction FCOPY Copie d'un fichier sur le disque
  • Fonction FOPEN Ouverture d'un fichier pour lecture ou écriture
  • Fonction FOPEN_NCHAR Ouverture d'un fichier en unicode pour lecture ou écriture
  • Procédure FFLUSH Ecriture physique des tampons sur le disque
  • Procédure FGETATTR Lecture des attributs d'un fichier
  • Fonction FGETPOS Retourne la position du pointeur de lecture/écriture
  • Fonction FREMOVE Suppression d'un fichier sur le disque
  • Fonction FRENAME Renommage d'un fichier
  • Fonction FSEEK Déplacement du pointeur de lecture/écriture dans le fichier
  • Procédure GET_LINE Lecture d'une ligne depuis un fichier ouvert
  • Procédure GET_LINE_NCHAR Lecture d'une ligne en unicode depuis un fichier ouvert
  • Procédure GET_RAW Lecture d'une ligne de type RAW depuis un fichier ouvert avec ajustement du pointeur de position
  • Procédure NEW_LINE Ecriture d'un caractère fin de ligne dans un fichier ouvert
  • Procédure PUT Ecriture d'une ligne (sans caractère fin de ligne) dans un fichier ouvert
  • Procédure PUT_NCHAR Ecriture d'une ligne en unicode (sans caractère fin de ligne) dans un fichier ouvert
  • Procédure PUT_RAW Ecriture d'une ligne de type RAW dans un fichier ouvert
  • Procédure PUT_LINE Ecriture d'une ligne (avec caractère fin de ligne) dans un fichier ouvert
  • Procédure PUT_LINE_NCHAR Ecriture d'une ligne en unicode (avec caractère fin de ligne) dans un fichier ouvert
  • Procédure PUTF Ecriture d'une ligne formatée
  • Procédure PUTF_NCHAR Ecriture d'une ligne en unicode formatée

8.2. Syntaxe des procédures et fonctions

8.2.1. IS_OPEN

Vérification de l'ouverture d'un fichier

UTL_FILE.IS_OPEN (
pointeur IN FILE_TYPE)
RETURN BOOLEAN

pointeur représente une variable de type UTL_FILE.FILE_TYPE préalablement obtenue par un appel aux fonctions FOPEN() ou FOPEN_NCHAR()

La fonction retourne TRUE si le fichier est ouvert, sinon FALSE

8.2.2. FCLOSE

Fermeture d'un fichier

UTL_FILE.FCLOSE (


pointeur IN OUT FILE_TYPE)

pointeur représente une variable de type UTL_FILE.FILE_TYPE préalablement obtenue par un appel aux fonctions FOPEN() ou FOPEN_NCHAR()

Exceptions générées
WRITE_ERROR
INVALID_FILEHANDLE

8.2.3. FCLOSE_ALL

Fermeture de tous les fichiers ouverts de la session en cours

UTL_FILE.FCLOSE_ALL

Exceptions générées
WRITE_ERROR

8.2.4. FCOPY

Copie d'un fichier

UTL_FILE.FCOPY (
dir_source IN VARCHAR2,
fichier_source IN VARCHAR2,
dir_cible IN VARCHAR2,
fichier_cible IN VARCHAR2,
ligne_debut IN PLS_INTEGER DEFAULT 1,
ligne_fin IN PLS_INTEGER DEFAULT NULL)

dir_source représente le répertoire Oracle du fichier source
fichier_source représente le nom du fichier source avec son extension
dir_cible représente le répertoire de destination
fichier_cible représente le nom du fichier de destination
ligne_debut représente le numéro de ligne de début de copie (par défaut 1, début du fichier)
ligne_fin représente le numéro de ligne de fin de copie (par défaut NULL, fin du fichier)

Cette fonction permet de créer un fichier à partir d'un autre en recopiant tout ou partie du fichier source.

8.2.5. FOPEN

Ouverture d'un fichier

Oracle 8i, 9i
UTL_FILE.FOPEN (
repertoire IN VARCHAR2,
fichier IN VARCHAR2,
mode IN VARCHAR2,
taille_ligne_maxi IN BINARY_INTEGER)
RETURN UTL_FILE.FILE_TYPE

repertoire représente le chemin d'accès (8i) ou le répertoire (9i)
fichier représente le nom du fichier avec son extension
mode représente le mode d'ouverture du fichier qui peut prendre l'une des trois valeurs suivantes

  • R le fichier est ouvert en lecture (Read)
  • W le fichier est ouvert en écriture (Write)
  • A le fichier est ouvert en ajout (Append)

taille_ligne_maxi représente la taille maximum en octets d'une ligne lue ou écrite. La plage des valeurs acceptables est comprise entre 1 et 32767 (par défaut environ 1000)

La fonction retourne un enregistrement de type UTL_FILE.FILE_TYPE

Exceptions générées
Oracle 8i, 9i
INVALID_PATH: Nom de répertoire ou de fichier invalide.
INVALID_MODE: Mode d'ouverture invalide.
INVALID_OPERATION: Le fihcier ne peut être ouvert.
INVALID_MAXLINESIZE: La valeur de taille_ligne_maxi est trop grande ou trop petite.

8.2.6. FOPEN_NCHAR

(9i)
Ouverture d'un fichier

Identique à la fonction FOPEN, mais traite des fichiers en mode unicode

UTL_FILE.FOPEN_NCHAR (
repertoire IN VARCHAR2,
fichier IN VARCHAR2,
mode IN VARCHAR2,
taille_ligne_maxi IN BINARY_INTEGER)
RETURN UTL_FILE.FILE_TYPE

8.2.7. FFLUSH

Ecriture physique des tampons sur le disque

UTL_FILE.FFLUSH (
pointeur IN FILE_TYPE)

pointeur représente une variable de type UTL_FILE.FILE_TYPE préalablement obtenue par un appel aux fonctions FOPEN() ou FOPEN_NCHAR()

Cette fonction permet de forcer l'écriture du tampon sur le disque

Exceptions générées
INVALID_FILEHANDLE
INVALID_OPERATION
WRITE_ERROR

8.2.8. FGETATTR

(9i)
Lecture des attributs d'un fichier

UTL_FILE.FGETATTR(
repertoire IN VARCHAR2,
fichier IN VARCHAR2,
exists OUT BOOLEAN,
taille_fichier OUT NUMBER,
taille_bloc OUT NUMBER)

repertoire représente le répertoire Oracle
fichier représente le nom du fichier avec son extension
exists vaut TRUE si le fichier existe, sinon FALSE
taille_fichier représente la taille du fichier en octets
taille_bloc représente la taille d'un bloc système en octets

Cette fonction teste l'existence d'un fichier et récupère, dans l'affirmative, la taille du fichier et la taille du bloc système

8.2.9. FGETPOS

Position du pointeur de lecture/écriture du fichier

UTL_FILE.FGETPOS (
pointeur IN file_type)
RETURN PLS_INTEGER

pointeur représente une variable de type UTL_FILE.FILE_TYPE préalablement obtenue par un appel aux fonctions FOPEN() ou FOPEN_NCHAR()

Cette fonction retourne la position en octets actuelle de pointeur du fichier ouvert

8.2.10. FREMOVE

(9i)
Suppression d'un fichier sur disque

UTL_FILE.FREMOVE (
repertoire IN VARCHAR2,
fichier IN VARCHAR2)

repertoire représente le répertoire Oracle
fichier représente le nom du fichier avec son extension

8.2.11. FRENAME

(9i)
Renommage d'un fichier sur disque

UTL_FILE.FRENAME (
dir_source IN VARCHAR2,
fichier_source IN VARCHAR2,
dir_cible IN VARCHAR2,
fichier_cible IN VARCHAR2,
remplacer IN BOOLEAN DEFAULT FALSE)

dir_source représente le répertoire Oracle du fichier source
fichier_source représente le nom du fichier source avec son extension
dir_cible représente le répertoire de destination
fichier_cible représente le nom du fichier de destination
remplacer positionné à TRUE permet d'écraser un fichier existant

Cette fonction permet de renommer un fichier avec possibilité de déplacement, comme de la commande Unix mv

8.2.12. FSEEK

(9i)
Positionnement du pointeur de lecture/écriture dans le fichier

UTL_FILE.FSEEK (
pointeur IN utl_file.file_type,
déplacement_absolu IN PL_INTEGER DEFAULT NULL,
déplacement_relatif IN PLS_INTEGER DEFAULT NULL)

pointeur représente une variable de type UTL_FILE.FILE_TYPE préalablement obtenue par un appel aux fonctions FOPEN() ou FOPEN_NCHAR()
déplacement_absolu représente l'octet du fichier sur lequel on veut pointer
déplacement_relatif représente un déplacement vers l'avant ou vers l'arrière par rapport à la position courante

si déplacement_relatif est positif, le déplacement s'effectue vers la fin du fichier
si déplacement_relatif est négatif, le déplacement s'effectue vers le début du fichier

8.2.13. GET_LINE

Lecture d'une ligne depuis un fichier

Oracle 8i
UTL_FILE.GET_LINE (
pointeur IN FILE_TYPE,
tampon OUT VARCHAR2);

Oracle 9i
UTL_FILE.GET_LINE (
pointeur IN FILE_TYPE,
tampon OUT VARCHAR2,
longueur IN NUMBER,
len IN PLS_INTEGER DEFAULT NULL)

pointeur représente une variable de type UTL_FILE.FILE_TYPE préalablement obtenue par un appel aux fonctions FOPEN() ou FOPEN_NCHAR()
tampon représente la variable destinée à recevoir les données lues
longueur représente le nombre maximum d'octets à lire
len représente le nombre d'octets à lire. Par défaut il est à NULL ce qui signifie qu'il vaut la taille maximum d'un RAW

La variable tampon doit être suffisamment dimensionnée pour accueillir la ligne lue depuis le fichier sous peine de générer l'exception VALUE_ERROR
longueur ne peut pas excéder la valeur définie par le paramètre taille_ligne_maxi de la fonction FOPEN()
Lorsque la lecture ne ramène aucune donnée (fin de fichier), l'exception NO_DATA_FOUND est générée

Exceptions générées
INVALID_FILEHANDLE
INVALID_OPERATION
READ_ERROR
NO_DATA_FOUND
VALUE_ERROR

8.2.14. GET_LINE_NCHAR

(9i)
Lecture d'une ligne en unicode depuis un fichier

UTL_FILE.GET_LINE_NCHAR (
pointeur IN FILE_TYPE,
tampon OUT NVARCHAR2,
len IN PLS_INTEGER DEFAULT NULL)

pointeur représente une variable de type UTL_FILE.FILE_TYPE préalablement obtenue par un appel aux fonctions FOPEN() ou FOPEN_NCHAR()
tampon représente la variable de type NVARCHAR2 retournée par la procédure
len représente le nombre d'octets à lire. Par défaut il est à NULL ce qui signifie qu'il vaut la taille maximum d'un RAW

Exceptions générées
INVALID_FILEHANDLE
INVALID_OPERATION
READ_ERROR
NO_DATA_FOUND
VALUE_ERROR

8.2.15. GET_RAW

(9i)
Lecture d'une ligne dans une variable RAW depuis un fichier

UTL_FILE.GET_RAW (
pointeur IN utl_file.file_type,
tampon OUT NOCOPY RAW,
len IN PLS_INTEGER DEFAULT NULL)

pointeur représente une variable de type UTL_FILE.FILE_TYPE préalablement obtenue par un appel aux fonctions FOPEN() ou FOPEN_NCHAR()
tampon représente la variable de type RAW retournée par la procédure
len représente le nombre d'octets à lire. Par défaut il est à NULL ce qui signifie qu'il vaut la taille maximum d'un RAW

Exceptions générées
INVALID_FILEHANDLE
INVALID_OPERATION
READ_ERROR
NO_DATA_FOUND
VALUE_ERROR

8.2.16. NEW_LINE

Ecriture d'un caractère fin de ligne dans un fichier

UTL_FILE.NEW_LINE (
pointeur IN FILE_TYPE,
nombre_lignes IN NATURAL := 1)

pointeur représente une variable de type UTL_FILE.FILE_TYPE préalablement obtenue par un appel aux fonctions FOPEN() ou FOPEN_NCHAR()
nombre_lignes représente le nombre de terminateurs fin de ligne que l'on souhaite écrire dans le fichier (par défaut 1)

Exceptions générées
INVALID_FILEHANDLE
INVALID_OPERATION
WRITE_ERROR

8.2.17. PUT

Ecriture d'une ligne (sans caractère fin de ligne) dans un fichier

UTL_FILE.PUT (
pointeur IN FILE_TYPE,
tampon IN VARCHAR2)

pointeur représente une variable de type UTL_FILE.FILE_TYPE préalablement obtenue par un appel aux fonctions FOPEN() ou FOPEN_NCHAR()
tampon représente la variable destinée à contenir les données à écrire

Exceptions générées
INVALID_FILEHANDLE
INVALID_OPERATION
WRITE_ERROR

8.2.18. PUT_NCHAR

(9i)
Ecriture d'une ligne en unicode (sans caractère fin de ligne) dans un fichier

UTL_FILE.PUT_INCHAR (
pointeur IN FILE_TYPE,
tampon IN NVARCHAR2)

pointeur représente une variable de type UTL_FILE.FILE_TYPE préalablement obtenue par un appel aux fonctions FOPEN() ou FOPEN_NCHAR()
tampon représente la variable de type NVARCHAR2 destinée à contenir les données à écrire

Exceptions générées
INVALID_FILEHANDLE
INVALID_OPERATION
WRITE_ERROR

8.2.19. PUT_RAW

(9i)
Ecriture d'une ligne de type RAW dans un fichier

UTL_FILE. PUT_RAW (
pointeur IN utl_file.file_type,
tampon IN RAW,
autoflush IN BOOLEAN DEFAULT FALSE)


pointeur représente une variable de type UTL_FILE.FILE_TYPE préalablement obtenue par un appel aux fonctions FOPEN() ou FOPEN_NCHAR()
tampon représente la variable de type RAW destinée à contenir les données à écrire
autoflush, positionné à TRUE permet de forcer l'écriture du tampon sur disque.

Exceptions générées
INVALID_FILEHANDLE
INVALID_OPERATION
WRITE_ERROR

8.2.20. PUT_LINE

Ecriture d'une ligne (avec caractère fin de ligne) dans un fichier

Oracle 8i
UTL_FILE.PUT_LINE (
pointeur IN FILE_TYPE,
tampon IN VARCHAR2)

Oracle 9i
UTL_FILE.PUT_LINE (
pointeur IN FILE_TYPE,
tampon IN VARCHAR2,
autoflush IN BOOLEAN DEFAULT FALSE)

pointeur représente une variable de type UTL_FILE.FILE_TYPE préalablement obtenue par un appel aux fonctions FOPEN() ou FOPEN_NCHAR()
tampon représente la variable destinée à contenir les données à écrire
autoflush, positionné à TRUE permet de forcer l'écriture du tampon sur disque.

Un caractère fin de ligne est ajouté en fin de tampon

8.2.21. PUT_LINE_NCHAR

(9i)
Ecriture d'une ligne en unicode (avec caractère fin de ligne) dans un fichier

UTL_FILE.PUT_LINE_NCHAR (
pointeur IN FILE_TYPE,
tampon IN NVARCHAR2,
autoflush IN BOOLEAN DEFAULT FALSE)

pointeur représente une variable de type UTL_FILE.FILE_TYPE préalablement obtenue par un appel aux fonctions FOPEN() ou FOPEN_NCHAR()
tampon représente la variable de type NVARCHAR2 destinée à contenir les données à écrire
autoflush, positionné à TRUE permet de forcer l'écriture du tampon sur disque.

Un caractère fin de ligne est ajouté en fin de tampon

8.2.22. PUTF

Ecriture d'une ligne formatée dans un fichier

UTL_FILE.PUTF (
pointeur IN FILE_TYPE,
format IN VARCHAR2,
[arg1 IN VARCHAR2 DEFAULT NULL,
. . .
arg5 IN VARCHAR2 DEFAULT NULL])

pointeur représente une variable de type UTL_FILE.FILE_TYPE préalablement obtenue par un appel aux fonctions FOPEN() ou FOPEN_NCHAR()
format représente une chaîne pouvant inclure les caractères %s et \n
arg1.. arg5 représentent jusqu'à 5 paramètres dont chaque valeur remplace un caractère %s du format donné.

Cette fonction ressemble à une version limitée de fprintf() du langage C

Exceptions générées
INVALID_FILEHANDLE
INVALID_OPERATION
WRITE_ERROR

8.2.23. PUTF_NCHAR

Ecriture d'une ligne unicode formatée dans un fichier

UTL_FILE.PUTF_NCHAR (
pointeur IN FILE_TYPE,
format IN NVARCHAR2,
[arg1 IN NVARCHAR2 DEFAULT NULL,
. . .
arg5 IN NVARCHAR2 DEFAULT NULL])

pointeur représente une variable de type UTL_FILE.FILE_TYPE préalablement obtenue par un appel aux fonctions FOPEN() ou FOPEN_NCHAR()
format représente une chaîne pouvant inclure les caractères %s et \n
arg1.. arg5 représentent jusqu'à 5 paramètres dont chaque valeur remplace un caractère %s du format donné.

Exceptions générées
INVALID_FILEHANDLE
INVALID_OPERATION
WRITE_ERROR

8.3. Exceptions générées par le paquetage

8.3.1. Exceptions de la version 8i

  • INVALID_PATH La directorie ou le nom de fichier est invalide
  • INVALID_MODE Mode d'ouverture invalide pour la fonction FOPEN
  • INVALID_FILEHANDLE Pointeur de fichier invalide
  • INVALID_OPERATION Le fichier ne peut être ouvert ou manipulé
  • READ_ERROR Erreur système pendant une opération de lecture
  • WRITE_ERROR Erreur système pendant une opération d'écriture
  • INTERNAL_ERROR Erreur PL/SQL non spécifiée

8.3.2. Exceptions de la version 9i

  • INVALID_PATH La directorie ou le nom de fichier est invalide
  • INVALID_MODE Mode d'ouverture invalide pour la fonction FOPEN
  • INVALID_FILEHANDLE Pointeur de fichier invalide
  • INVALID_OPERATION Le fichier ne peut être ouvert ou manipulé
  • READ_ERROR Erreur système pendant une opération de lecture
  • WRITE_ERROR Erreur système pendant une opération d'écriture
  • INTERNAL_ERROR Erreur PL/SQL non spécifiée
  • CHARSETMISMATCH Utilisation de fonctions non NCHAR après une ouverture avec FOPEN_NCHAR
  • FILE_OPEN L'opération a échoué car le fichier est ouvert
  • INVALID_MAXLINESIZE La taille MAXLINESIZE pour la fonction FOPEN doit être entre 1 to 32767
  • INVALID_FILENAME Nom de fichier invalide
  • ACCESS_DENIED Vous n'avez pas les droits d'accès au fichier
  • INVALID_OFFSET Le déplacement déclaré dans la fonction FSEEK() doit être supérieur à 0 et inférieur à la taille du fichier
  • DELETE_FAILED L'opération de suppression à échoué
  • RENAME_FAILED L'opération de renommage a échoué

8.4. Exemples concrets

  • Ouverture d'un fichier en lecture et écriture du contenu dans un autre
 
Sélectionnez

Declare
  -- Noms des fichiers --
  LC$Fic_in   Varchar2(128) := 'EMP.TXT' ;       -- a adapter sur votre configuration
  LC$Fic_out  Varchar2(128) := 'EMP2.TXT' ;      -- a adapter sur votre configuration
  -- Noms des répertoires --
  LC$Dir_in   Varchar(30)   := 'FICHIERS_IN';    -- a adapter sur votre configuration
  LC$Dir_out  Varchar(30)   := 'FICHIERS_OUT' ;  -- a adapter sur votre configuration
  -- Pointeurs de fichier --
  LF$FicIN	UTL_FILE.FILE_TYPE ;
  LF$FicOUT	UTL_FILE.FILE_TYPE ;
  -- Tampon de travail --  
  LC$Ligne	Varchar2(32767) ;  
  -- Message --
  LC$Msg    Varchar2(256) ;
  -- Exception --
  LE$Fin    Exception ;
Begin

  -- Ouverture du fichier en entrée
  Begin
    LF$FicIN := UTL_FILE.FOPEN( LC$Dir_in, LC$Fic_in, 'R', 32764 ) ;
  Exception
    When OTHERS Then
	LC$Msg := SQLERRM || ' [' || LC$Dir_in || '] -> ' || LC$Fic_in;
	Raise LE$Fin ;
  End ;
  
  -- Ouverture du fichier en sortie
  Begin
    LF$FicOUT := UTL_FILE.FOPEN( LC$Dir_out, LC$Fic_out, 'W', 32764 ) ;
  Exception
    When OTHERS Then
	LC$Msg := SQLERRM || ' [' || LC$Dir_out || '] -> ' || LC$Fic_out;
	Raise LE$Fin ;
  End ;	
  
  -- Traitement --
  Begin
     Loop
	-- lecture du fichier en entrée --
	UTL_FILE.GET_LINE( LF$FicIN,  LC$Ligne ) ;
	-- écriture du fichier en sortie --
	UTL_FILE.PUT_LINE( LF$FicOUT, LC$Ligne ) ;
     End loop ;
  Exception
     When NO_DATA_FOUND Then -- Fin du fichier en entrée
        -- Fermeture des fichiers --
	UTL_FILE.FCLOSE( LF$FicIN ) ;
	UTL_FILE.FCLOSE( LF$FicOUT ) ;
  End  ;		 		
  
Exception
   When LE$Fin Then
     UTL_FILE.FCLOSE_ALL ;
     RAISE_APPLICATION_ERROR( -20100, LC$Msg ) ;
End ;	 	           
  • Extraction d'une table dans un fichier

Voici le code d'une procédure qui permet d'extraire le contenu d'une table dans un fichier


Cette procédure a besoin des 3 premiers paramètres pour fonctionner

PC$Table reçoit le nom d'une table Oracle
PC$Fichier reçoit le nom du fichier de sortie
PC$Repertoire reçoit le nom du chemin (8i) ou de la directorie (9i)
PC$Separateur reçoit le caractère de séparation voulu (défaut ,)
PC$DateFMT reçoit le format des dates (défaut : DD/MM/YYYY)
PC$Where reçoit une éventuelle clause WHERE
PC$Order reçoit une éventuelle clause ORDER BY

le paramètre PC$Entetes permet les actions suivantes

  • positionné à O, il indique une sortie des lignes de la table avec une ligne d'entête
  • positionné à I, il indique la génération des ordres INSERT pour chaque ligne
  • différent de O et I, il indique une sortie des lignes de la table sans ligne d'entête
 
Sélectionnez

CREATE OR REPLACE PROCEDURE Extraction_Table
	(
		PC$Table      in Varchar2,                      -- Nom de la table a extraire
		PC$Fichier    in Varchar2,                      -- Nom du fichier de sortie
		PC$Repertoire in Varchar2,                      -- Nom du directory de sortie
		PC$Separateur in Varchar2 Default ',',          -- Caractere de separation
		PC$Entetes    in Varchar2 Default 'O',          -- Affichage de l'entete des colonnes
		PC$DateFMT    in Varchar2 Default 'DD/MM/YYYY', -- Format des dates
		PC$Where      in Varchar2 Default Null,         -- Clause Where de filtrage
		PC$Order      in Varchar2 Default Null          -- Colonne de tri
	) IS


LF$Fichier  UTL_FILE.FILE_TYPE ;
LC$Ligne    Varchar2(32767) ;
LI$I        Integer ;
LC$DateFMT  Varchar2(40) := '''' || PC$DateFMT || '''' ;


TYPE REFCUR1 IS REF CURSOR ;
cur		REFCUR1;


-- Colonnes de la table --
  CURSOR C_COLTAB ( PC$Tab IN VARCHAR2 ) IS
  SELECT
  	COLUMN_NAME,
	DATA_TYPE
  FROM
  	USER_TAB_COLUMNS
  WHERE
  	TABLE_NAME = PC$Tab
  AND
  	DATA_TYPE IN ('CHAR','VARCHAR2','NUMBER','DATE','FLOAT')
  ;

LC$Separateur	Varchar2(2) := PC$Separateur ;
LC$Requete	Varchar2(10000) ;
LC$Desc		Varchar2(10000) ;
LC$SQLW		VARCHAR2(10000):= 'SELECT ';
LC$Col		VARCHAR2(256);


-----------------------------------------
-- Ouverture d'un fichier d'extraction --
-----------------------------------------
FUNCTION Ouvrir_fichier
	(
		PC$Dir in Varchar2,
		PC$Nom_Fichier in Varchar2
	) RETURN UTL_FILE.FILE_TYPE
IS
  Fichier	UTL_FILE.FILE_TYPE ;
  LC$Msg	Varchar2(256);

Begin

  Fichier := UTL_FILE.FOPEN( PC$Dir, PC$Nom_Fichier, 'W', 32764 ) ;

  If not UTL_FILE.IS_OPEN( Fichier ) Then
	LC$Msg := 'Erreur ouverture du fichier (' || PC$Dir || ') ' || PC$Nom_Fichier ;
  	RAISE_APPLICATION_ERROR( -20100, LC$Msg ) ;
  End if ;

  Return( Fichier ) ;

Exception

When UTL_FILE.INVALID_PATH Then
	LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'File location is invalid.';
	RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.INVALID_MODE Then
	LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'The open_mode parameter in FOPEN is invalid.';
	RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.INVALID_FILEHANDLE Then
	LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'File handle is invalid.';
	RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.INVALID_OPERATION	Then
	LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'File could not be opened or operated on as requested.';
	RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.READ_ERROR	Then
	LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'Operating system error occurred during the read operation.';
	RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.WRITE_ERROR Then
	LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'Operating system error occurred during the write operation.';
	RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.INTERNAL_ERROR then
	LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'Unspecified PL/SQL error';
	RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
---------------------------------------------------------------
-- Les exceptions suivantes sont spécifiques à la version 9i --
-- A mettre en commentaire pour une version antérieure       --
---------------------------------------------------------------
When UTL_FILE.CHARSETMISMATCH Then
	LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'A file is opened using FOPEN_NCHAR,'
	  || ' but later I/O operations use nonchar functions such as PUTF or GET_LINE.';
	RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.FILE_OPEN Then
	LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'The requested operation failed because the file is open.';
	RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.INVALID_MAXLINESIZE Then
	LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'The MAX_LINESIZE value for FOPEN() is invalid;'
	  || ' it should be within the range 1 to 32767.';
	RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.INVALID_FILENAME Then
	LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'The filename parameter is invalid.';
	RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.ACCESS_DENIED Then
	LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'Permission to access to the file location is denied.';
	RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.INVALID_OFFSET Then
	LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'The ABSOLUTE_OFFSET parameter for FSEEK() is invalid;'
	  ||' it should be greater than 0 and less than the total number of bytes in the file.';
	RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.DELETE_FAILED Then
	LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'The requested file delete operation failed.';
	RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.RENAME_FAILED Then
	LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'The requested file rename operation failed.';
	RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
-----------------------------------------------------------------
-- Les exceptions précédentes sont spécifiques à la version 9i --
--     mettre en commentaire pour une version antérieure       --
-----------------------------------------------------------------
When others Then
	LC$Msg := 'Erreur : ' || To_char( SQLCODE ) || ' sur ouverture du fichier ('
	   || PC$Dir || ') ' || PC$Nom_Fichier ;
	RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;

End Ouvrir_fichier ;

Begin

  -- Ouverture du fichier --
  LF$Fichier := Ouvrir_fichier( PC$Repertoire, PC$Fichier ) ;

  -- Affichage des entetes de colonne ? --
  If Upper(PC$Entetes) = 'O' Then
	LI$I := 1 ;
	For COLS IN C_COLTAB( PC$Table ) Loop
	   If LI$I = 1 Then
	      LC$Ligne := LC$Ligne || COLS.COLUMN_NAME ;
	   Else
	      LC$Ligne := LC$Ligne || LC$Separateur || COLS.COLUMN_NAME ;
	   End if ;
	   LI$I := LI$I + 1 ;
	End loop ;
	-- Ecriture ligne entetes --
	UTL_FILE.PUT_LINE( LF$Fichier, LC$Ligne ) ;
  ElsIf Upper(PC$Entetes) = 'I' Then
    LC$Separateur := ',' ;
    LC$Desc := 'INSERT INTO ' || PC$Table || ' (' ;
    LI$I := 1 ;
    For COLS IN C_COLTAB( PC$Table ) Loop
      If LI$I = 1 Then
	     LC$Desc := LC$Desc || COLS.COLUMN_NAME ;
      Else
	     LC$Desc := LC$Desc || LC$Separateur || COLS.COLUMN_NAME ;
      End if ;
      LI$I := LI$I + 1 ;
    End loop ;
    LC$Desc := LC$Desc || ' ) VALUES (' ;
  End if ;

  -- Construction de la requete --
  LI$I := 1 ;

  FOR COLS IN C_COLTAB( PC$Table ) LOOP
    IF LI$I > 1 THEN
       LC$SQLW := LC$SQLW || '||' ;
    END IF ;

    If COLS.DATA_TYPE IN ('NUMBER','FLOAT') Then
	    LC$Col := 'Decode(' || COLS.COLUMN_NAME || ',NULL, ''NULL'',To_char("'
	       || COLS.COLUMN_NAME || '"))' ;
    ElsIf COLS.DATA_TYPE = 'DATE' Then
       If Upper(PC$Entetes) = 'I' Then
           LC$Col := 'Decode(' || COLS.COLUMN_NAME || ',NULL,''NULL'',''to_date(''''''||'
              || 'To_char("' || COLS.COLUMN_NAME || '",'|| LC$DateFMT ||')' || '||'''''','''|| LC$DateFMT||''')'')' ;
       Else
	       LC$Col := 'To_char("'|| COLS.COLUMN_NAME || '",'|| LC$DateFMT ||')' ;
       End if ;
    Else
       If Upper(PC$Entetes) = 'I' Then
	      LC$Col := 'Decode(' || COLS.COLUMN_NAME || ',NULL, ''NULL'',' || ''''''''''
	       || '|| REPLACE("'|| COLS.COLUMN_NAME || '",CHR(39),CHR(39)||CHR(39))' || '||' || ''''''''')' ;
       Else
	      LC$Col := '"'|| COLS.COLUMN_NAME || '"' ;
       End if ;
    End if ;

    IF LI$I = 1 THEN
       LC$SQLW := LC$SQLW || LC$Col ;
    ELSE
       LC$SQLW := LC$SQLW || '''' || LC$Separateur || '''' || '||' || LC$Col  ;
    END IF ;
    LI$I := LI$I + 1 ;
  END LOOP ;

  LC$Requete := LC$SQLW || ' FROM ' || PC$Table ;

  If PC$Where is not null Then
    -- ajout de la clause WHERE --
    LC$Requete := LC$Requete || ' WHERE ' || PC$Where ;
  End if ;
  If PC$Order is not null Then
    -- ajout de la clause ORDER BY --
    LC$Requete := LC$Requete || ' ORDER BY ' || PC$Order ;
  End if ;

  F_TRACE( LC$Requete, 'T' ) ;
  -- Extraction des lignes --
  Open cur For LC$Requete ;
  Loop
	Fetch cur Into LC$Ligne ;
	Exit when cur%NOTFOUND ;
	-- Ecriture du fichier de sortie --
	If Upper(PC$Entetes) = 'I' Then
	  UTL_FILE.PUT_LINE( LF$Fichier, LC$Desc || LC$Ligne || ' );' ) ;
	Else
	  UTL_FILE.PUT_LINE( LF$Fichier, LC$Ligne ) ;
	End if ;
  End loop ;

  Close cur ;

  -- Fermeture fichier --
  UTL_FILE.FCLOSE( LF$Fichier ) ;

End ;
/

Nous allons maintenant extraire les lignes de la table EMP dans le fichier EMP.TXT

 
Sélectionnez

SQL> execute extraction_table( 'EMP','EMP.TXT','FICHIERS_OUT' ) ;

Procédure PL/SQL terminée avec succès.

dont voici le contenu

EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
7369,SMITH,CLERK,7902,18/12/1980,880,,20
7499,ALLEN,SALESMAN,7698,21/02/1981,1936,300,30
7521,WARD,SALESMAN,7698,23/02/1981,1375,500,30
7566,JONES,MANAGER,7839,03/04/1981,3273,,20
7654,MARTIN,SALESMAN,7698,29/09/1981,1375,1400,30
7698,BLAKE,MANAGER,7839,02/05/1981,3135,,30
7782,CLARK,MANAGER,7839,10/06/1981,2695,,10
7788,SCOTT,ANALYST,7566,20/04/1987,3300,,20
7839,KING,PRESIDENT,,18/11/1981,5500,,10
7844,TURNER,SALESMAN,7698,09/09/1981,1650,0,30
7876,ADAMS,CLERK,7788,24/05/1987,1210,,20
7900,JAMES,CLERK,7698,04/12/1981,1045,,30
7902,FORD,ANALYST,7566,04/12/1981,3300,,20
7934,MILLER,CLERK,7782,24/01/1982,1430,,10
9991,Dupontont,CLERK,,,,,
9992,Duboudin,CLERK,,,,,
9994,Schmoll,CLERK,,,2500,,20


Maintenant, utilisons cette procédure pour générer les ordres INSERT pour les employés ayant le job CLERK dans un fichier EMP.INS

 
Sélectionnez

SQL>  execute extraction_table( 'EMP','EMP.INS','FICHIERS_OUT', ',', 'I', 'JOB = ''CLERK''' ) ;

Procédure PL/SQL terminée avec succès.

dont voici le contenu

INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO ) VALUES (7369,'SMITH','CLERK',7902,to_date('18/12/1980','DD/MM/YYYY'),880,NULL,20 );
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO ) VALUES (7876,'ADAMS','CLERK',7788,to_date('24/05/1987','DD/MM/YYYY'),1210,NULL,20 );
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO ) VALUES (7900,'JAMES','CLERK',7698,to_date('04/12/1981','DD/MM/YYYY'),1045,NULL,30 );
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO ) VALUES (7934,'MILLER','CLERK',7782,to_date('24/01/1982','DD/MM/YYYY'),1430,NULL,10 );
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO ) VALUES (9991,'Dupontont','CLERK',NULL,NULL,NULL,NULL,NULL );
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO ) VALUES (9992,'Duboudin','CLERK',NULL,NULL,NULL,NULL,NULL );
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO ) VALUES (9994,'Schmoll','CLERK',NULL,NULL,2500,NULL,20 );


précédentsommairesuivant

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   

  

Copyright © 2004 SheikYerbouti. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.