SQLcl - Die nächste Generation von SQLPlus Version4-dna

SQLcl – Die nächste Generation von SQLPlus
Nachbar Dirk
Senior Consultant / Oracle ACE
Oktober 2015
Generationen von Datenbankadministratoren haben über die letzten Jahre versucht
SQLPlus benutzerfreundlicher zu machen, mittels selbst entworfener Skripte, mit der
Einbindung von RLWRAP usw. Seit Anfang 2015 gibt es „The Next Generation“ von
SQLPlus: SQLcl. Das neue Command Line Tool SQLcl wurde aus dem populären SQL
Developer abgeleitet.
1.
Die Vorbereitungen
Download von 12MB, Unzip, kontrollieren ob Java im Zugriffpfad vorhanden ist. That’s it …
Zunächst einmal müssen wir die SQLcl Zip Datei von Oracle Technology Network
herunterladen. Das SQLcl ist zu finden in der Download Rubrik des SQL Developers
http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html
Die Zip Datei kann für Windows sowie UNIX Umgebungen verwendet werden.
Als nächstes muss die Zip Datei entpackt werden und anschliessend finden wir unter dem
Verzeichnis sqlcl folgende Verzeichnisstruktur:
Im Unterverzeichnis bin befinden sich 3 Dateien:
• sql: Ein Shell Skript für die Ausführung von SQLcl unter UNIX
• sql.bat: Eine Windows Ausführungsdatei für die Ausführung von SQLcl unter Windows
• sql.exe: Ein Ausführungsprogramm von SQLcl für Windows
Für Windows stehen wahlweise die sql.bat oder das Ausführungsprogramm sql.exe zur
Verfügung. Innerhalb der sql.bat können im Gegensatz zum Ausführungsprogramm sql.exe
noch persönliche oder notwendige Erweiterungen konfigurieren wie z.B. CLASSPATH
Erweiterungen.
Im Unterverzeichnis lib befinden sich die benötigten jar-Dateien für SQcl (z.B. ojdbc6.jar).
[email protected] . www.trivadis.com . Info-Tel. 0800 87 482 347 . Datum 10.11.2015 . Seite 2 / 14
2.
Benutzung von SQLcl
Auf den ersten Blick sieht SQLcl nicht viel anders aus als das klassische SQLPlus.
Das Anmelden an eine Oracle Datenbank kann über verschiedene Methoden durchgeführt
werden:
sql.bat <username>/<password>@[Net Service Name | //<hostname|IP>:<Listener
Port>/<Service Name>]
# Zum Beispiel via Easy Connect mit Password
sql.bat hr/Oracle12c@dbdin01:1521/DB12102.tvd.ch
SQLcl: Release 4.2.0.15.275.1225 RC on Thu Oct 15 10:27:29 2015
Copyright (c) 1982, 2015, Oracle.
All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing
options
SQL>
# Zum Beispiel via Easy Connect ohne Password
sql.bat hr @dbdin01:1521/DB12102.tvd.ch
SQLcl: Release 4.2.0.15.275.1225 RC on Thu Oct 15 10:27:29 2015
Copyright (c) 1982, 2015, Oracle.
All rights reserved.
Password? (**********?) *********
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing
options
SQL>
Eine Anmeldung als SYSDBA, SYSOPER und SYSASM sind. Um sich alle Aufrufoptionen für
SQLcl anzeigen zu lassen kann man SQLcl mit der Option -H aufrufen:
sql.bat -H
SQLcl: Release 4.2.0.15.278.1216 RC
Usage 1: sql -H | -V
-H
-V
Usage 2: sql
<option> is:
. . .
. . .
Displays the SQLcl version and the
usage help.
Displays the SQLcl version.
[<option>] [{ <logon> | /nolog}] [<start>]
[-R <level>] [-S] [-verbose] [-oci] [-L[OGON] ]
Was ist neu im SQLcl im Vergleich zum klassischen SQLPlus? Ein kurzer Blick in die Hilfe
Funktion zeigt uns alle verfügbaren Optionen. Im Nachfolgenden sind nur die neuen Optionen
aufgelistet; die gesamte Anzahl von Optionen beläuft sich auf 62 Optionen:
[email protected] . www.trivadis.com . Info-Tel. 0800 87 482 347 . Datum 10.11.2015 . Seite 3 / 14
SQL> help
For help on a topic type help <topic>
List of Help topics available:
ALIAS
APEX
BRIDGE
CD
CTAS
DDL
FORMAT
HISTORY
INFORMATION
LOAD
NET
OERR
REPEAT
REST
SSHTUNNEL
TNSPING
Wie man schon aus den Namen der neuen Optionen erkennen kann, befinden sich darunter
einige sehr interessante neue Optionen, welche in das SQLcl implementiert wurden.
Hier ein paar der nützlichsten neuen Optionen von SQLcl im Detail mit einigen
Anwendungsbeispielen.
ALIAS Option:
Der Name ALIAS verrät schon worum es bei dieser neuen Option geht: einen Alias wie unter
UNIX einrichten hinter dem sich eine vollständige Anweisung verbirgt.
[email protected] . www.trivadis.com . Info-Tel. 0800 87 482 347 . Datum 10.11.2015 . Seite 4 / 14
# Hilfe zu ALIAS Option anzeigen
SQL> help alias
# Einen einfachen Alias anlegen
SQL> alias dual=select * from dual;
# Alias ausführen
SQL> dual
Command=dual
D
X
# Alias mit Bind Variablen anlegen
SQL> alias emp_id=select employee_id, first_name, last_name from employees
where employee_id = :one;
SQL> emp_id 100
Command=emp_id
EMPLOYEE_ID FIRST_NAME
LAST_NAME
----------- -------------------- ------------------------100 Steven
King
# Alias mit einem PL/SQL Block anlegen:
SQL> alias hello_world=begin dbms_output.put_line('Hello World'); end;
2 /
SQL> set SERVEROUTPUT on
SQL> hello_world
Command=hello_world
PL/SQL procedure successfully completed.
Hello World
CD Option:
Hinter dieser Option verbirgt sich ein simples CD (Change Directory) Kommando, wie wir es
auch aus UNIX oder Windows kennen, nur dieses kann innerhalb unseres SQLcl ausgeführt
werden.
Klassischerweise startet man seine SQL Session aus einem Arbeitsverzeichnis heraus, z.B.
c:\work\project1\scripts. Innerhalb dieses Verzeichnisses hat man normalerweise
Unterverzeichnisse z.B. tables, indexes, data usw. In einem Hauptskript würde man folgendes
definieren:
[email protected] . www.trivadis.com . Info-Tel. 0800 87 482 347 . Datum 10.11.2015 . Seite 5 / 14
# Project Create Script
PROMPT Create Project Tables
@tables/01_tables.sql
PROMPT Create Project Indexes
@indexes/02_indexes.sql
PROMPT Load Project Data
@data/03_data.sql
Unter Verwendung der neuen Option CD würde mein Skript folgendermassen aussehen
# Project Create Script
PROMPT Create Project Tables
cd tables
@01_tables.sql
PROMPT Create Project Indexes
cd ../indexes
@ 02_indexes.sql
PROMPT Load Project Data
cd ../data
@ 03_data.sql
Option CTAS:
Eine der Besten neuen Optionen innerhalb des SQLcl. CTAS steht für "Create Table As
Select". Hierüber kann man sich in einer relative simplen Variante ein vollständiges SQL
Statement für ein Duplizieren einer Tabelle generieren.
[email protected] . www.trivadis.com . Info-Tel. 0800 87 482 347 . Datum 10.11.2015 . Seite 6 / 14
# Hilfe zu CTAS Option anzeigen
SQL> help CTAS
# CTAS Skript generieren
SQL> CTAS employees employees_copy
CREATE TABLE "HR"."EMPLOYEES_COPY"
(
"EMPLOYEE_ID",
"FIRST_NAME",
"LAST_NAME",
"EMAIL",
"PHONE_NUMBER",
"HIRE_DATE",
"JOB_ID",
"SALARY",
"COMMISSION_PCT",
"MANAGER_ID",
"DEPARTMENT_ID",
CONSTRAINT "EMP_SALARY_MIN" CHECK (salary > 0) ENABLE,
CONSTRAINT "EMP_EMAIL_UK" UNIQUE ("EMAIL")
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,
CONSTRAINT "EMP_EMP_ID_PK" PRIMARY KEY ("EMPLOYEE_ID")
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 EMPLOYEES
Nun kann man via EDIT Option die notwendigen Anpassungen an dem zuvor generierten
Skript vornehmen, z.B. Anpassungen der Constraint Namen usw. und anschliessend das
Skript ausführen.
DDL Option:
Mit der Option DDL kann man sich Data Definition Language Code generieren lassen für
Objekte und optional auch in eine Datei speichern.
[email protected] . www.trivadis.com . Info-Tel. 0800 87 482 347 . Datum 10.11.2015 . Seite 7 / 14
# Hilfe zu Option DDL anzeigen
SQL> help DDL
# DDL Code generieren
SQL> DDL EMP_DEPARTMENT_IX
CREATE INDEX "HR"."EMP_DEPARTMENT_IX" ON "HR"."EMPLOYEES"
("DEPARTMENT_ID")
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" ;
# DDL Code generieren und in eine Datei speichern
SQL> DDL EMP_DEPARTMENT_IX SAVE emp_department_ix.sql
HISTORY Option:
Eine weitere interessante Option ist die Option HISTORY, hierfür haben Generationen von
DBA’s RLWRAP eingebunden in das klassische SQLPlus. Mit SQLcl wird dies nun überflüssig,
da hierfür die Option HISTORY zur Verfügung steht.
In der HISTORY werden die letzten 100 Anweisungen gespeichert.
[email protected] . www.trivadis.com . Info-Tel. 0800 87 482 347 . Datum 10.11.2015 . Seite 8 / 14
# Hilfe zu Option HISTORY anzeigen
SQL> help HISTORY
# History Inhalt anzeigen
SQL> HISTORY
1 select * from cat
2 select employee_id, first_name, last_name from employees where
employee_id = 100
3 host cls
4 select * from departments
# History mit Ausführungszeiten anzeigen lassen
SQL> HISTORY TIME
1 (00.344) select * from cat
2 (00.028) select employee_id, first_name, last_name from employees where
employee_id = 100
3
host cls
4 (00.249) select * from departments
# Ein Kommando aus der History in den Buffer laden
SQL> HISTORY 2
1* select employee_id, first_name, last_name from employees where
employee_id = 100
# Anschliessend kann das geladene SQL Kommando mit / ausgeführt werden
SQL> /
EMPLOYEE_ID FIRST_NAME
LAST_NAME
----------- -------------------- ------------------------100 Steven
King
# Anzeigen von Anzahl der Ausführungen der Kommandos in der HISTORY
SQL> HISTORY USAGE
1 (1) select * from cat
2 (1) host cls
3 (3) select employee_id, first_name, last_name from employees where
employee_id = 100
4 (3) select * from departments
INFORMATION Option:
Bei der Option INFORMATION handelt es sich um ein erweitertes DESCRIBE. Während bei
einem klassischen DESCRIBE lediglich rudimentäre Informationen zum Objekt angezeigt
werden, werden bei der neuen Option INFORMATION wesentlich mehr Informationen
angezeigt, wie z.B. Indexes einer Tabelle, Constraints usw.
Die Option INFORMATION kommt in zwei Varianten. Die normale Option INFO[RMATION]
zeigt detaillierte Informationen zu einem Objekt an, INFO+ zeigt Spalten Statistiken an.
[email protected] . www.trivadis.com . Info-Tel. 0800 87 482 347 . Datum 10.11.2015 . Seite 9 / 14
# Klassisches DESCRIBE
SQL> DESCRIBE jobs
Name
---------JOB_ID
JOB_TITLE
MIN_SALARY
MAX_SALARY
Null
-------NOT NULL
NOT NULL
Type
-----------VARCHAR2(10)
VARCHAR2(35)
NUMBER(6)
NUMBER(6)
# Neue Option INFORMATION
SQL> INFORMATION jobs
TABLE: JOBS
LAST ANALYZED:2015-10-08 14:10:20.0
ROWS
:19
SAMPLE SIZE :19
INMEMORY
:DISABLED
COMMENTS
:jobs table with job titles and salary ranges. Contains 19 rows.
References with employees and job_history table.
Columns
NAME
*JOB_ID
JOB_TITLE
MIN_SALARY
MAX_SALARY
Indexes
INDEX_NAME
HR.JOB_ID_PK
References
TABLE_NAME
EMPLOYEES
JOB_HISTORY
DATA TYPE
VARCHAR2(10 BYTE)
VARCHAR2(35 BYTE)
NULL
No
No
NUMBER(6,0)
NUMBER(6,0)
Yes
Yes
UNIQUENESS
UNIQUE
STATUS
VALID
CONSTRAINT_NAME
EMP_JOB_FK
JHIST_JOB_FK
DEFAULT
COMMENTS
Primary key of jobs table.
A not null column that shows job title,
e.g. AD_VP, FI_ACCOUNTANT
Minimum salary for a job title.
Maximum salary for a job title
FUNCIDX_STATUS
DELETE_RULE
NO ACTION
NO ACTION
STATUS
ENABLED
ENABLED
COLUMNS
JOB_ID
COLUMN_EXPRESSION
DEFERRABLE
NOT DEFERRABLE
NOT DEFERRABLE
VALIDATED
VALIDATED
VALIDATED
GENERATED
USER NAME
USER NAME
# Option INFORMATION angewendet auf ein PL/SQL Paket
SQL> INFORMATION dbms_output.put_line
Package
/* Package SYS.DBMS_OUTPUT */
/* PROCEDURE SYS.DBMS_OUTPUT.PUT_LINE */
SYS.DBMS_OUTPUT.PUT_LINE(
A
=> p_IN_param0
/*
VARCHAR2
*/);
# Anzeigen von Spalten Statistiken via INFO+ Option
SQL> INFO+ jobs
TABLE: JOBS
LAST ANALYZED:2015-10-08 14:10:20.0
ROWS
:19
SAMPLE SIZE :19
INMEMORY
:DISABLED
COMMENTS
:jobs table with job titles and salary ranges. Contains 19 rows.
References with employees and job_history table.
Columns
NAME
*JOB_ID
JOB_TITLE
MIN_SALARY
MAX_SALARY
Indexes
INDEX_NAME
HR.JOB_ID_PK
DATA TYPE
VARCHAR2(10 BYTE)
VARCHAR2(35 BYTE)
NUMBER(6,0)
NUMBER(6,0)
UNIQUENESS
UNIQUE
STATUS
VALID
NULL
No
No
Yes
Yes
DEFAULT
LOW_VALUE
AC_ACCOUNT
Accountant
2008
5000
FUNCIDX_STATUS
COLUMNS
JOB_ID
HIGH_VALUE
ST_MAN
Stock Manager
20080
40000
NUM_DISTINCT HISTOGRAM
19
NONE
19
NONE
14
NONE
13
NONE
COLUMN_EXPRESSION
[email protected] . www.trivadis.com . Info-Tel. 0800 87 482 347 . Datum 10.11.2015 . Seite 10 / 14
References
TABLE_NAME
EMPLOYEES
JOB_HISTORY
CONSTRAINT_NAME
EMP_JOB_FK
JHIST_JOB_FK
DELETE_RULE
NO ACTION
NO ACTION
STATUS
ENABLED
ENABLED
DEFERRABLE
NOT DEFERRABLE
NOT DEFERRABLE
VALIDATED
VALIDATED
VALIDATED
GENERATED
USER NAME
USER NAME
LOAD Option:
Mit der Option LOAD kann man CSV (Comma Separated Values) Dateien in eine bestehende
Tabelle laden. Dabei muss die erste Zeile der CSV Datei die Spalten der zu beladenden
Tabelle widerspiegeln.
# Hilfe zu Option LOAD anzeigen
SQL> help LOAD
# Tabelle BEERS anlegen
SQL> CREATE TABLE BEERS ( ID NUMBER(10)
,BEER_NAME VARCHAR2(50)
,COUNTRY VARCHAR2(50))
;
# CSV Datei beer_data.csv
ID,BEER_NAME,COUNTRY
1,Flensburger,Germany
2,Heineken,Netherland
3,Red Horse,Philippines
4,Feldschlösschen, Suisse
5,Tsingtao,China
6,Miller,United States
# Beladen der Tabelle BEERS mit der Option LOAD
SQL> LOAD BEERS beer_data.csv
--Number of rows processed: 6
--Number of rows in error: 0
0 - SUCCESS: Load processed without errors
SQL> select * from beers;
ID
---------1
2
3
4
5
6
BEER_NAME
--------------Flensburger
Heineken
Red Horse
Feldschlösschen
Tsingtao
Miller
COUNTRY
-----------------Germany
Netherland
Philippines
Suisse
China
United States
Die oben gezeigten neuen Optionen sind schon einen Wechsel vom klassischen SQLPlus zum
neuen SQLcl wert, doch es gibt noch ein weiteres Highlight, welches sich unter der
altbekannten Option SET verbirgt.
SET SQLFORMAT Option:
Fast jeder DBA oder Developer ärgert sich regelmässig über die Darstellung von Ergebnissen
in SQLPlus. Spalten müssen via COL FORMAT lesbar gemacht werden oder über
umständliche Konstrukte um CSV Daten zu erzeugen. DBA’s oder Developer welche den SQL
[email protected] . www.trivadis.com . Info-Tel. 0800 87 482 347 . Datum 10.11.2015 . Seite 11 / 14
Developer einsetzen wissen wie leicht man hier zum Beispiel via /* CSV */ eine CSV Ausgabe
erzeugen kann. Diese Optionen aus dem SQL Developer wurden in das neue SQLcl integriert
über die SET SQLFORMAT Option.
Folgende Optionen sind vorhanden für die Option SET SQLFORMAT:
• csv, html, xml, json, ansiconsole, insert, loader, fixed, default
[email protected] . www.trivadis.com . Info-Tel. 0800 87 482 347 . Datum 10.11.2015 . Seite 12 / 14
# Hilfe zu Option SET SQLFORMAT anzeigen
SQL> help SET SQLFORMAT
# Ausgabe eines SELECT in CSV Format
SQL> set sqlformat csv
SQL> select * from jobs;
"JOB_ID","JOB_TITLE","MIN_SALARY","MAX_SALARY"
"AD_PRES","President",20080,40000
"AD_VP","Administration Vice President",15000,30000
"AD_ASST","Administration Assistant",3000,6000
"FI_MGR","Finance Manager",8200,16000
"FI_ACCOUNT","Accountant",4200,9000
"AC_MGR","Accounting Manager",8200,16000
"AC_ACCOUNT","Public Accountant",4200,9000
"SA_MAN","Sales Manager",10000,20080
. . .
. . .
# Ausgabe eines SELECT in json
SQL> set sqlformat json
SQL> select * from jobs;
{"items":
[
{"job_id":"AD_PRES","job_title":"President","min_salary":20080,"max_salary":
40000},{"job_id":"AD_VP","job_title":"Administration Vice
President","min_salary":15000,"max_salary":30000},{"job_id":"AD_ASST","job_t
itle":"Administration
Assistant","min_salary":3000,"max_salary":6000},{"job_id":"FI_MGR","job_titl
e":"FinanceManager","min_salary":8200,"max_salary":16000},{"job_id":"FI_ACCO
UNT","job_title":"Accountant","min_salary":4200,"max_salary":9000},{"job_id"
:"AC_MGR","job_title
. . .
. . .
# Ausgabe eines SELECT in insert’s
SQL> set sqlformat insert
SQL> select * from jobs;
REM INSERTING into jobs
SET DEFINE OFF;
Insert into "jobs" (JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY)
('AD_PRES','President',20080,40000);
Insert into "jobs" (JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY)
('AD_VP','Administration Vice President',15000,30000);
Insert into "jobs" (JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY)
('AD_ASST','Administration Assistant',3000,6000);
Insert into "jobs" (JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY)
('FI_MGR','Finance Manager',8200,16000);
. . .
. . .
values
values
values
values
Ein weiteres bereits erwähntes Problem ist die Darstellung von Ergebnislisten in SQLPlus,
normalerweise formatiert man die entsprechenden Spalten via COL FORMAT Optionen. Unter
[email protected] . www.trivadis.com . Info-Tel. 0800 87 482 347 . Datum 10.11.2015 . Seite 13 / 14
SQLcl entfällt dies, hierfür wurde eine spezielle SET SQLFORMAT Variante implementiert:
ANSICONSOLE
# Typisches Anzeigeproblem einer Ergebnisliste
SQL> select * from employees fetch first 2 rows only;
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-03
AD_PRES
24000
90
101 Neena
NKOCHHAR
AD_VP
Kochhar
515.123.4568
17000
21-SEP-05
100
90
# Problemlösung der Anzeige mit SQLcl Option ANSICONSOLE
SQL> set sqlformat ansiconsole
SQL> select * from employees fetch first 2 rows only;
3.
Fazit
Mit dem neuen SQLcl hat das altgediente SQLPlus nunmehr ein wirkliches Facelifting erhalten
mit den Standardfunktionen aus dem graphischen SQL Developer, die nun auch auf
Kommandozeilen Ebene benutzbar sind. Mit dem neuen SQLcl hat der DBA oder Developer
nunmehr die Möglichkeit zeitgemäss auf Kommandozeilen Ebene zu arbeiten und altbekannte
Probleme mit den neuen Optionen des SQLcl zu eliminieren.
Viel Erfolg beim Einsatz von Trivadis-Know-how wünscht Ihnen
Dirk Nachbar
Trivadis AG
Weltpoststrasse 5
CH-3015 Bern
Internet: www.trivadis.com
Tel:
Fax:
Mail:
+41-58-459 56 36
+41-58-459 56 66
[email protected]
[email protected] . www.trivadis.com . Info-Tel. 0800 87 482 347 . Datum 10.11.2015 . Seite 14 / 14