Comment bien présenter le résultat d'un SELECT sous SQL*Plus - How to present the result of a SELECT under SQL*Plus
Introduction
Quoi de pire qu'un SELECT qui renvoie le bon résultat mais qui est si mal formaté qu'il en devient illisible? Nous allons voir comment mettre en page celui-ci avec de simples commandes SQL*Plus.
[EDIT 22/03/2021]
Ici un article vous expliquant comment formater un SELECT sous SQLcl : "SQLCL : mieux redimensionner les colonnes d'un SELECT qu'avec SQL*Plus"
Base de tests
N'importe quelle base Oracle.
Points d'attention
Aucun.
Exemples
============================================================================================
Afficher des accents sous SQL*Plus
============================================================================================
Si des caractères exotiques s'affichent suite à votre SELECT, ne paniquez pas, cela ne veut pas dire que votre base est corrompue ou que les données en base sont incorrectes. Il y a de fortes chances pour que ce soit juste un problème d'affichage sous Windows; pour corriger cela, je vous renvoie vers mon article "SQL*Plus et les accents sous Windows".
============================================================================================
Les commandes SQL*Plus
============================================================================================
Pour afficher les commandes SQL*Plus, rien de plus simple, il suffit de taper help index dans le logiciel (n'oubliez pas le mot index). Nous n'allons pas détailler ici toutes les commandes mais on voit déjà que le logiciel offre un grand choix pour paramétrer l'affichage.
SQL> help index
Enter Help [topic] for help.
@ COPY PAUSE SHUTDOWN
@@ DEFINE PRINT SPOOL
/ DEL PROMPT SQLPLUS
ACCEPT DESCRIBE QUIT START
APPEND DISCONNECT RECOVER STARTUP
ARCHIVE LOG EDIT REMARK STORE
ATTRIBUTE EXECUTE REPFOOTER TIMING
BREAK EXIT REPHEADER TTITLE
BTITLE GET RESERVED WORDS (SQL) UNDEFINE
CHANGE HELP RESERVED WORDS (PL/SQL) VARIABLE
CLEAR HOST RUN WHENEVER OSERROR
COLUMN INPUT SAVE WHENEVER SQLERROR
COMPUTE LIST SET XQUERY
CONNECT PASSWORD SHOW
Chaque commande a aussi son propre help. Par exemple la commande COLUMN qui est très puissante pour formater du texte.
SQL> help column
COLUMN
------
Specifies display attributes for a given column, such as:
- text for the column heading
- alignment for the column heading
- format for NUMBER data
- wrapping of column data
Also lists the current display attributes for a single column
or all columns.
COL[UMN] [{column | expr} [option ...] ]
where option represents one of the following clauses:
ALI[AS] alias
CLE[AR]
ENTMAP {ON|OFF}
FOLD_A[FTER]
FOLD_B[EFORE]
FOR[MAT] format
HEA[DING] text
JUS[TIFY] {L[EFT] | C[ENTER] | R[IGHT]}
LIKE {expr | alias}
NEWL[INE]
NEW_V[ALUE] variable
NOPRI[NT] | PRI[NT]
NUL[L] text
OLD_V[ALUE] variable
ON|OFF
WRA[PPED] | WOR[D_WRAPPED] | TRU[NCATED]
============================================================================================
La police de caractères Courrier New
============================================================================================
Un problème récurrent quand on veut faire un copier/coller d'un résultat SQL*Plus dans un document Word ou un mail, c'est que le formatage par défaut de SQL*Plus est perdu, notamment les tabulations et l'alignement des données avec les colonnes. Pour retrouver ce formatage, on peut utiliser la police de caractères Courrier New.
Résultat d'un ordre SQL*Plus en Times New Roman : toutes les tabulations sont perdues.
SQL> help index
Enter Help [topic] for help.
@ COPY PAUSE SHUTDOWN
@@ DEFINE PRINT SPOOL
/ DEL PROMPT SQLPLUS
ACCEPT DESCRIBE QUIT START
APPEND DISCONNECT RECOVER STARTUP
ARCHIVE LOG EDIT REMARK STORE
ATTRIBUTE EXECUTE REPFOOTER TIMING
BREAK EXIT REPHEADER TTITLE
BTITLE GET RESERVED WORDS (SQL) UNDEFINE
CHANGE HELP RESERVED WORDS (PL/SQL) VARIABLE
CLEAR HOST RUN WHENEVER OSERROR
COLUMN INPUT SAVE WHENEVER SQLERROR
COMPUTE LIST SET XQUERY
CONNECT PASSWORD SHOW
En Courrier New, c'est beaucoup plus lisible.
SQL> help index
Enter Help [topic] for help.
@ COPY PAUSE SHUTDOWN
@@ DEFINE PRINT SPOOL
/ DEL PROMPT SQLPLUS
ACCEPT DESCRIBE QUIT START
APPEND DISCONNECT RECOVER STARTUP
ARCHIVE LOG EDIT REMARK STORE
ATTRIBUTE EXECUTE REPFOOTER TIMING
BREAK EXIT REPHEADER TTITLE
BTITLE GET RESERVED WORDS (SQL) UNDEFINE
CHANGE HELP RESERVED WORDS (PL/SQL) VARIABLE
CLEAR HOST RUN WHENEVER OSERROR
COLUMN INPUT SAVE WHENEVER SQLERROR
COMPUTE LIST SET XQUERY
CONNECT PASSWORD SHOW
ATTENTION : dans la suite de cet article je n'utiliserai pas cette police Courrier New car elle occupe plus de place à l'écran et les infos tenant sur une ligne sous SQL*Plus s'afficheront sous 2 lignes dans Canalblog.
============================================================================================
Linesize et Pagesize
============================================================================================
Les paramètres SQL*Plus linesize et pagesize sont fondamentaux pour formater le résultat d'un SELECT.
Linesize : nombre de caractères affichables sur une ligne. Attention au fait que ce nombre de caractères peut dépasser la capacité de la fenêtre de votre terminal ou bien de votre écran. Si votre logiciel utilise des ascenseurs horizontaux, pas de problème, sinon des données seront perdues car impossibles à afficher. Autre point, ce paramètre est capricieux, par exemple quand on fait un DESC ou un SELECT, Oracle affiche le résultat sur toute la longueur définie par linesize en complétant avec des blancs, ce qui rend parfois le résultat horrible!
Pagesize : le nombre de lignes contenues dans une page, avant que SQL*Plus ne réaffiche l'en-tête des colonnes.
Par défaut les valeurs sont 80 pour linesize et 14 pour pagesize.
SQL> show linesize pagesize
linesize 80
pagesize 14
Voici l'affichage obtenu avec les paramètres par défaut. Le résultat est inexploitable, les colonnes sont sur plusieurs lignes et leur en-tête apparaît plusieurs fois!!!!!
SQL> select W.WAIT_CLASS, S.NAME, S.VALUE
from V$SYSSTAT S, V$SYSTEM_WAIT_CLASS W
where S.CLASS = W.WAIT_CLASS# AND WAIT_CLASS IN ('Application','Configuration') AND
S.VALUE > 50000000 order by W.WAIT_CLASS, S.NAME;
WAIT_CLASS
----------------------------------------------------------------
NAME VALUE
---------------------------------------------------------------- ----------
Application
bytes received via SQL*Net from client 4059138333
Application
bytes sent via SQL*Net to client 8554615382
Application
file io wait time 7472958403
WAIT_CLASS
----------------------------------------------------------------
NAME VALUE
---------------------------------------------------------------- ----------
Application
in call idle wait time 959460342
Application
session connect time 1513294445
Application
session logical reads 792058734
WAIT_CLASS
----------------------------------------------------------------
NAME VALUE
---------------------------------------------------------------- ----------
Application
session pga memory 1267421792
On va corriger tout ça en modifiant les valeurs par défaut de ces deux paramètres.
Tout d'abord, trouvons leur valeur max : 32767 pour linesize et 50000 pour pagesize.
SQL> set linesize 200000
SP2-0267: option linesize 200000 hors limite (de 1 a 32767)
SQL> set pagesize 60000
SP2-0267: option pagesize 60000 hors limite (de 0 a 50000)
Passons linesize à 200 : tout de suite c'est mieux, les colonnes sont affichées sur une seule ligne. Il reste le problème des en-têtes de page qui se répètent.
SQL> set linesize 200
SQL> /
WAIT_CLASS NAME VALUE
---------------------------------------------------------------- ---------------------------------------------------------------- ----------
Application bytes received via SQL*Net from client 4060729724
Application bytes sent via SQL*Net to client 8557078523
Application file io wait time 7473001174
Application in call idle wait time 959944977
Application session connect time 1513294445
Application session logical reads 792427961
Application session pga memory 1261523904
Application session pga memory max 2409862752
Application session uga memory 2,4261E+12
Application session uga memory max 6,6864E+10
Configuration redo size 2599090352
WAIT_CLASS NAME VALUE
---------------------------------------------------------------- ---------------------------------------------------------------- ----------
Configuration redo wastage 423911576
Mettons pagesize à 1000 : génial, tout tient sur une page!
SQL> set pagesize 1000
SQL> /
WAIT_CLASS NAME VALUE
---------------------------------------------------------------- ---------------------------------------------------------------- ----------
Application bytes received via SQL*Net from client 4061150818
Application bytes sent via SQL*Net to client 8557994879
Application file io wait time 7473009756
Application in call idle wait time 960088551
Application session connect time 1513294445
Application session logical reads 792579310
Application session pga memory 1263688576
Application session pga memory max 2418253344
Application session uga memory 2,4261E+12
Application session uga memory max 6,6873E+10
Configuration redo size 2599368636
Configuration redo wastage 424010740
Voici maintenant LE problème avec linesize : SQL*Plus utilise systématiquement toute la longueur pour afficher le résultat et donne aux colonnes une largeur imprévisible.
SQL> show linesize
linesize 80
SQL> desc dict
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLE_NAME VARCHAR2(128)
COMMENTS VARCHAR2(4000)
SQL> set linesize 200
SQL> desc dict
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
TABLE_NAME VARCHAR2(128)
COMMENTS VARCHAR2(4000)
Alors là c'est le pompom...
SQL> set linesize 500
SQL> desc dict
Name Null? Type
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TABLE_NAME VARCHAR2(128)
COMMENTS VARCHAR2(4000)
============================================================================================
BREAK ... ON ... SKIP : éliminer les données en double
============================================================================================
La commande BREAK ... ON permet de créer une rupture pour les données identiques d'une colonne. Cela rend bien plus visible les changements de données, d'autant plus si elles sont ordonnées.
Pour afficher les breaks existant, saisir break.
SQL> break
SP2-0015: pas de rupture(s) definie(s)
On crée une rupture sur le colonne WAIT_CLASS et chaque libellé différent n'apparaît plus qu'une fois.
SQL> break on WAIT_CLASS
SQL> select W.WAIT_CLASS, S.NAME, S.VALUE
from V$SYSSTAT S, V$SYSTEM_WAIT_CLASS W
where S.CLASS = W.WAIT_CLASS# AND WAIT_CLASS IN ('Application','Configuration') AND
S.VALUE > 50000000 order by W.WAIT_CLASS, S.NAME;
WAIT_CLASS NAME VALUE
---------------------------------------------------------------- ---------------------------------------------------------------- ----------
Application bytes received via SQL*Net from client 4062798183
bytes sent via SQL*Net to client 8562293847
file io wait time 7473030126
in call idle wait time 960280544
session connect time 1513294445
session logical reads 792655113
session pga memory 1272543872
session pga memory max 2429926688
session uga memory 2,4304E+12
session uga memory max 6,6885E+10
Configuration redo size 2599645620
redo wastage 424083260
Allez, on ajoute l'option SKIP à BREAK ON pour insérer une ou N lignes après chaque rupture.
SQL> break on WAIT_CLASS skip 2
SQL> /
WAIT_CLASS NAME VALUE
---------------------------------------------------------------- ---------------------------------------------------------------- ----------
Application bytes received via SQL*Net from client 4070933194
bytes sent via SQL*Net to client 8575491206
file io wait time 7473053662
in call idle wait time 960359563
session connect time 1513294445
session logical reads 792666277
session pga memory 1272135584
session pga memory max 2437382720
session uga memory 2,4304E+12
session uga memory max 6,6893E+10
Configuration redo size 2599894936
redo wastage 424137520
A noter que l'on peut faire un BREAK sur N colonnes.
SQL> break on WAIT_CLASS on NAME on VALUE skip 2;
Pour effacer les breaks, utilisez la commande CLEAR BREAKS.
SQL> clear breaks
breaks efface
============================================================================================
Mettre des titres aux résultats des SELECT : TTITLE, BTITLE, REPHEADER, REPFOOTER
============================================================================================
La commande TTITLE permet de créer un titre en haut de page d'un SELECT; son pendant pour le bas de page est BTITLE.
Attention, BTITLE complète la page en cours avec le nombre de lignes restantes pour arriver à pagesize, ce qui conduit parfois à des dizaines de lignes vides après le dernier enregistrement juste pour remplir la page. Ces commandes acceptent plusieurs paramètres pour formater les titres.
SQL> TTITLE skip 5 LEFT '18/12/2017' CENTER 'WAITS PAR CLASSE D''EVENEMENTS' RIGHT 'By David D.'
SQL> BTITLE skip 1 LEFT '18 Decembre 2017' CENTER 'Client Fonderie SteelMen' RIGHT 'Version 1.1' skip 1
18/12/2017 WAITS PAR CLASSE D'EVENEMENTS By David D.
WAIT_CLASS NAME VALUE
---------------------------------------------------------------- ---------------------------------------------------------------- ----------
Application bytes received via SQL*Net from client 4091320352
bytes sent via SQL*Net to client 8606272220
file io wait time 7473293512
in call idle wait time 961345439
session connect time 1513294445
session logical reads 793178197
session pga memory 1280731072
session pga memory max 2459150944
session uga memory 2,4390E+12
session uga memory max 6,6970E+10
Configuration redo size 2601492244
redo wastage 424607324
18 Decembre 2017 Client Fonderie SteelMen Version 1.1
Pour annuler ces titres, utilisez la commande ttitle off et btitle off.
SQL> ttitle off
SQL> btitle off
Une alternative à TTITLE et BTITLE existe, il s'agit des commandes REPFOOTER et REPHEADER.
SQL> REPHEADER PAGE CENTER 'Listing societe'
SQL> REPFOOTER PAGE CENTER 'Fin Listing Societe'
SQL> select sysdate from dual;
Listing societe
SYSDATE
---------
18-DEC-17
Fin Listing Societe
============================================================================================
Changer les noms de colonnes avec des alias ou la commande HEADING
============================================================================================
Parfois les noms de colonnes ne sont pas parlants, soit ce sont des noms cryptés en base (Tps01_DT_Emb) soit ils sont trop techniques (Salaire_prime_prorata_correct) soit Oracle les construit avec le nom d'une fonction (SUM(COL1*0.25)). On peut alors les changer en utilisant un alias directement dans l'ordre SQL; la meilleure pratique est d'utiliser le mot AS et d'entourer l'alias de guillemets pour plus de lisibilité.
SQL> select W.WAIT_CLASS AS "Classe evt wait", S.NAME AS "Nom evt wait", S.VALUE AS "Valeur"
from V$SYSSTAT S, V$SYSTEM_WAIT_CLASS W
where S.CLASS = W.WAIT_CLASS# AND WAIT_CLASS IN ('Application','Configuration') AND
S.VALUE > 50000000 order by W.WAIT_CLASS, S.NAME;
Classe evt wait Nom evt wait Valeur
---------------------------------------------------------------- ---------------------------------------------------------------- ----------
Application bytes received via SQL*Net from client 4095271029
Application bytes sent via SQL*Net to client 8616809762
Application file io wait time 7473664086
Application in call idle wait time 962704968
Application session connect time 1513294445
Application session logical reads 793885312
Application session pga memory 1272010816
Application session pga memory max 2428689792
Application session uga memory 2,4476E+12
Application session uga memory max 6,7068E+10
Configuration redo size 2605322624
Configuration redo wastage 425278912
Attention, si on avait fait un BREAK sur le nom de la colonne, il faut refaire ce break sur l'alias.
SQL> break on "Classe evt wait" skip 2
Classe evt wait Nom evt wait Valeur
---------------------------------------------------------------- ---------------------------------------------------------------- ----------
Application bytes received via SQL*Net from client 4095473306
bytes sent via SQL*Net to client 8617105377
file io wait time 7473699838
in call idle wait time 962776616
session connect time 1513294445
session logical reads 794034684
session pga memory 1267501568
session pga memory max 2425229120
session uga memory 2,4476E+12
session uga memory max 6,7070E+10
Configuration redo size 2605591904
redo wastage 425339432
La commande SQL*Plus HEADING donne aussi le même résultat.
SQL> select TABLE_NAME from dict where rownum < 2;
TABLE_NAME
--------------------------------------------------------------------------------
CDB_2PC_NEIGHBORS
SQL> select TABLE_NAME AS "Nom de la table" from dict where rownum < 2;
Nom de la table
--------------------------------------------------------------------------------
CDB_2PC_NEIGHBORS
SQL> column TABLE_NAME HEADING "Nom de la table"
SQL> /
Nom de la table
--------------------------------------------------------------------------------
CDB_2PC_NEIGHBORS
Pour voir tous les alias ou formatages existants sur les colonnes, utilisez la fonction COLUMN. Attention, les infos sont sur plusieurs lignes, une ligne par paramètre de la fonction column.
SQL> column
COLUMN NAME_COL_PLUS_SHOW_EDITION ON
HEADING 'EDITION'
FORMAT a30
word_wrap
COLUMN result_plus_xquery ON
HEADING 'Result Sequence'
COLUMN other_plus_exp ON
FORMAT a44
...
Pour effacer ces alias, utiliser la commande CLEAR COLUMN.
SQL> clear column
columns efface
SQL> column
SP2-0045: *pas de COLUMN defini
============================================================================================
Réduire la taille d'une colonne
============================================================================================
Il est également possible de réduire la taille d'une colonne pour gagner de la place d'affichage. Attention, le nom de la colonne est parfois coupé, comme pour les deux premières colonnes de ce SELECT! Vous noterez que si l'enregistrement dépasse la taille de la colonne, il s'affiche sur N lignes.
Remarque : pas la peine de chercher sur le net, je l'ai fais avant vous, il n'est pas possible en une seule commande de réduire la taille de plusieurs colonnes, comme de dire "Toutes les colonnes VARCHAR2 sont de longueur 20 caractères"! Vous êtes obligé de saisir le nouveau format des colonnes un par un.
SQL> column "Classe evenement attente" format A20
SQL> column "Nom evenement attente" format A20
SQL> select W.WAIT_CLASS AS "Classe evenement attente", S.NAME AS "Nom evenement attente", S.VALUE as "Valeur evenement attente"
from V$SYSSTAT S, V$SYSTEM_WAIT_CLASS W
where S.CLASS = W.WAIT_CLASS# AND WAIT_CLASS IN ('Application','Configuration') AND
S.VALUE > 50000000 order by W.WAIT_CLASS, S.NAME;
Classe evenement att Nom evenement attent Valeur evenement attente
-------------------- -------------------- ------------------------
Application bytes received via S 4104773352
QL*Net from client
bytes sent via SQL*N 8636391459
et to client
file io wait time 7476336221
in call idle wait ti 966062378
me
session connect time 1513294445
session logical read 795776400
s
session pga memory 1287186528
session pga memory m 2446814624
ax
session uga memory 2,4777E+12
session uga memory m 6,7277E+10
ax
Configuration redo size 2611859252
redo wastage 426915380
============================================================================================
Afficher le nom d'une colonne sur plusieurs lignes
============================================================================================
Il est aussi possible d'afficher le nom d'une colonne sur plusieurs lignes pour gagner de la place. Il faut utiliser le caractère pipe | dans la commande HEADING.
SQL> COLUMN 'Classe evenement attente' HEADING 'Classe|evenement|attente'
SQL> COLUMN 'Nom evenement attente' HEADING 'Nom|evenement|attente'
SQL> COLUMN 'Valeur evenement attente' HEADING 'Valeur|evenement|attente'
Classe Nom Valeur
evenement evenement evenement
attente attente attente
-------------------- -------------------------------------------------- ----------------
Application bytes received via SQL*Net from client 4108899738
bytes sent via SQL*Net to client 8647301471
file io wait time 7478823323
in call idle wait time 967449443
session connect time 1513294445
session logical reads 796770381
session pga memory 1276961408
session pga memory max 2435361376
session uga memory 2490596338840
session uga memory max 67344704256
Configuration redo size 2614321048
redo wastage 427607800
============================================================================================
Afficher la totalité des données d'une colonne NUMBER
============================================================================================
Parfois la taille de la colonne générée par Oracle ne permet pas d'afficher en totalité le contenu d'un champ NUMBER et Oracle est forcé de faire un arrondi des plus laids. Qu'à cela ne tienne, il est possible comme pour les colonnes caractères de changer le format d'affichage des colonnes NUMBER.
Voici ce qu'affiche SQL*Plus : inutilisable!
session uga memory 2,4777E+12
Pour les colonnes NUMBER, il faut utiliser N fois le chiffre 9 pour spécifier le nombre de caractères à afficher; on peut aussi utiliser des séparateurs comme la virgule.
SQL> column "Valeur evenement attente" format 999999999999999
SQL> column "Nom evenement attente" format A50
Classe evenement att Nom evenement attente Valeur evenement attente
-------------------- ------------------------------------------------------------ ------------------------
Application bytes received via SQL*Net from client 4105711863
bytes sent via SQL*Net to client 8638435546
file io wait time 7476389682
in call idle wait time 966499181
session connect time 1513294445
session logical reads 796071804
session pga memory 1268207744
session pga memory max 2419791968
session uga memory 2481999406600
session uga memory max 67285588208
Configuration redo size 2612492052
redo wastage 427115344
============================================================================================
Afficher le contenu d'une colonne sur plusieurs lignes : commande WRAP
============================================================================================
La commande WRAP permet de faire un retour à la ligne si les données à afficher dépassent la taille d'une colonne. Utiliser SHOW WRAP pour afficher si oui on non des colonnes seront wrappées (la traduction en français est Emballée). Pour annuler cette commande, utiliser SET WRAP OFF.
SQL> show wrap
wrap : lines will be wrapped
SQL> set wrap off
SQL> show wrap
wrap : lines will be truncated
SQL> set linesize 200 pagesize 5000
SQL> break on WAIT_CLASS on NAME
SQL> select W.WAIT_CLASS, S.NAME, S.VALUE
from V$SYSSTAT S, V$SYSTEM_WAIT_CLASS W
where S.CLASS = W.WAIT_CLASS# AND WAIT_CLASS IN ('Application','Configuration')
order by W.WAIT_CLASS, S.NAME;
WAIT_CLASS NAME VALUE
----- ----------------------------------------------------------- ---------------------------------------------------------------- ----------
Configuration Streaming No-Stall Reap 0
Streaming Stall Reap 0
flashback log write bytes 0
flashback log writes 0
recovery cvmap unavailable 0
...
On wrappe la colonne NAME; le résultat obtenu n'est pas très beau mais on comprend vite à quoi sert ce paramètre : faire tenir sur un écran le contenu d'une ou de colonnes dépassant sa taille!
SQL> COLUMN NAME FORMAT A20 WRAP
SQL> select W.WAIT_CLASS, S.NAME, S.VALUE
from V$SYSSTAT S, V$SYSTEM_WAIT_CLASS W
where S.CLASS = W.WAIT_CLASS# AND WAIT_CLASS IN ('Application','Configuration')
order by W.WAIT_CLASS, S.NAME;
WAIT_CLASS NAME VALUE
---------------------------------------------------------------- -------------------- ----------
Configuration Streaming No-Stall R 0
eap
Streaming Stall Reap 0
flashback log write 0
bytes
flashback log writes 0
recovery cvmap unava 0
ilable
recovery local buffe 0
r freed
recovery local buffe 0
r unavailable
recovery logmerger c 0
atchup
La fonction COLUMN a d'autres paramètres de formatage mais maintenant c'est à vous de jouer :-)