SQLcl : mieux redimensionner les colonnes d'un SELECT qu'avec SQL*Plus - resize the columns of a SELECT better than SQL*Plus

21 mars 2021

SQLcl : mieux redimensionner les colonnes d'un SELECT qu'avec SQLPlus - resize the columns of a SELECT better than SQLPlus

Introduction
SQLcl (SQL Command Line) est un utilitaire Java intégré dans Oracle depuis la 12.2, alors qu'auparavant il fallait l'installer à part. Il est basé sur l'outil gérant les scripts SQL de SQL Developer et intègre de nouvelles commandes par rapport à SQL*Plus.

Aujourd'hui nous allons voir comment cet outil améliore l'affichage des SELECTs par rapport à SQL*Plus. Sous SQL*Plus il faut redimensionner chaque colonne avec la commande "COLUMN my_column FORMAT xxx". Si vous avez 10 colonnes à formater, il faut saisir dix commandes... c'est pénible, long et le formatage n'est pas persistant d'une session à l'autre; on peut certes exécuter ces commandes depuis un .sql mais c'est lourd.

J'avais déjà écris un article fin 2017 sur ce sujet "Comment bien présenter le résultat d'un SELECT sous SQL*Plus". Il est toujours d'actualité mais si vous avez une 12.2 alors utilisez SQLcl, bien plus pratique sur ce point.

Autres articles sur SQLcl : http://dbaoraclesql.canalblog.com/archives/sqlcl/index.html

Points d'attention
N/A.

Base de tests
Une base Oracle 19c multi-tenants.

Exemples
============================================================================================
SQL*Plus : formater un résultat
============================================================================================
Voyons d'abord SQL*Plus.
[oracle@localhost ~]$ . oraenv
ORACLE_SID = [orclcdb] ?
The Oracle base remains unchanged with value /u01/app/oracle

[oracle@localhost ~]$ sqlplus HR@orcl

SQL> desc zz1
Name                      Null?    Type
----------------------------------------- -------- ----------------------------
ID                      NOT NULL NUMBER(8)
NAME                      NOT NULL VARCHAR2(50)
FIRST_NAME                NOT NULL VARCHAR2(50)
ID_EXT                     NOT NULL NUMBER(8)

LINESIZE
Doc Oracle : "LINESIZE : Sets the total number of characters that SQL*Plus displays on one line before beginning a new line." Sous SQL*Plus on peut donc changer la valeur de linesize car la valeur par défaut, 80, est trop petite pour les écrans actuels. Avec 80 chaque enregistrement de mon SELECT est affiché sur deux lignes, ce qui est illisible.
SQL> show linesize
linesize 80

SQL> select * from zz1 where rownum < 3;
ID      NAME
---------- --------------------------------------------------
FIRST_NAME ID_EXT
-------------------------------------------------- ----------
957    JONES
Tom    9570
958    JONES
Tom    9580

Si on met ce paramètre à 200, chaque enregistrement est bien affiché sur une ligne MAIS les colonnes sont trop larges (on le voit avec la ligne qui souligne les colonnes); ce n'est pas grave mais lors de copier/coller de la requête dans Word ou un mail, on peut se retrouver avec le problème ci-dessus, un enregistrement affiché sur deux lignes.
SQL> set lines 200
SQL> show linesize
linesize 200

SQL> select * from zz1 where rownum < 3;
ID    NAME    FIRST_NAME ID_EXT
---------- -------------------------------------------------- -------------------------------------------------- ----------
957    JONES    Tom       9570
958    JONES    Tom       9580

COLUMN *** FORMAT ***
Pour améliorer l'affichage, on peut redimensionner la largeur des colonnes, une par une... c'est lourd et ce nouveau format n'est persistant que durant la session active; néanmoins, le résultat est largement plus lisible.
SQL> column NAME format A6
SQL> column FIRST_NAME format A6
SQL> column ID for 999

SQL> select * from zz1 where rownum < 3;
ID NAME FIRST_ ID_EXT
---- ------ ------ ----------
957 JONES Tom 9570
958 JONES Tom 9580

Si je me reconnecte, le paramétrage est perdu.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

[oracle@localhost ~]$ sqlplus HR@orcl

SQL> select * from zz1 where rownum < 3;
ID    NAME
---------- --------------------------------------------------
FIRST_NAME ID_EXT
-------------------------------------------------- ----------
957    JONES
Tom    9570
958    JONES
Tom    9580

LINESIZE WINDOW
Sous SQL*Plus, depuis la 12, il existe le paramètre WINDOW comme option de LINESIZE, qui n'existe pas sous SQLcl. Il permet d'utiliser la taille max de votre fenêtre, sans avoir à spécifier la longueur de chaque colonne :-)
SQL> show lines
linesize 80

SQL> select * from employees where rownum = 1;
EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- -------------------- -------------------------
EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY
------------------------- -------------------- --------- ---------- ----------
COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
-------------- ---------- -------------
100 Steven King
SKING 515.123.4567 17-JUN-87 AD_PRES 24000
90

SQL> set lines window
SQL> show lines
linesize 180 WINDOW

Si j'agrandi maintenant la fenêtre (avec la souris) : la taille a été recalculée automatiquement; coooool! même si les colonnes pourraient être moins larges.
SQL> show lines
linesize 272 WINDOW

SQL> select * from employees where rownum = 1;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- --------- ---------- ------ ---- -------------- ---------- -------------
100 Steven King SKING 515.123.4567 17-JUN-87 AD_PRES 24000 90

============================================================================================
SQLcl : formater un résultat
============================================================================================
Attention, la commande pour lancer SQLcl est sqlui et non pas sqlcl; allez savoir pourquoi...
[oracle@localhost ~]$ sqlcl
bash: sqlcl: command not found...

[oracle@localhost ~]$ sqlui
SQLcl: Release 19.1 Production on Sat Mar 20 06:52:40 2021
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Username? (''?) HR
Password? (**********?) **
Last Successful login time: Sat Mar 20 2021 06:52:45 -04:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

LINES WINDOW
Sous SQLcl la commande SET LINES WINDOW n'existe pas.
SQL> SET LINES WINDOW
SP2-0268: linesize option not a valid number

SQLFORMAT ANSICONSOLE
SQLcl a une nouvelle option par rapport à SQL*Plus, SQLFORMAT, qui permet de formater de façon poussée le résultat d'un SELECT. Nous nous intéresserons uniquement à l'option ANSICONSOLE. Celle-ci redimensionne automatiquement les colonnes au mieux, selon la taille du terminal mais aussi des données dans chaque colonne; c'est le nec plus ultra! En plus ça souligne les noms des colonnes plutôt que de mettre une ligne de tirets. Autre avantage : plus besoin de reformater les colonnes avec plusieurs commandes "SET colonne FORMAT A***".

Avec l'aide, vous voyez que l'option SQLFORMAT est très riche et peut générer des résultats formatés de plusieurs manières.
SQL> help set sqlformat
SET SQLFORMAT
   SET SQLFORMAT { default,csv,html,xml,json,fixed,insert,loader,delimited,ansiconsole}

      default : SQL*PLUS style formatting
      csv : comma separated and string enclosed with "
      html : html tabular format
      xml : xml format of /results/rows/column/*
      json : json format matching ORDS Collection Format
      json-formatted : json format matching ORDS Collection Format and pretty printed
      fixed : fixed width
      insert : generates insert statements from sql results
         Example
            Insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
            values (7369,'SMITH','CLERK',7902,to_timestamp('17-DEC-80','DD-MON-RR HH.MI.SSXFF AM'),800,null,20);
      loader : pipe (|) delimited enclosed with "
         Example:
            7369|"SMITH"|"CLERK"|7902|"1980-12-17 00:00:00"|800||20|5555555555554444|
      delimited : CSV format with optional separator , left, and right enclosure
            set sqlformat delimited [separator] [left enclosure] [right enclosure]
         Example:
            set sqlformat delimited , < >
            7369,<SMITH>,<CLERK>,7902,17-DEC-80,800,,20,5555555555554444
      ansiconsole : advanced formatting based on data and terminal size
               set sqlformat ansiconsole : base format
               set sqlformat ansiconsole default : number formatting to ###,###.###
               set sqlformat ansiconsole <number format mask> : Mask following Java DecimalFormat
                  https://docs.oracle.com/javase/8/docs/api/java/text/DecimalFormat.html
               set sqlformat ansiconsole -config=highlight.json : highlight matches in results
               highlight options :
               Example :
               {"highlights":[
                  {"type":"startWith","test":"W","color":"INTENSITY_BOLD,CYAN"},
                  {"type":"endWith","test":"MAN","color":"BLUE"},
                  {"type":"contains","test":"MIT","color":"YELLOW"},
                  {"type":"exact","test":"FORD","color":"GREEN"},
                  {"type":"regex","test":"[0-9]{2}","color":"MAGENTA"}
                  ]
               }

SQL> SET SQLFORMAT ANSICONSOLE
SQL> show sqlformat
SQL Format : ansiconsole

Là c'est le top : chaque colonne est retaillée à la taille max des données récupérées plutôt qu'utiliser toute la taille du terminal et remplir les connes avec des espaces.
SQL> select * from zz1 where rownum < 3;
ID NAME FIRST_NAME ID_EXT
______ ________ _____________ _________
957 JONES Tom 9570
958 JONES Tom 9580
2 rows selected.

On réinitialise le format avec le mot clé DEFAULT.
SQL> SET SQLFORMAT DEFAULT
SQL Format Cleared