Blog

  • Oracle, Outils de développement, Page d'accueil

SQLcl : Lorsque SQL Developer rencontre SQL*Plus

SQLcl est un nouvel outil qui offre des fonctionnalités intéressantes pour tous les DBA.
Cet article présente les fonctionnalités de base de cet outil.

1. Télécharger « Command Line – SQLcl – Early Adopter »

SQLcl est disponible sur OTN.

2. Décompresser l’archive

 $ unzip sqlcl-4.2.0.15.349.0706-no-jre.zip
 Archive: sqlcl-4.2.0.15.349.0706-no-jre.zip
 inflating: sqlcl/bin/sql
 inflating: sqlcl/bin/sql.bat
 inflating: sqlcl/bin/sql.exe
 inflating: sqlcl/lib/SQLinForm.jar
 inflating: sqlcl/lib/gson-2.3.1.jar
 inflating: sqlcl/lib/javax.json-1.0.4.jar
 inflating: sqlcl/lib/jline-2.12.1.jar
 inflating: sqlcl/lib/jsch.jar
 inflating: sqlcl/lib/ojdbc6.jar
 inflating: sqlcl/lib/oracle.dbtools-common.jar
 inflating: sqlcl/lib/oracle.sqldeveloper.sqlcl.jar
 inflating: sqlcl/lib/orai18n-mapping.jar
 inflating: sqlcl/lib/orai18n-utility.jar
 inflating: sqlcl/lib/orai18n.jar
 inflating: sqlcl/lib/orajsoda.jar
 inflating: sqlcl/lib/xdb6.jar
 inflating: sqlcl/lib/xmlparserv2.jar

3. Lancement & connexions

La connexion est semblable à celle de SQL*Plus

$ ./sql -v
 SQLcl: Release 4.2.0.15.349.0706 RC
$ ./sql /nolog
$ ./sql scott/tiger@
$ ./sql scott/tiger@hostname:port/service
SQLcl: Release 4.2.0.15.349.0706 RC on Thu Dec 24 12:26:53 2015
Copyright (c) 1982, 2015, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

4. Alias

Comme sous Linux, il est possible de créer des alias pour gagner du temps :


 SQL> help alias
ALIAS
 ------
alias [=;| LOAD []|SAVE [] | LIST [] |
                               DROP  | DESC  ]
Alias is a command which allows you to save a sql, plsql or sqlplus script and assign it a shortcut command.
        "alias" - print a list of aliases
        "alias list " - list the contents of the alias
        "alias =select :one from dual;" - Simple alias command
        Define an alias simply by using the alias keyword followed by a single identifier
        name followed by an '='. Anything after the '=' will be used as the alias contents.
        For example, if it is SQL, it will be terminated by a ';'. If it is PLSQL, it will
        be terminated by a '/'

sqlc_2015-12-24_12h23_24

5. Formatage

SQLcl fournit des fonctionnalités avancées sur le formatage des données.

 SET SQLFORMAT ANSICONSOLE
 SET SQLFORMAT ANSICONSOLE DEFAULT
 SET SQLFORMAT ANSICONSOLE
 SET SQLFORMAT CSV
 SET SQLFORMAT XML
 SET SQLFORMAT INSERT

Exemple de formatage standard (comme sous SQL*Plus) vs format SQLcl :
sqlc_2015-12-24_12h06_26
Exemple de formatage des nombres :
sqlc_2015-12-24_12h52_35


 SQL> SET TERM OFF
 SQL> SET SQLFORMAT CSV
 SQL> SPOOL liste_tables.csv
 SQL> liste_tables MGMT_METRIC%
 SQL> SPOOL OFF
SQL> ! grep ^\" liste_tables.csv
"OWNER","TABLE_NAME"
 "SYSMAN","MGMT_METRICS_EXT"
 "SYSMAN","MGMT_METRICS_NG"
 "SYSMAN","MGMT_METRICS_ROLLUP_TEMP"
 "SYSMAN","MGMT_METRIC_COLLECTIONS_REP"
 "SYSMAN","MGMT_METRIC_DEPENDENCY"
 "SYSMAN","MGMT_METRIC_DEPENDENCY_DEF"
 "SYSMAN","MGMT_METRIC_DEPENDENCY_DETAILS"
 "SYSMAN","MGMT_METRIC_TO_DELETE"
 "SYSMAN","MGMT_METRIC_VERSIONS"

Il est même possible de charger le fichier dans une table avec la commande LOAD :


 SQL> HELP LOAD
 LOAD
-----
Loads a comma separated value (csv) file into a table.
The first row of the file must be a header row.  The columns in the header row must match the columns defined on the table.
The columns must be delimited by a comma and may optionally be enclosed in double quotes.
Lines can be terminated with standard line terminators for windows, unix or mac.
File must be encoded UTF8.
The load is processed with 50 rows per batch.
If AUTOCOMMIT is set in SQLCL, a commit is done every 10 batches.
The load is terminated if more than 50 errors are found.
LOAD [schema.]table_name[@db_link] file_name

6. Complétion du code et historique

SQLcl permet une complétion des instructions ainsi que l’accès à l’historique des commandes.

SQL> help history
 HISTORY
 ---------
 history [ | FULL | USAGE | SCRIPT | TIME | CLEAR (SESSION)?] | FAILS

7. Affichage répétitif du résultat d’une requête SQL


 SQL> help repeat
repeat
        Repeats the current sql in the buffer the specified times with sleep intervals
        Maximum sleep is 120s
 SET SQLFORMAT ANSICONSOLE
 SELECT current_scn FROM v$database;
 repeat 10 O.5

Exemple de la commande « repeat » :
sqlc_2015-12-24_12h12_26

8. Description des objets

La commande info remplace avantageusement la commande DESC.
Exemple de la commande « info » :
sqlc_2015-12-24_12h29_04
Les commandes CTAS et DDL permettent de travailler avec les métadonnées.


 SQL> help ctas
CTAS
 ctas table new_table
Uses DBMS_METADATA to extract the DDL for the existing table
 Then modifies that into a create table as select * from
SQL> help ddl
DDL
---
DDL generates the code to reconstruct the object listed.  Use the type option
for materialized views. Use the save options to save the DDL to a file.

Par exemple :

 SQL> ctas DEPT DEPT_TEMP
  CREATE TABLE "SCOTT"."DEPT_TEMP"
   (    "DEPTNO",
        "DNAME",
        "LOC",
         CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
 as
select * from DEPT
SQL> ddl EMP
  CREATE TABLE "SCOTT"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0),
         CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
SQL> ddl SALGRADE save ddl_salgrade.sql
SQL> ! cat ddl_salgrade.sql
  CREATE TABLE "SCOTT"."SALGRADE"
   (    "GRADE" NUMBER,
        "LOSAL" NUMBER,
        "HISAL" NUMBER
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;

Il est même possible de formater un fichier :

SQL> help format
 FORMAT
 ---------
 FORMAT BUFFER - formats the script in the SQLcl Buffer
 FORMAT RULES  - Loads SQLDeveloper Formatter rules file to formatter.
 FORMAT FILE
 Format used is default or for SQLcl can be chosen by setting an environmental variable
 pointing to a SQLDeveloper export (.xml) of formatter options.
 The variable is called SQLFORMATPATH
 In SQLDeveloper the format options are the default chosen in the preferences.

Par exemple :

 SQL> ! cat test_unformatted.sql
select table_name,
                        num_rows,
         last_analyzed
                          from dba_tables
 where         owner          = 'SCOTT';
 SQL> format file test_unformatted.sql test_formatted.sql
 SQL> ! cat test_formatted.sql
 SELECT
    table_name,
    num_rows,
    last_analyzed
FROM
    dba_tables
WHERE
    owner = 'SCOTT';

SQLcl est encore en version béta (Early Adoptor) mais sera bientôt un compagnon indispensable pour tout DBA.

dummydba
dummydba
Voir tous ses articles
Articles récents

Mentions légales & Politique de confidentialité