Comment générer un fichier CSV avec Oracle SQL*Plus?

Vous travaillez sur des bases de données Oracle? Vous utilisez très certainement l'outil Oracle SQL*Plus qui permet d'aller au-delà des requêtes de base de données standard exécutées sous SQL Developer (7864). Avec SQL*Plus vous pouvez faire du traitement par lots, des scripts et des calculs de toute sorte.

Il est même possible, comme on va le voir ensemble ci-dessous, de générer des fichiers plats CSV en utilisant la sortie d'une requête de base de données particulière. Si vous faites du support ERP dans la dimention technique et fonctionnelle, l'intérêt d'extraire des données sous cette forme est évident.

L'outil Oracle SQL*Plus fait peur aux noobs à cause de son invite de commandes, mais y'a pas de quoi.

Lancement de SQL*Plus

Selon votre installation Oracle, vous pouvez avoir accès à l'une des nombreuses versions ou « modes » différents dans lesquels exécuter l'application SQL*Plus. Si vous êtes consultant fonctionnel et ne comprenez pas ce dont je parle, demandez à un consultant support ERP… OK?

Ligne de commande SQL*Plus

Si vous souhaitez utiliser SQL*Plus en ligne de commande, félicitations vous êtes un pro des bases de données Oracle. Vous entrez simplement la commande sqlplus à partir de votre shell:

$ sqlplus

L'outil tente de se connecter à la base de données par défaut et vous êtes invité à entrer vos identifiants pour vous authentifier. Si vous devez vous connecter à une autre base de données ou passer par un autre utilisateur (schéma), essayez ce qui suit en remplaçant vos propres valeurs si nécessaire:

$sqlplus schema@//machine.domain:port/base de données

Ce qui donnerait par exemple:

$sqlplus clients@//server.michelcampillo.com:1481/ORADB

SQL*Plus pour Windows

Si vous utilisez Windows car votre expérience du logiciel libre a été courte (9452), il existe également une version Windows de SQL*Plus qui peut généralement être lancée à partir de votre menu Démarrer: Démarrer> Programmes> Oracle> Développement d'applications> SQL Plus.

Sortie d'une requête dans un fichier

Maintenant que vous êtes connecté à SQL*Plus, nous pouvons commencer à créer notre fichier.

Modifier la configuration SQL*Plus

La première étape consiste à configurer certains paramètres système de SQL*PLus à l'aide de l'instruction SET. Pour cet exemple, nous modifierons manuellement ces paramètres une unique fois avant notre génération de requête et de fichier CSV. Mais si vous le souhaitez et si l'administrateur système de votre entreprise le permet, vous pouvez modifier les valeurs par défaut de plusieurs paramètres dans votre profil utilisateur. Cela se passe dans le fichier login.sql.

set colsep ,
set headsep off
set pagesize 0
set trimspool on

Vous n'allez sûrement pas vouloir modifier ces paramètres, mais je vais expliquer brièvement à quoi ils servent.

colsep est le caractère de séparation utilisé pour diviser vos colonnes. Pour un fichier CSV, il s'agit d'une simple virgule comme on le voit ci-dessus.
headsep est le caractère séparateur de la ligne d'en-tête (si vous en avez besoin). Dans cet exemple, nous ne sortons pas la ligne d'en-tête, nous allons donc laisser cette option désactivée.
pagesize est le nombre de lignes « par page ». Il s'agit d'un paramètre légèrement archaïque destiné à l'impression pour ne pas avoir trop de lignes par page. Avec une valeur de 0 nous n'utilisons pas de pages vu que nous sortons les données dans un fichier. Si vous choisissez d'afficher la ligne d'en-tête, définissez la taille de la page sur un très grand nombre (supérieur au nombre attendu de résultats d'enregistrement dans la requête), afin que votre ligne d'en-tête n'apparaisse qu'une seule fois plutôt qu'une fois « par page ».
trimspool quand il est réglé sur on supprime simplement les espaces de fin.

Pour trimspool on c'est un collègue développeur PL/SQL qui me l'avait expliqué quand je travaillais au Brésil, avant ma mutation professionnelle à Aix en Provence (8537). Un paramètre intéressant à connaître pour ne pas être ennuyé par les espaces de fin.

Et maintenant les deux derniers paramètres à modifier en fonction de votre requête:

set linesize #
set numwidth #

Pour linesize la valeur # doit être le nombre total de colonnes de sortie souhaité dans votre fichier CSV à générer. Pour numwidth c'est la largeur de colonne (nombre d'espaces de caractères) utilisée lors de la sortie de valeurs numériques.

La commande SPOOL

Le paramètrage (set) étant fait, nous devons maintenant dire à SQL*Plus de sortir un fichier. Pour ce faire, on utilise l'instruction SPOOL. Pendant que SPOOL est actif, SQL*Plus stockera la sortie de toute requête dans le fichier spécifié au lieu de l'afficher à l'écran. Par conséquent, la prochaine commande à entrer est spool:

spool chemin/fichier.csv

Une fois le fichier CSV généré il faudra arrêter le spooler. L'instruction qui le permet (spool off) se met à la fin du script pour clore la génération du fichier :

spool off

Insérez la requête

La dernière étape après la modification des paramètres et l'exécution du spool consiste à insérer votre requête. Dans cet exemple, je veux sortir tous les codes client et désignation client de la table clients:

SELECT
  code_client,
  nom_client
FROM
  clients;
	

N'oubliez pas le point-virgule pour fermer votre requête, puis entrez la commande spool off dont j'ai parlé ci-dessus. Voilà, vous avez généré un nouveau fichier plat CSV avec les résultats de votre requête à l'aide de SQL*Plus. Vous pouvez l'ouvrir dans Excel et le sauvegarder au format .xlsx avant de le délivrer au chef de projet ERP qui fait boîte à lettre avec le client.

Réaliser un script SQL

Soyons astucieux, plutôt que le faire manuellement on va intégrer ces instructions dans un script SQL qu'on pourra exécuter dans SQL*Plus en une seule commande.

Commençons par créer un nouveau fichier de script SQL avec l'instruction EDIT:

EDIT mon_script.sql

Collez maintenant la liste complète des commandes de script dans ce nouveau fichier et enregistrez. Le contenu complet de mon exemple de script est ci-dessous, il suffit de copier coller. Je vous connais, vous avez peur que votre doigt dérape sur la touche donc je vous le fais:

set colsep ,
set headsep off
set pagesize 0
set trimspool on
set linesize 2
set numwidth 5

spool mon_fichier.csv

SELECT
  code_client,
  nom_client
FROM
  clients;

spool off

Dans SQL*Plus pour exécuter le script, utilisez simplement le symbole @ suivi du nom du fichier de script:

SQL> @mon_script.sql

Votre script SQL va s'exécuter et le fichier CSV va se créer comme prévu.

Outre l'email, mobile, téléphone, Telegram, réseaux sociaux, je vous invite à me retrouver également sur Skype. Très utile, installé sur mon mobile, je reçois instantanément vos messages. Vous n'aurez pas à patienter pour être ajouté. Mon identifiant: michelcampillo.

Aix en Provence, le 26 mars 2020

Michel Campillo
Consultant chef de projet IT
☎ 06 89 56 58 18  ✉ contact par email, voir plus bas

➽ Autres billets pour consultants ERP travaillant sur des bases de données Oracle: Une backdoor sur des milliers de serveurs Microsoft SQL (4651), Comment changer la taille de police dans Oracle SQL Developer?, Configuration de SQL Developer pour MySQL (3813), SkySQL : Oracle n'aura pas la peau de MySQL (8719), Apprendre le langage SQL.

Ce post vous a aidé? Alors partagez-le avec d'autres utilisateurs en cliquant sur les boutons ci-dessous:

Merci de vos partages! 👷🏻‍



🎯 Autres options: Mentions légales, Quelques outils de gestion de projet open source disponibles, À quoi sert une page entreprise Linkedin ?, L'ERP nouvelle génération arrive, Carte mentale, exemples et concept, Intergiciel ERP, Logiciel de prise de notes, Être consultant sur un logiciel métier, Logiciels ERP les plus connus, Les outils en gestion Agile, quelles alternatives?, La quête d'un chef de projet pour la productivité.
✇ Site web 🤖 100% thermo-dynamique 🌱 depuis 2004 🌿

Copyright © 2004-2024 Michel Campillo, tous droits réservés