Letztens fragte mich ein Kollege, wie war das noch einmal mit SQLcl? Ich weiss zwar, dass es ein Oracle Commandline Tool ist, aber wozu kann man es verwenden? Welche Funktionen gibt es denn?
Seit Anfang Juli ist nun auch die Version 21.2 verfügbar, und es gibt wieder einige interessante Neuigkeiten. Grund genug ein Posting zu SQLcl herauszugeben, um nicht nur die neuen Funktionen sondern auch um einige Beispiel zum “alten” Funktionsumfang aufzuzeigen.

Oracle SQLcl (kurz für Oracle SQL Developer Command Line) ist im Unterschied zu SQL*Plus, dem mitgelieferten Standard SQL Tool der Oracle Datenbank, ein weiteres Oracle SQL Tool mit vielen interessanten Eigenschaften und Schnittstellen. Oracle SQLcl ist ein Java-basierte Commandline Tool für die Oracle Datenbank. Mit SQLcl können SQL- und PL/SQL- Befehle interaktiv oder als Batch-Datei ausgeführt werden. Zur Basis Funktionalität gehört Inline-Editierung, automatische Vervollständigung, die History Funktionalität und das Ausführen von SQL*Plus-Skripten. Bald gibt es für SQLcl ein 5 jähriges Jubiläum, da es seit Herbst 2016 zur Verfügung steht. Installation und Konfiguration ist wie bei SQL Developer und den Instant Clients ganz einfach: Nur ein Download der aktuellen zip-Datei und das Auspacken sind dazu erforderlich. Die aktuelle Version von SQLcl findet sich auf der Produktseite von oracle.com und ist im Bundle mit SQL Developer, Oracle Database, OCI Cloud Shell, OCI Linux Yum Repository und dem OCI Developer Image auf dem Market Place erhältlich. 
Übrigens ist SQLcl unter die “Oracle Free Use Terms and Conditions License”  Bestimmungen gefallen. Das bedeutet. dass das Tool jetzt auch ohne manuelle Zustimmung heruntergeladen werden kann um automatisierte Downloads zu ermöglichen. Mittlerweile gibt es auch eine Datei mit Namen sqlcl-latest.zip, die immer die aktuellste Version enthält. Damit ist es einfach möglich, die letzte freigegebene Version des Tools automatisch herunterzuladen. Der Pfad zu dieser zip-Datei lautet: https://download.oracle.com/otn_software/java/sqldeveloper/sqlcl-latest.zip

Exzellente Beiträge in englischer Sprache zum Thema SQLcl findet man mit gängigen Internet-Suchmaschinen oder auf Twitter,, wenn man nach Einträgen der Produktmanager Jeff Smith, Kris Rice oder Gerald Venzl sucht. Am Ende dieses Beitrags sind einige Postings verlinkt.

Wie startet man mit SQLcl? Nutzt man Oracle Cloud Shell kann man sofort starten und einfach sql /nolog nach dem Cloud Shell Prompt eintippen.

Bild 1: SQLcl in der Cloud Shell

Oder man lädt sich die aktuelle zip-Datei, entpackt diese und startet zum Beispiel in einer Linux oder Windows Umgebung. Möchte man einen Überblick über die Funktionen bekommen, gibt man zuerst einmal das Kommando help ein.


Bild 2: SQLcl in der Windows Umgebung: Anzeige aller Kommandos

Wie man am Umfang der Kommandos erkennen kann, ist SQLcl mittlerweile zu einem mächtigen Werkzeug mit vielen Schnittstellen herangewachsen. Einige Kommandos sind sicherlich den SQL*Plus Anwendern bekannt, wie zum Beispiel: SET, CONNECT, DEFINE, EDIT, SHOW, SAVE, SPOOL, REMARK, LIST, RUN, START etc. Aber es gibt noch weitere Kommandos, die beim Skripten und Arbeiten mit SQL sehr hilfreich sind. 

Grundsätzlich gilt: Mit help <Kommando> wird eine Beschreibung und die Syntax des Kommandos ausgegeben, mit show <Kommando> kann man sich die aktuellen Einstellungen ansehen. Voreinstellungen wie zum Beispiel ALIAS- und SET-Einstellungen für alle SQLcl Sessions kann man in einer login.sql speichern. Die Umgebungsvariable SQLPATH verweist auf das Verzeichnis, in dem sich die login.sql befindet. 

C:\Data\sqlcl\sqlcl\bin>sql /nolog
Picked up JAVA_TOOL_OPTIONS: -Duser.language=de

SQLcl: Release 21.2 Production auf Di Jul 06 15:06:58 2021

Copyright (c) 1982, 2021, Oracle. All rights reserved. Alle Rechte vorbehalten.

SQL> show sqlpath
SQLPATH : C:\Data\sqlcl\sqlcl\bin\;.;C:\DATA\sqlcl

Einige interessante Anwendungen mit SQLcl beschreiben wir in den folgenden Abschnitten. Zur leichteren Verwendung sind die einzelnen Kapiteleinstiege verlinkt.

Connection mit Autonomous Database

Das Verbinden mit einer Autonomous Datenbank funktioniert ganz einfach mit dem Kommando set cloudconfig. Man lädt sich das zugehörige Instance-Wallet zuerst herunter und verweist mit dem set cloudconfig Kommando auf das entsprechende Wallet.zip-Verzeichnis.

C:\Data\sqlcl\sqlcl\bin>sql /nolog
SQLcl: Release 21.2 Production on Mon Jul 05 13:51:15 2021
Copyright (c) 1982, 2021, Oracle.  All rights reserved.

SQL> set cloudconfig c:\Data\sqlcl\sqlcl\bin\Wallet_DBJUN_neu.zip
SQL> connect admin/Monchen__223344@dbjun_medium
Connected.

Dienstprogramm OERR

Für diejenigen die OERR (Oracle ERRor message facility) nicht kennen: OERR ist ein Oracle-Dienstprogramm, das mit der Datenbank Software auf Unix Plattformen installiert wird und Fehlermeldungen mit Vorschlägen zur Behebung aus den Standard-Oracle-Message-Dateien extrahieren kann. Es gibt auch eine Schnittstelle in SQLcl dazu. Verwendet man die Option -v erhält man darüber hinaus auch die Informationen über das Datenbank Release.

SQL> oerr -v
19.3

SQL> oerr ora 2250

02250. 00000 -  "missing or invalid constraint name"
*Cause:    The constraint name is missing or invalid.
*Action:   Specify a valid identifier name for the constraint name.

Informationen über Datenbank-Objekte

Schnell  einmal Informationen zu einer Tabelle erhalten, geht im SQLcl ganz einfach. Mit ddl beispielsweise wird das zugehörige DDL Script ausgegeben; mit information oder info erhält man Details zu einer Tabelle und ihren Spalten. 

SQL> info dept
TABLE: DEPT
         LAST ANALYZED:2021-06-10 13:27:46.0
         ROWS         :4
         SAMPLE SIZE  :4
         INMEMORY     :DISABLED
         COMMENTS     :

Columns
NAME         DATA TYPE           NULL  DEFAULT    COMMENTS
*DEPTNO      NUMBER(2,0)         No
 DNAME       VARCHAR2(14 BYTE)   Yes
 LOC         VARCHAR2(13 BYTE)   Yes

Indexes
      INDEX_NAME    UNIQUENESS    STATUS    FUNCIDX_STATUS    COLUMNS
________________ _____________ _________ _________________ __________
ADMIN.PK_DEPT    UNIQUE        VALID                       DEPTNO

Möchte man das DDL-Kommando ohne Segment-Informationen generieren, kann man vorab eine entsprechende set ddl Einstellung absetzen.

SQL> set ddl segment_attributes off
DDL-Option SEGMENT_ATTRIBUTES off
SQL> ddl scott.dept

  CREATE TABLE "SCOTT"."DEPT"
   (    "DEPTNO" NUMBER(2,0),
        "DNAME" VARCHAR2(14) COLLATE "USING_NLS_COMP",
        "LOC" VARCHAR2(13) COLLATE "USING_NLS_COMP",
         CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
  USING INDEX  ENABLE
   )  DEFAULT COLLATION "USING_NLS_COMP" ;
SQL>

Ausgabe- Formatierung

Die Möglichkeiten der Ausgabe-Formatierung mit SET SQLFORMAT sind vielfältig – sei es HTML, CSV oder auch JSON. Sogar zugehörige INSERT-Kommandos können damit generiert werden.

SQL> help set sqlformat
SET SQLFORMAT
  SET SQLFORMAT { default,csv,html,xml,json,fixed,insert,loader,delimited,ansiconsole}


SQL> set sqlformat JSON

SQL> select empno, ename from emp;
{"results":[{"columns":[{"name":"EMPNO","type":"NUMBER"},{"name":"ENAME","type":"VARCHAR2"}],"items":

[
{"empno":7369,"ename":"SMITH"}
,{"empno":7499,"ename":"ALLEN"}
,{"empno":7521,"ename":"WARD"}
,{"empno":7566,"ename":"JONES"}
...
]}]}

SQL> set SQLFORMAT INSERT
SQL> select empno, ename from emp;
REM INSERTING into EMP
SET DEFINE OFF;
Insert into EMP (EMPNO,ENAME) values ('7369','SMITH');
Insert into EMP (EMPNO,ENAME) values ('7499','ALLEN');
Insert into EMP (EMPNO,ENAME) values ('7521','WARD');
Insert into EMP (EMPNO,ENAME) values ('7566','JONES');
...

Database Schema Änderungen mit Liquibase

SQLcl besitzt ab Version 19.2 eine Schnittstelle zu Open Source Liquibase. Zur Erinnerung: Liquibase ist eine datenbankunabhängige Open-Source-Bibliothek für das Management und die Nachverfolgungen von Modifikationen in Datenbankschemata. Insbesondere im Kontext agiler Softwareentwicklung ist sie ausgesprochen hilfreich, weil sie für mehr Transparenz und ein besseres zurückverfolgen von Datenbankänderungen sorgt. Liquibase erlaubt es dem Entwickler, sogenannte Changesets für die Datenbank zu schreiben. Die Changesets werden dann der Reihe nach eingelesen und ausgeführt. Liquibase überwacht dabei, welche Changesets bereits ausgeführt worden sind.

Mit der Liquibase-Funktion in SQLcl kann man Befehle ausführen, um ein Changelog für ein einzelnes Objekt oder für ein ganzes Schema (Changeset und Changelogs) zu erzeugen. Diese Objekte kann man dann auch manuell mit SQLcl oder über eine der traditionellen Liquibase-Schnittstellen bearbeiten. Um Liquibase zu verwenden, nutzt man das Kommando lb oder liquibase mit den entsprechenden Optionen. Ein einfaches Beispiel soll die Erzeugung eines Changelogs demonstrieren, um eine Tabelle EMP in einem weiteren Datenbank Schema anzulegen.

Folgendes Beispiel legt die Changelog-Datei für das Objekt EMP an. Die Changelog-Dateien liegen im XML-Format vor.

SQL> lb genobject -type table -name emp

Action successfully completed please review created file emp_table2.xml

Changelog-Dateien für das gesamte Schema lassen sich folgendermaßen generieren.

SQL> lb genschema
[Method loadCaptureTable]:
                 Executing
[Type - TYPE_SPEC]:                           91 ms
[Type - TYPE_BODY]:                           62 ms
[Type - SEQUENCE]:                            44 ms
[Type - DIRECTORY]:                           45 ms
[Type - CLUSTER]:                            924 ms
[Type - TABLE]:                              562 ms
[Type - MATERIALIZED_VIEW_LOG]:               45 ms
[Type - MATERIALIZED_VIEW]:                   33 ms
[Type - VIEW]:                               148 ms
...

Nun verbinden wir uns mit einem anderen User, der keine EMP Tabelle besitzt und wenden die Changelog-Datei zum Anlegen der Tabelle EMP an .

SQL> desc emp
ERROR:
ORA-04043: Objekt emp ist nicht vorhanden

SQL> lb update -changelog emp_table2.xml
ScriptRunner Executing: emp_table2.xml::b618761d0c538606b8b0eb7a59b70c91224e1ec8::(SCOTT)-Generated -- DONE

######## ERROR SUMMARY ##################
Errors encountered:0

######## END ERROR SUMMARY ##################

SQL> desc emp
Name     Null?    Typ
-------- -------- ------------
EMPNO    NOT NULL NUMBER(4)
ENAME             VARCHAR2(10)
JOB               VARCHAR2(9)
MGR               NUMBER(4)
HIREDATE          DATE
SAL               NUMBER(7,2)
COMM              NUMBER(7,2)
DEPTNO            NUMBER(2)

Weitere Beispiele und umfangreiche Erläuterungen finden sich im Handbuch im Kapitel Liquibase Support in SQLcl

JSON mit der Soda-Schnittstelle

SODA (kurz für Simple Oracle Document Access) ermöglicht eine schemalose Anwendungsentwicklung unter Verwendung des JSON-Datenmodells. Wie immer zeigt help soda alle Möglichkeiten der Verwendung in SQLcl auf.
Wir haben einige Postings zum Thema SODA in der Oracle Datenbank auf unserer deutschsprachigen Blog Plattform veröffentlicht. So hat mein Kollege Marcus Schröder einige Beispiele in seinem Beitrag
Simple Oracle Document Access (SODA) ausprobieren? dazu veröffentlicht. Daher zeige ich an dieser Stelle nur ein paar kurze Beispiele zum Anlegen und Auflisten einer Collection und Einfügen von JSON.

SQL> soda list
List of collections:

        PURCHASEORDERS
        empcol

SQL> soda create employees

 Successfully created collection: employees
SQL> desc employees
Name          Null?    Type
------------- -------- -------------
ID            NOT NULL VARCHAR2(255)
CREATED_ON    NOT NULL TIMESTAMP(6)
LAST_MODIFIED NOT NULL TIMESTAMP(6)
VERSION       NOT NULL VARCHAR2(255)
JSON_DOCUMENT          BLOB

SQL> soda list
List of collections:

        PURCHASEORDERS
        empcol
        employees

SQL> soda insert employees {"name" : "SMITH", "department" : 20}

 JSON document inserted successfully.

Statusbar, Editor vi und Syntax-Highlighting

Jetzt ist es endlich soweit: Mit SQLcl kann man Highlighting und jetzt auch vi Funktionen verwenden :). In der aktuellen Version 21.2 sind diese Funktionen eingearbeitet worden. Für ein Hervorheben (auch Highlighting) benötigt man die Einstellung highlighting, für die Verwendung von vi-Funktionen die Einstellung statusbar. Wie immer kann man mit help und show die aktuellen Einstellungen bzw. die Verwendungsmöglichkeiten ausgeben.

SQL> show highlighting
HIGHLIGHTING: off
SQL> help set highlighting
set highlighting 
  
                | 
  
    RESET
                | 
   
     FOREGROUND 
    
     
                | 
     
       BACKGROUND 
      
        | 
       
         BOLD 
        
          | 
         
           ULINE 
          
            | 
           
             INVERSE 
             
             
               = DEFAULT | COMMENT | STRING | NUMBER | PUNCTUATION | KEYWORD | IDENTIFIER 
              
                = RED | BLUE | BLACK | CYAN | GREEN | MAGENTA | WHITE | YELLOW 
               
                 = ON | OFF | RESET SQL> set highlighting on SQL> set highlighting keyword foreground red

Und schon kann es losgehen: alle Schlüsselwörter sind jetzt in roter Farbe.

Möchte man Kommandofunktionen von vi oder emacs verwenden, ist die Verwendung der Statusanzeige erforderlich. Die Standardanzeige informiert über den verwendeten Modus wie zum Beispiel viins oder vicmd, die Zeilen- und Spaltenposition des Cursors, den aktuellen User und den verwendeten Servicenamen. Folgender Screenshot zeigt ein Beispiel mit eingeschalteter Default Status-Anzeige.


Bild 3: Syntax Highlighting und Statusbar

Es gibt natürlich noch weitere sinnvolle Einstellungen. So kann man das Timing und den Transaktionsstand anzeigen.

set statusbar add TXN
set statusbar add TIMING

Und das Ergebnis sieht dann zum Beispiel so aus.


Bild 4: Status-Anzeige mit Timing und TXN 

Mehr dazu auch mit animierten GIFs findet sich im Posting von Jeff Smith unter Oracle SQLcl: Syntax Highlighting and the Status Bar.

Fazit

SQLcl steht schnell und einfach zur Verfügung und ist für “Liebhaber” von Linemode Werkzeugen eine gute Wahl, wenn es um das Entwickeln und Arbeiten mit der Oracle Datenbank geht. SQLcl kann natürlich noch viel mehr als im Blogeintrag gezeigt werden konnte. So gibt es weitere Schnittstellen zu Javascript, ORDS, APEX, SQL Developer Data Modeler, Property Graph Query Language (PGQL) und natürlich auch zu OCI und Cloud Object Storage. Beispielskripte sind auf Github oder in zahlreichen Blogeinträgen zu finden. Am Besten dazu im Internet recherchieren oder die aufgeführten Links verwenden. Es lohnt sich übrigens wie bei den anderen downloadbaren Oracle Tools – SQL Developer  oder Instant Clients – die aktuelle Version zu verwenden, um von einem Maximum an Funktionalität zu profitieren. Also gleich downloaden und die aktuelle Version ausprobieren :).

Informationen und Links