Dans les environnements architecturés sous Oracle, le package UTL_FILE s’est imposé comme un standard de fait pour la manipulation de fichiers externes (texte, CSV ou logs) directement depuis le code de la base de données.
Lorsqu’une équipe technique entreprend une migration vers PostgreSQL, elle est rapidement confrontée à un constat : le langage natif PL/pgSQL ne propose pas d’équivalent strict permettant cette granularité de flux (la lecture et l’écriture ligne par ligne) de manière totalement intégrée.
Même si l’extension communautaire Orafce permet de retrouver la plupart de ces fonctionnalités, son déploiement ajoute un socle de dépendances Oracle extrêmement large (plusieurs fonctions de compatibilité) qui s’avère bien souvent disproportionné si votre unique besoin est d’exporter occasionnellement un reporting.
C’est pourquoi, dans mes récents projets, j’ai fait le choix de concevoir une extension autonome nommée pg_utl_file. Développée en C, elle intègre la philosophie d’UTL_FILE tout en corrigeant ses défauts historiques.
Voici un retour d’expérience sur les concepts, les limites d’Oracle et la manière d’outiller PostgreSQL de façon performante et sécurisée.

Historiquement, le package UTL_FILE s’est imposé dans l’écosystème Oracle comme la solution native pour permettre au moteur de base de données de communiquer de manière fluide avec le système de fichiers (lecture et écriture de textes), typiquement via des objets sécurisés appelés DIRECTORY. Cela couvrait tous les besoins d’exportation, d’intégration de données et de journalisation.
Toutefois, reproduire cette flexibilité et accorder à un moteur transactionnel (comme PostgreSQL) un accès direct au système de fichiers matériel soulève trois défis capitaux en matière de sécurité et de système, qu’il faut rigoureusement encadrer :

1- La gestion de la mémoire et des descripteurs (File Descriptors) : Un descripteur de fichier est une ressource finie du système (OS). Si le moteur PostgreSQL ouvre un fichier physique et qu’une procédure SQL échoue brutalement avant l’instruction de fermeture, le serveur doit absolument garantir que ce descripteur est libéré en arrière-plan. Sans ce mécanisme, le système d’exploitation s’asphyxierait inexorablement (Too many open files).

2- La cohérence et l’isolation transactionnelle (ACID) : Contrairement aux lignes d’une table relationnelle, l’écriture d’une chaîne de texte physique sur un disque dur échappe naturellement aux clauses de ROLLBACK. Il faut donc concevoir une architecture garantissant que les manipulations I/O restent prévisibles et s’interruptent net lors de l’annulation d’une transaction.

3- Le cadre de sécurité et les risques d’usurpation (Path Traversal) : Par définition, le service de base de données tourne avec les droits très élevés de l’utilisateur système postgres. Si une procédure permet à un développeur d’injecter des motifs de navigation (de type ../../etc/hôtes), l’intégrité du serveur est compromise. Le cadre de sécurité sous PostgreSQL doit donc exiger des contrôles inviolables limitant l’accès aux seuls répertoires validés par l’administration.

L’idée de concevoir pg_utl_file est de reproduire fidèlement l’ergonomie globale d’Oracle, tout en tirant parti du noyau C de PostgreSQL pour pallier les défauts évoqués ci-dessus.

Voici les grands principes de l’architecture :

• Sécurité intégrée par Dictionnaire de données : Plutôt que de reposer sur un DIRECTORY propriétaire, l’extension s’appuie sur une table de configuration classique (utl_file_directories). Seuls les rôles DBA peuvent insérer des chemins, et de simples droits GRANT SELECT permettent d’octroyer cet usage aux divers utilisateurs applicatifs.

• La Gestion « Zero-Leak » de la mémoire : La couche logicielle C exploite les callbacks d’événements transactionnels de PostgreSQL. En d’autres termes, si la connexion crashe ou exécute un ROLLBACK, le noyau PostgreSQL notifie pg_utl_file qui prend soin de fermer automatiquement tous les flux restés en suspens en mémoire. Les fuites sont donc impossibles.

• Blocage formel des traversées de répertoires : Toute détection du motif ../ dans l’extension est rejetée délibérément avant de relayer l’appel noyau au système de fichiers (OS).

Le dictionnaire des procédures PL/pgSQL

utl_file_fopen(location text, filename text, open_mode text)

Initialise le fichier demandé et génère un pointeur abstrait sécurisé.

• location : L’identifiant logique du dossier configuré par le DBA (ex: 'EXPORT_DIR').
• filename : Le nom du fichier cible (ex: 'reporting_q1.csv').
• open_mode :
  • 'r' : Lecture seule.
  • 'w' : Écriture (crée un nouveau fichier ou détruit le contenu de l’éventuel fichier existant).
  • 'a' : Ajout (Append, préserve le texte existant en insérant à la toute fin).

utl_file_put_line(file utl_file_type, buffer text, autoflush boolean)

Inscrit une chaîne de caractères suivie d’un retour à la ligne natif du système d’exploitation.

• file : Le pointeur rattaché, fourni précédemment par fopen.
• buffer : Le texte cible à inscrire.
• autoflush (Optionnel, boolean valant false par défaut) : Indique si le buffer mémoire doit être forcé (« flush ») immédiatement sur le disque matériel (très efficace pour de l’audit et des logs vitaux en temps réel).

utl_file_get_line(file utl_file_type)

Renvoie de manière incrémentale la prochaine ligne du fichier en mémoire.

• Comportement natif : Dès que l’on dépasse la fin du fichier (EOF), la fonction lève volontairement l’exception standard logicielle d’absence de données (no_data_found). Les développeurs peuvent donc simplement transposer sans effort leurs boucles EXCEPTION WHEN no_data_found THEN.

utl_file_fflush(file utl_file_type)

Opère un flush mémoire explicite sur un pointeur fichier distinct.

utl_file_fclose(file utl_file_type)

Exige la fermeture immédiate du flux avec l’OS pour libérer le descripteur associé au processus serveur.

utl_file_is_open(file utl_file_type)

Un simple booléen permettant d’ausculter si un pointeur est actuellement actif ou révoqué pendant l’exécution d’une routine asynchrone pointue.

Conclusion

S’affranchir d’Oracle pour intégrer PostgreSQL est un voyage architectural qui interroge régulièrement l’outillage de la base et de l’administration.

Grâce à l’extensibilité quasi infinie des bibliothèques C natives de PostgreSQL, reproduire un besoin ultra-ciblé tel que UTL_FILE devient un atout considérable. Cette approche allège drastiquement les dépendances de vos instances en garantissant une intégrité mémoire (Anti-FD leak) et des garde-fous de cybersécurité que des bases standards commerciales de longue date ne posent pas systématiquement d’elles-mêmes.

L’implémentation algorithmique de cette extension repose sur l’API système AllocateFile.