Oracle: SQL*PLUS

Vor zu " Einführung Oracle SQL Developer "

Aufruf-Möglichkeiten

iSQL

iSQL ist eine Möglichkeit, mit dem Datenbankserver Kontakt aufzunehmen alleine über einen beliebigen Web-Browser. Es muss keine Software auf dem Client installiert werden. Diese Möglichkeit gibt es jedoch nur bei der Version 10g. Schon bei der Version 11g gibt es diese Möglichkeit nicht mehr.

Aufruf bei Oracle 10g über die URL: http://<host>:5560/isqlplus Dabei muss anstelle von <host> der Computername angegeben werden. Den Computernamen eines Servers, an dem man gerade angemeldet ist, kann man bei Windows herausfinden durch:

Start / Einstellungen / Systemsteuerung / System

In Reiter Netzwerkidentifikation findet man den Computernamen angegeben.

Über den Web-Browser kann man dadurch auf alle Datenbank-Server zugreifen, deren Servername man kennt und bei denen eine Anmeldung über iSQL und den Port 5560 nicht explizit deaktiviert ist.

Natürlich ist eine Identifikation an diesem Server mit einer Oracle-User-ID und Passwort auch noch erforderlich, aber viele Datenbanken haben ja den User SCOTT mit dem Passwort TIGER...

Über diesen Aufruf kann man sich nicht als SYSDBA oder SYSOPER anmelden.

iSQL/dba

Um administrative Arbeiten auszuführen, die eine Anmeldung AS SYSDBA oder AS SYSOPER erfordern, kann die folgende URL benutzt werden: http://<host>:5560/isqlplus/dba

Nach einem erfoglreichen Connect mit einem User in der SYSDBA-Rolle kann die Datenbank z.B. heruntergefahren werden oder neu gestartet werden.

SQL*Plus als Windows-Tool

Das SQL*Plus-Worksheet war lange Zeit die im Oracle-Lieferumfang gehörende Standard-Client-Software, um SQL-Befehle ausführen zu lassen. Seit der Version 11g gibt es diese Möglichkeit nicht mehr. An die Stelle dessen tritt nun der Oracle SQL Developer.

Aufrufmöglichkeiten:

Als Befehl in einem CMD-Fenster: SQLPLUSW
Start / Ausführen / SQLPLUSW
Start / Programme / Oracle / Anwendungsentwicklung / SQL Plus
Oder C:\oraclexe\app\oracle\product\11.2.0\server\bin\sqlplus.exe

vignette

SQL*Plus als Command-Line-Tool

Als Befehl in einem CMD-Fenster: SQLPLUS. Dadurch wird die Verwendung von SQL*Plus per Shell-/Batchskript ermöglicht. Diese Variante gab es schon immer und sie ist auch in der aktuellen Version (11g) noch vorhanden.

Als Befehlsparameter kann man einen Connect-String angeben. Angenommen, man möchte eine Anmeldung als User SCOTT (mit dem Passwort TIGER) an einer lokalen Datenbank vornehmen, dann sind folgende Aufrufe möglich:

SQLPLUS SCOTT/TIGER

SQLPLUS SCOTT

Wenn man das Passwort nicht direkt angibt, wird man anschließend aufgefordert, das Passwort einzugeben. Der Vorteil dabei ist, dass das eingegebene Passwort nicht auf dem Bildschirm sichtbar ist. Da wir in diesem Beispiel den Datenbank-Namen nicht angegeben haben, verwendet SQL*Plus den in der Registry angegebenen Datenbank-Namen.

Aufbau zu einer entfernten Datenbank d.h. zu einem Datenbank-Server, der über das Netzwerk zu erreichen ist und der in der lokal gespeicherten Datei TNSNAMES.ORA eingetragen ist. Der Name der Datenbank ist z.B. ORCL. Folgende Aufrufe sind möglich:

SQLPLUS SCOTT/TIGER@ORCL

SQLPLUS SCOTT@ORCL

Man kann auch SQL*Plus starten ohne ein Login auszuführen:

SQLPLUS /NOLOG

Danach ist SQL*Plus gestartet, aber es besteht noch keine Verbindung zu einer Datenbank. In diesem Zustand kann man lediglich die SQL*Plus-Umgebungsvariablen abfragen oder ändern und eben einen Connect-Befehl anweisen z.B.:

CONNECT SCOTT/TIGER@ORCL
INSERT ...;
COMMIT;
DISCONNECT
EXIT

Das gibt einem die Möglichkeit, mehrere SQL-Befehle in einer Datei als Skript zu speichern und den Connect-Befehl ebenfalls im Skript anzugeben. Wenn das Skript dann noch mit dem Befehl disconnect endet, dann ist es eine in sich abgeschlossene Einheit, die eine Verbindung zu einer Datenbank aufbaut, eine Verarbeitung ausführt und die Verbindung wieder beendet. Wenn man beim Aufruf von SQL*Plus gleich schon das Passwort mitangibt, dann hat das den Nachteil, dass bei MS-Windows das Passwort im Window-Header sichtbar ist.

SQL*Plus-Session beenden

Alle SQL*Plus-Varianten können mit dem Befehl EXIT oder QUIT wieder beendet werden.

Sowohl das Beenden über EXIT oder QUIT, als auch das sofortige schliessen (in Windows) über Fenster schliessen / x-Button führt implizit ein commit auf dem Datenbank-Server aus.

Interaktives Arbeiten

Allgemeine Formatierung ändern

Nach dem Aufruf einer SQL*Plus-Session ist es in vielen Fällen erforderlich, die Formatierung der Server-Ausgaben zu ändern.

Die Formatierung wird im SQL*Plus in Systemvariablen (auf dem Client-Computer) gespeichert.

Die Systemvariable LINESIZE gibt an, wie viele Zeichen in einer Zeile maximal ausgegeben werden sollen. Wenn die Ausgabezeile länger ist, dann wird der Rest in der nächsten Zeile ausgegeben. Der Default-Wert dieser Systemvariablen ist 80.

Die Systemvariable PAGESIZE gibt an, nach wievielen Ausgabezeilen die Überschrift erneut ausgegeben werden soll. Der Default-Wert dieser Systemvariablen ist 14.

Die Inhalte der SQL*Plus-Systemvariablen kann man mit dem Befehl SHOW ausgeben lassen:

SQL> show linesize
linesize 80
SQL> show pagesize
pagesize 14
SQL>

Wenn viele Tabellenspalten ausgegeben werden sollen, dann können die vielen Zeilenumbrüche störend sein. Auch die wiederholte Ausgabe der Spaltenüberschriften kann störend wirken.

Die SQL*Plus-Systemvariablen kann man mit dem SET-Befehl ändern. Die Änderung ist jedoch nur für die aktuelle Session aktiv:

SQL> set linesize 9000
SQL> set pagesize 9000

Es gibt noch wesentlich mehr Systemvariablen, mit denen man die Formatierung beeinflussen kann. Eine vollständige Beschreibung findet man im SQL*Plus-Manual. (Siehe Literatur-Liste)

Formatierung bestimmter Spalten ändern

Wenn Daten durch den SELECT-Befehl ausgegeben werden, dann wird für jede Spalte so viel Platz gelassen, dass auch ein Wert mit maximaler Länge ausgegeben werden kann.

Beispiel:

select empno, ename, sal from emp;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH             800
      7499 ALLEN            1600
      7521 WARD             1250
      7566 JONES            2975
      7654 MARTIN           1250
      7698 BLAKE            2850
      ...

Wenn man die Formatierung ändern will, dann kann man mit dem Befehl COLUMN FORMAT das Ausgabeformat ändern. Die Befehle können auch abgekürzt werden als COL FOR.

Beispiel: Die Spalte ENAME soll in maximal 6 Zeichen ausgegeben werden und die Spalte SAL soll mit Tausenderpunkten und zwei Nachkommastellen ausgegeben werden:

column ename format a6
column sal format 99,999.99
select empno, ename, sal from emp;

     EMPNO ENAME         SAL
---------- ------ ----------
      7369 SMITH      800.00
      7499 ALLEN    1,600.00
      7521 WARD     1,250.00
      7566 JONES    2,975.00
      7654 MARTIN   1,250.00
      7698 BLAKE    2,850.00
      ...

Wenn man der Wert einer Spalte in dem vorgegebenen Format nicht ausgegeben werden kann (wenn der Platz nicht ausreicht), dann wird automatisch ein Zeilenumbruch ausgeführt.

Beispiel:

column ename format a5
select empno, ename from emp;

     EMPNO ENAME
---------- -----
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTI
           N

      7698 BLAKE
      ...

und bei numerischen Werten wird bei Platzmangel der vorhandene Platz mit dem Hash-Zeichen aufgefüllt.

column sal format 999.99
select empno, sal from emp;

     EMPNO     SAL
---------- -------
      7369  800.00
      7499 #######
      7521 #######
      7566 #######
      7654 #######
      7698 #######
      ...

Eine Formatierung kann wieder aufgehoben werden mit dem Schlüsselwort CLEAR.

Um die Änderungen an den Systemvariablen des SQL*Plus-Client nicht bei jedem Start einer Session erneut setzen zu müssen, können die Änderungen in einem Login-Skript eingetragen werden, das bei jedem Aufbau einer neuen Session automatisch ausgeführt wird. Bei einer Standard-Installation der Oracle-Version 10g unter Windows befindet sich das Login-Skript in dem folgenden Verzeichnis:

C:\oracle\product\10.2.0\db_1\sqlplus\admin\glogin.sql

Bei der Version 11g liegt es hier:

C:\app\<User-ID>\product\11.1.0\db_1\sqlplus\admin\glogin.sql

Sollte kein schreibender Zugriff auf die oben angegebene Datei möglich sein, kann man in dem aktuellen Verzeichnis oder in dem per Umgebungsvariable gesetzten Verzeichnis $ORACLE_PATH eine Datei namens login.sql erstellen, die durch SQL*Plus beim Start nach der glogin.sql eingelesen wird. Eventuell doppelte Angaben überschreiben demnach die Werte, die beim Einlesen der glogin.sql gesetzt worden sind.

Editor

Im SQLPLUS und im SQLPLUSW kann ein Editor aufgerufen werden, um den letzten SQL-Befehl bzw. den letzten PL/SQL-Block zu verändern.

Der Aufruf erfolgt mit dem Befehl EDIT (Abkürzung: ED). Nach dem Editieren des Befehls muss die Seite gespeichert werden und der Editor beendet werden. Dann kann der veränderte Befehl mit / oder mit RUN (Abkürzung: R) ausgeführt werden.

In der Systemvariablen _EDITOR ist der Name des Editors eingetragen, der bei EDIT aufgerufen wird.

Man kann auch einen anderen Editor angeben, z.B. den VI-Editor:

DEFINE _EDITOR = vi

Readline Funktionalität

Will man dieselbe Funktionalität wie in einer Shell haben (alle vorhergehenden Befehle, durchsuchen der Befehle etc.) hilft das Programm rlwrap (readline wrapper). Das Programm liegt den meisten Distributionen als Paket bei, unter Ubuntu installiert man es mit apt-get install rlwrap. Der Aufruf von SQLPLUS erfolgt dann so:

rlwrap sqlplus

Befehl / Skript ausführen

Ein SQL-Statement wird mit einem Semikolon oder einer Leerzeile abgeschlossen. Bei einem Abschluss durch ein Semikolon wird der SQL-Befehl auch gleich ausgeführt.

Ein PL/SQL-Block kann aus mehreren SQL-Statements bestehen. Er wird erst ausgeführt, wenn der Block abgeschlossen wurde. Das geschieht durch eine Zeile, in der nur ein einzelner Punkt oder ein Slash eingetragen ist. Bei einem Slash wird der PL/SQL-Block auch gleich ausgeführt.

Mit dem Befehl RUN (Abkürzung: R) oder einem Slash (eine Eingabezeile, in der nur ein Slash eingegeben wird) wird der letzte SQL-Befehl bzw. der letzte PL/SQL-Block erneut ausgeführt. Der Unterschied ist nur, dass bei RUN der SQL-Befehl bzw. der PL/SQL-Block, der ausgeführt werden soll, noch einmal ausgegeben wird, während bei dem Slash nur ausgeführt wird.

Mit dem Befehl LIST (Abkürzung: L) kann man den letzten SQL-Befehl bzw. den letzten PL/SQL-Block ausgeben ohne ihn auszuführen.

Mit dem Befehl SAVE <Dateiname> (Abkürzung: SAV <Dateiname>) kann man den letzten SQL-Befehl bzw. den letzten PL/SQL-Block in die Datei <Dateiname> ausgeben. Wenn man dabei keine Extension angibt, dann wird automatisch .sql ergänzt.

Mit dem Befehl GET <Dateiname> kann man SQL-Statements aus einer Datei in den Buffer laden. Die gelesenen Statements werden dabei nicht ausgeführt.

Wenn man SQL-Statements aus einer Datei lesen und gleichzeitig ausführen will, dann kann man GET und danach RUN verwenden oder START <Dateiname> (Abkürzung: @<Dateiname>) angeben. Der Unterschied ist nur, dass GET und RUN nur ein einziges SQL-Statement oder einen einzigen PL/SQL-Block in den Buffer übertragen können, während mit START auch beliebig viele SQL-Statements und PL/SQL-Blöcke aus der Datei ausgeführt werden können.

In der Datei, die mit START oder @ ausgeführt wird, können weitere Skripte eingebunden werden, indem diese ebenfalls mit START oder @ benannt werden. Die weiteren Skripte werden bei einem Aufruf mit @ <Dateiname> in dem Verzeichnis gesucht, aus dem SQLPLUS gestartet wurde. Man kann ein weiteres Sktipt auch mit @@ <Dateiname> aufrufen. Der Unterschied besteht darin, dass <Dateiname> in dem Verzeichnis gesucht wird, in dem das aufrufende Skript liegt.

Tabellenstruktur ausgeben

Mit dem DESCRIBE-Befehl (Abkürzung: DESC) kann die Struktur einer Tabelle, Prozedur oder Funktion ausgegeben werden.

Beispiel:

SQL> describe 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)

Sollten nach den Spaltennamen zuviele Leerstellen kommen, ist die Variable LINESIZE zu hoch eingestellt, dann mit set linesize 80 diese kleiner setzen, und den describe Befehl wiederholen.

Die selben Informationen erhält man auch mit einem Zugriff auf die Katalog-Tabelle USER_TAB_COLUMNS (ohne störende 1000 Leerzeichen dazwischen). Wie bei allen Zugriffen auf Katalog-Tabellen muss man darauf achten, dass der Tabellenname in Grossbuchstaben angegeben wird:

select * 
from user_tab_columns 
where table_name = 'EMP'

SQL*Plus-Austausch-Variablen - erstes Beispiel

Wenn man eine Austausch-Variable das erste Mal mit & verwendet, dann wird bei jeder Skriptausführung eine Eingabeaufforderung ausgeführt.

SQL> select empno, ename from emp where empno = &x;
Geben Sie einen Wert für x ein: 7369
alt   1: select empno, ename from emp where empno = &x
neu   1: select empno, ename from emp where empno = 7369

     EMPNO ENAME
---------- ----------
      7369 SMITH

Wenn das Skript ein zweites Mal ausgeführt wird, dann muss erneut ein Wert für die Austauschvariable eingegeben werden, allerdings kann dann ein anderer Wert eingegeben werden.

SQL> /
Geben Sie einen Wert für x ein: 7566
alt   1: select empno, ename from emp where empno = &x
neu   1: select empno, ename from emp where empno = 7566

     EMPNO ENAME
---------- ----------
      7566 JONES

Unterschied zwischen & und &&

Wenn die Austausch-Variable das erste Mal mit && verwendet wurde, dann wird nur bei diesem ersten Auftreten eine Eingabe verlangt. Wenn man die Ausführung mit / wiederholt, dann erfolgt keine Eingabeaufforderung mehr.

SQL> select empno, ename from emp where empno = &&y;
Geben Sie einen Wert für y ein: 7698
alt   1: select empno, ename from emp where empno = &&y
neu   1: select empno, ename from emp where empno = 7698

     EMPNO ENAME
---------- ----------
      7698 BLAKE

SQL> /
alt   1: select empno, ename from emp where empno = &&y
neu   1: select empno, ename from emp where empno = 7698

     EMPNO ENAME
---------- ----------
      7698 BLAKE

Eingabeaufforderung mit PROMPT und ACCEPT

Die Eingabe einer Austausch-Variablen kann explizit durch den Befehl ACCEPT (Abkürzung: ACC) angewiesen werden, ohne dass auf dem Datenbank-Server ein SQL-Befehl ausgeführt werden muss.

SQL> accept x
7369
SQL> select empno, ename from emp where empno = &x;
alt   1: select empno, ename from emp where empno = &x
neu   1: select empno, ename from emp where empno = 7369

     EMPNO ENAME
---------- ----------
      7369 SMITH

Mit ACCEPT PROMPT kann noch ein Kommentar zu der Eingabeaufforderung definiert werden.

SQL> accept x prompt 'Bitte eine Personennummer eingeben '
Bitte eine Personennummer eingeben 7698
SQL> select empno, ename from emp where empno = &x;
alt   1: select empno, ename from emp where empno = &x
neu   1: select empno, ename from emp where empno = 7698

     EMPNO ENAME
---------- ----------
      7698 BLAKE

PROMPT und ACCEPT eignen sich vor allem für die Ausführung von SQL-Skripten, die in einer Datei abgelegt sind.

Beispiel: In der Datei s.sql stehen das folgende Skript:

prompt jetzt geht es los
accept x prompt 'Bitte eine Personennummer eingeben '
prompt Danke für die Eingabe
select empno, ename from emp where empno = &x;

Das Skript wird ausgeführt:

SQL> @s
jetzt geht es los
Bitte eine Personennummer eingeben 7698
Danke für die Eingabe
alt   1: select empno, ename from emp where empno = &x
neu   1: select empno, ename from emp where empno = 7698

     EMPNO ENAME
---------- ----------
      7698 BLAKE

Deklaration mit DEFINE

Austausch-Variable können auch mit DEFINE (Abkürzung: DEF) bekannt gemacht werden. Wenn sie danach verwendet werden, dann keine Eingabeaufforderung mehr ausgeführt.

SQL> define s=2990
SQL> select ename, sal from emp where sal > &s;
alt   1: select ename, sal from emp where sal > &s
neu   1: select ename, sal from emp where sal > 2990

ENAME             SAL
---------- ----------
SCOTT            3000
KING             5000
FORD             3000

Selbst wenn man den SQL-Befehl erneut ausführt, wird keine Eingabeaufforderung mehr ausgeführt. Der einmal definierte Wert wird wieder verwendet.

SQL> /
alt   1: select ename, sal from emp where sal > &s
neu   1: select ename, sal from emp where sal > 2990

ENAME             SAL
---------- ----------
SCOTT            3000
KING             5000
FORD             3000

Eingabeaufforderung für einen String-Wert

Wenn man mit Austausch-Variablen String-Werte verarbeiten will, dann muss man sich grundsätzlich entscheiden, ob in der Variablen der String mit oder ohne Anführungszeichen gespeichert werden soll. Im ersten Fall muss auch der Anwender bei der Eingabeaufforderung den String mit - und um zweiten Fall ohne - Anführungszeichen eingeben.

Beispiel für die Speicherung mit Anführungszeichen:

SQL> select empno, ename from emp where ename = &n;
Geben Sie einen Wert für n ein: 'SMITH'
alt   1: select empno, ename from emp where ename = &n
neu   1: select empno, ename from emp where ename = 'SMITH'

     EMPNO ENAME
---------- ------------------------------------------------
      7369 SMITH

Beispiel für die Speicherung ohne Anführungszeichen:

SQL> select empno, ename from emp where ename = '&n';
Geben Sie einen Wert für n ein: SMITH
alt   1: select empno, ename from emp where ename = '&n'
neu   1: select empno, ename from emp where ename = 'SMITH'

     EMPNO ENAME
---------- ------------------------------------------------
      7369 SMITH

Gibt es ein Escape-Zeichen?

Wie kann man denn in einem SQL*Plus-Skript nach einem String suchen, der mit & beginnt ohne dass die nachfolgenden Buchstaben als Austauschvariable interpretiert werden?

Mit set define kann das & Zeichen geändert werden:

>show define
>define "&" (hex 26)
>select * from dual where '1'='&testvariable';
Enter value for testvariable: 2
old   1: select * from dual where '1'='&testvariable'
new   1: select * from dual where '1'='2' 

no rows selected

>set define #
>select * from dual where '1'='&testvariable';

no rows selected

>select * from dual where '1'='#testvariable';
Enter value for testvariable: 2
old   1: select * from dual where '1'='#testvariable'
new   1: select * from dual where '1'='2'

no rows selected

>show define
>define "#" (hex 23)
>set define &

Mit set define off kann die Suche nach Austauschvariablen ausgeschaltet werden. Dann werden alle Zeichen so belassen, wie sie angegeben sind. Das kann dann sinnvoll sein, wenn man in einem Skript Strings mit allen möglichen Sonderzeichen verwenden möchte, ohne dass eines davon als Austauschvariable interpretiert werden soll.

Punkt als Trennzeichen

Wenn man eine Variable ohne Leerzeichen an ein anderes Literal anschließen möchte, dann muss man den Punkt als Trennzeichen verwenden.

Beispiel für numerische Werte

SQL> undefine x
SQL> select &x.000 from dual;
Geben Sie einen Wert für x ein: 33
alt   1: select &x.000 from dual
neu   1: select 33000 from dual

     33000
----------
     33000

Beispiel für Zeichenketten

SQL> undefine x
SQL> select '&x.abc' from dual;
Geben Sie einen Wert für x ein: 12345
alt   1: select '&x.abc' from dual
neu   1: select '12345abc' from dual

'12345AB
--------
12345abc

Nutzung von SQL*Plus in Skripten

einfaches Beispiel

Man kann in einer Datei SQL-Befehle und PL/SQL-Blöcke speichern, die häufig ausgeführt werden müssen.

Beispiel: In der Datei ana.sql stehen die folgenden Befehle:

analyze TABLE SALGRADE compute statistics;
analyze TABLE BONUS compute statistics;
analyze TABLE EMP compute statistics;
analyze TABLE DEPT compute statistics;

Diese Datei kann ausgeführt werden in einer SQL*Plus-Session:

SQL> @ana
Tabelle wurde analysiert.
Tabelle wurde analysiert.
Tabelle wurde analysiert.
Tabelle wurde analysiert.
SQL>

Wenn das Skript sehr umfangreich ist und man nicht möchte, dass die Meldungen 'Tabelle wurde analysiert.' auf dem Bildschirm ausgegeben werden, dann kann man die Systemvariable FEEDBACK auf OFF setzen. Das Skript ana.sql wird dann wie folgt erweitert:

set feedback off
analyze TABLE SALGRADE compute statistics;
analyze TABLE BONUS compute statistics;
analyze TABLE EMP compute statistics;
analyze TABLE DEPT compute statistics;

Kommentare

Es gibt drei Möglichkeiten, in einem Skript Kommentare zu verfassen:

rem Zeilen, die mit REM beginnen, werden nicht ausgeführt
rem

/* Mit Slash-Stern wird ein Kommentar begonnen, der über mehrere
Zeilen gehen kann.
Mit Stern-Slash wird der Kommentar beendet.
*/

-- Mit zwei Minusstrichen kann man den Rest einer Zeile
-- als Kommentar definieren.
-- Alles, was vor den Kommentarstrichen steht, wird ausgeführt

select * -- Hier sollen alle Spalten gelesen werden
from emp -- Diese Tabelle liegt im Schema scott
;

Parameter übergeben

Bind-Variablen

Das kann man in einem SQL*Plus-Fenster ausführen. (Mit dem Programm TOAD geht das leider nicht)

variable a number 
begin 
  select min(id) into :a from auftrag_tabelle; 
end; 
/ 
print a 
select :a 
from dual 
; 
select * 
from auftrag_tabelle 
where id = :a 
;

Whenever-Befehl

Wenn bei der Ausführung der Befehle eines Skripts Fehler auftreten, dann wird normalerweise der fehlerhafte Befehl übersprungen und mit den nachfolgenden Befehlen fortgesetzt. Wenn man jedoch bei einem Fehler die weitere Verarbeitung abbrechen will, dann kann man an den Anfang des Skripts eine WHENEVER-Anweisung setzen:

whenever sqlerror exit 1;
whenever oserror  exit 1;

Oserror betrifft Fehler aus dem umgebenden Betriebssystem. Sqlerror betrifft Fehler bei der Ausführung der SQL-Statements vom Oracle-Server.

Wenn bei einem Fehler die Verarbeitung fortgesetzt werden soll, dann kann man das auch explizit anweisen:

whenever sqlerror continue

Die Whenever-Anweisungen beziehen sich immer auf alle nachfolgenden SQL-Befehle.

Return-Code abfragen

SQL*Plus kann beim EXIT Returncodes übergeben.

SQL> help exit
 ...
{EXIT|QUIT} [SUCCESS|FAILURE|WARNING|n|variable|:BindVariable]
  [COMMIT|ROLLBACK]

ein SUCCESS liefert als RC 0, FAILURE als RC 1 und WARNING als RC 2. Des Weiteren kann ein numerischer Wert (0..255) übergeben werden, der dann im RC steht:

sqlplus -s test/test
exit

In der shell:
->  echo $?
0

sqlplus -s test/test
exit 99

In der shell:
->  echo $?
99


sqlplus -s test/test
variable RTC number;
begin
select 5 into :RTC from dual;
end;
/
exit :RTC

->  echo $?
5

Cursor definieren

Mit SQL*Plus können Cursor-Variablen definiert werden, die in einem PL/SQL-Block verwendet werden. In dem nachfolgenden Beispiel wird eine Cursor-Variable mit dem Namen C deklariert, geöffnet und - nach Beendigung des PL/SQL-Blocks - ausgegeben. Die Ausgabe wird durch die Autoprint-Option aktiviert.

SQL> variable c refcursor
SQL> set autoprint on
SQL> begin
  2  open :c for 
  3  select ename 
  4  from scott.emp 
  5  where ename like '%E%';
  6  end;
  7  /

PL/SQL-Prozedur erfolgreich abgeschlossen.

ENAME
----------
ALLEN
JONES
BLAKE
TURNER
JAMES
MILLER

6 Zeilen ausgewählt.

Cursor-Variablen kann man auch dafür verwenden, um Prozeduren oder Funktionen aufzurufen, die einen Cursor als Parameter erwarten.

Tipps und Tricks

Skript generieren

Ein Administrator muss oft verschiedene Wartungs-Arbeiten für alle Tabellen ausführen, die bestimmte Kriterien erfüllen. So zum Beispiel das Aktualisieren von Statistikdaten.

analyze table <name> compute statistics;

Dabei kann es sein, dass diese SQL-Befehle für hunderte oder gar tausende Tabellen ausgeführt werden müssen. Natürlich wird ein Administrator solche Befehle nicht für jede Tabelle von Hand eintippen, sondern er könnte sich ein Skript dafür generieren.

Beispiel: Mit dem folgenden Befehl werden Analyze-Befehle für alle Tabellen des Users SCOTT generiert.

select 'analyze table ' 
    || table_name 
    || ' compute statistics;' 
from user_tables;

'ANALYZETABLE'||TABLE_NAME||'COMPUTESTATISTICS;'
-------------------------------------------------
analyze table SALGRADE compute statistics;
analyze table BONUS compute statistics;
analyze table EMP compute statistics;
analyze table DEPT compute statistics;

Nun kann man den Output entweder mit der Maus kopieren und erneut ausführen lassen, oder man läßt den Output gleich in eine Datei mitschreiben. Das kann man mit dem SPOOL-Befehl machen. Das hat den Vorteil, dass man das Skript noch in einem Editor nachbearbeiten kann, bevor man es ausführt. Nach der Ausführung steht das Skript immer noch zur Verfügung und man kann zu einem späteren Zeitpunkt noch einmal nachschauen, welche Befehle ausgeführt wurde.

Wenn man den Output in eine Datei protokolliert, dann wäre es nützlich, die Spaltenüberschriften und die Ergebnis-Meldung zu deaktivieren. Die Befehle dafür sehen dann so aus:

SQL> set newpage 0
SQL> set space 0
SQL> set pagesize 0
SQL> set echo off
SQL> set feedback off
SQL> set verify off
SQL> set heading off
SQL> spool x.sql
SQL> set trimspool on
SQL> select 'analyze table '
  2   || table_name || ' compute statistics;'
  3   from user_tables;
SQL> spool off

In der Datei x.sql wurde das Skript generiert. Hier muss man evtl. noch die Protokollierung des SPOOL OFF entfernen, danach kann man es ausführen:

@x.sql

Serveroutput vergrößern

Um die Ausgaben von ausgeführten Packages/Prozeduren auch zu sehen, muss diese Ausgabe auch eingeschaltet werden, und gross genug sein:

>show serveroutput
serveroutput OFF

>exec dbms_output.put_line('Das ist ein Test');

PL/SQL procedure successfully completed.

So wird eine Prozedur zwar ausgeführt, es wird aber nichts ausgegeben. Um die Ausgabe zu erhalten, muss der Serveroutput eingeschaltet werden:

>set serveroutput on
>exec dbms_output.put_line('Das ist ein Test');
Das ist ein Test

PL/SQL procedure successfully completed.

Oracle schreibt diese Ausgaben erst mal in einen Buffer, und gibt diesen Buffer erst am Ende aus. Dabei kann es passieren, dass der Fehler ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes auftritt, der besagt, dass dieser Buffer übergelaufen ist. Diesen kann man bis zu einer Grösse von 1000000 vergrössern:

>set serveroutput on size 1000001
SP2-0547: size option 1000001 out of range (2000 through 1000000)
>set serveroutput on size 1000000
>show serveroutput
serveroutput ON size 1000000 format WORD_WRAPPED

Datenbankserver starten und stoppen

Der Vorteil von SQL*Plus ist, dass man damit auch auf einen Datenbank-Server zugreifen kann, bei dem die Datenbank-Instanz noch nicht gestartet ist. Das liegt daran, dass das sqlplus Programm auf einem Client-Computer ausgeführt wird. Sqlplus nimmt zunächst Kontakt mit dem Listener auf dem Datenbank-Server auf. Der Listener ist ein Prozess, der in der Regel aktiv bleibt, auch wenn die Datenbank heruntergefahren ist. Der Listener hat die Möglichkeit, ein Hochfahren der Datenbank-Instanz zu bewirken.

Der User SCOTT hat nicht das SYSDBA-Privileg, daher kann er sich an einer inaktiven Instanz nicht anmelden:

C:\Oracle_test>sqlplus scott/tiger

SQL*Plus: Release 11.1.0.6.0 - Production on Sa Feb 2 17:04:52 2008

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Prozess-ID: 0
Session-ID: 0 Seriennummer: 0

Jedoch der User SYS hat das SYSDBA-Privileg, daher kann er sich anmelden und die Instanz hochfahren:

C:\Oracle_test>sqlplus sys/geheim as sysdba
Bei einer nicht hochgefahrenen Instance angemeldet.

SQL> startup
ORACLE-Instance hochgefahren.

Total System Global Area  313860096 bytes
Fixed Size                  1332892 bytes
Variable Size             226494820 bytes
Database Buffers           79691776 bytes
Redo Buffers                6340608 bytes
Datenbank mounted.
Datenbank geöffnet.
SQL>

Das Stoppen und Herunterfahren einer Datenbank-Instanz kann genauso durch SQL*Plus ausgeführt werden - vorausgesetzt, der User hat die SYSDBA-Berechtigung:

SQL> shutdown
Datenbank geschlossen.
Datenbank dismounted.
ORACLE-Instance heruntergefahren.
SQL>

Das Starten und Stoppen einer Datenbank wird ausführlicher beschrieben im Kapitel Oracle: Datenbank starten.

Literatur

Zurück zu " Einleitung " |

Hoch zu " Inhaltsverzeichnis " |

Vor zu " Einführung Oracle SQL Developer "

Oracle: SQL*PLUS

Aufruf-Möglichkeiten

iSQL

iSQL/dba

SQL*Plus als Windows-Tool

SQL*Plus als Command-Line-Tool

SQL*Plus-Session beenden

Interaktives Arbeiten

Allgemeine Formatierung ändern

Formatierung bestimmter Spalten ändern

Login-Skript erstellen

Editor

Readline Funktionalität

Befehl / Skript ausführen

Tabellenstruktur ausgeben

SQL*Plus-Austausch-Variablen - erstes Beispiel

Unterschied zwischen & und &&

Eingabeaufforderung mit PROMPT und ACCEPT

Deklaration mit DEFINE

Eingabeaufforderung für einen String-Wert

Gibt es ein Escape-Zeichen?

Punkt als Trennzeichen

Nutzung von SQL*Plus in Skripten

einfaches Beispiel

Kommentare

Parameter übergeben

Bind-Variablen

Whenever-Befehl

Return-Code abfragen

Cursor definieren

Tipps und Tricks

Skript generieren

Serveroutput vergrößern

Datenbankserver starten und stoppen

Literatur