Oracle SQL Kurzreferenz: Befehle, Funktionen und Praxisbeispiele

Diese Oracle-SQL-Kurzreferenz enthält kompakte Beispiele für häufig benötigte Abfragen, Funktionen und PL/SQL-Konstrukte.

Jeder Abschnitt erklärt den Zweck des jeweiligen Befehls, zeigt ein ausführbares Beispiel und beschreibt das erwartete Ergebnis. Für komplexere Themen führen interne Links zu ausführlichen
Praxisanleitungen.

Die Beispiele sind für Oracle Database vorgesehen. Abweichungen zwischen Datenbankversionen werden direkt beim jeweiligen Abschnitt angegeben.

Aggregationen mit ROLLUP, CUBE und GROUPING

Oracle SQL erweitert die normale GROUP BY-Klausel um mehrere Funktionen für Zwischensummen, Gesamtsummen und mehrdimensionale Auswertungen.

Die wichtigsten Varianten sind:

• ROLLUP für hierarchisch aufgebaute Zwischensummen
• CUBE für alle möglichen Kombinationen mehrerer Gruppierungsmerkmale
• GROUPING zur Erkennung automatisch erzeugter Summenzeilen
• GROUPING SETS zur gezielten Auswahl bestimmter Gruppierungen

Die folgenden Beispiele verwenden eine kleine Umsatzliste mit Ländern, Städten, Produkten und Beträgen.

ROLLUP: Zwischensummen und Gesamtsumme erzeugen

ROLLUP eignet sich für hierarchische Auswertungen. Typische Hierarchien sind beispielsweise:

• Jahr, Monat und Tag
• Land, Bundesland und Stadt
• Abteilung, Team und Mitarbeiter

Oracle beginnt mit der detailliertesten Gruppierung. Anschließend werden die Gruppierungen von rechts nach links schrittweise zusammengefasst.

WITH umsaetze AS (
    SELECT
        'Österreich' AS land,
        'Graz'       AS stadt,
        'Laptop'     AS produkt,
        1000         AS betrag
    FROM dual

    UNION ALL

    SELECT
        'Österreich',
        'Graz',
        'Monitor',
        500
    FROM dual

    UNION ALL

    SELECT
        'Österreich',
        'Wien',
        'Laptop',
        1500
    FROM dual

    UNION ALL

    SELECT
        'Deutschland',
        'München',
        'Laptop',
        1200
    FROM dual
)
SELECT
    land,
    stadt,
    SUM(betrag) AS umsatz
FROM umsaetze
GROUP BY ROLLUP(land, stadt)
ORDER BY
    land NULLS LAST,
    stadt NULLS LAST;

Das Ergebnis enthält drei Ebenen:

Die Zeilen mit einer Stadt zeigen die normalen Gruppierungen.

Eine Zeile mit einem Land und einer leeren Stadt enthält die Zwischensumme des jeweiligen Landes.

Die letzte Zeile, in der sowohl Land als auch Stadt leer sind, enthält die Gesamtsumme aller Datensätze.

Die Reihenfolge der Spalten ist bei ROLLUP entscheidend:

GROUP BY ROLLUP(land, stadt)

erzeugt folgende Gruppierungen:

Land und Stadt
Land
Gesamtsumme

Die umgekehrte Reihenfolge:

GROUP BY ROLLUP(stadt, land)

würde dagegen zuerst nach Stadt und Land, danach nur nach Stadt und schließlich über alle Datensätze aggregieren.

Summenzeilen mit GROUPING kennzeichnen

Die von ROLLUP erzeugten Summenzeilen enthalten an den zusammengefassten Stellen NULL. Ein solcher Wert darf nicht ungeprüft mit einem tatsächlich gespeicherten NULL-Wert verwechselt werden.

Die Funktion GROUPING liefert:

0 = normaler Wert oder tatsächlich gespeichertes NULL
1 = durch ROLLUP oder CUBE erzeugte Summenzeile

Damit lassen sich verständliche Beschriftungen erzeugen:

WITH umsaetze AS (
    SELECT
        'Österreich' AS land,
        'Graz'       AS stadt,
        'Laptop'     AS produkt,
        1000         AS betrag
    FROM dual

    UNION ALL

    SELECT
        'Österreich',
        'Graz',
        'Monitor',
        500
    FROM dual

    UNION ALL

    SELECT
        'Österreich',
        'Wien',
        'Laptop',
        1500
    FROM dual

    UNION ALL

    SELECT
        'Deutschland',
        'München',
        'Laptop',
        1200
    FROM dual
)
SELECT
    CASE
        WHEN GROUPING(land) = 1
        THEN 'Gesamtergebnis'
        ELSE land
    END AS land,

    CASE
        WHEN GROUPING(stadt) = 1
        THEN 'Alle Städte'
        ELSE stadt
    END AS stadt,

    SUM(betrag) AS umsatz
FROM umsaetze
GROUP BY ROLLUP(land, stadt)
ORDER BY
    GROUPING(land),
    land,
    GROUPING(stadt),
    stadt;

Das Ergebnis ist dadurch verständlicher:

Eine einfache Prüfung mit:

land IS NULL

reicht nicht aus, wenn die Ursprungstabelle tatsächlich leere Länderwerte enthalten kann. GROUPING(land) erkennt dagegen eindeutig, ob Oracle die Zeile als Summenzeile erzeugt hat.

CUBE: Alle Kombinationen berechnen

CUBE erzeugt alle möglichen Gruppierungskombinationen der angegebenen Spalten.

Bei zwei Spalten:

CUBE(land, produkt)

entstehen folgende Gruppierungen:

Land und Produkt
Land
Produkt
Gesamtsumme

Beispiel:

WITH umsaetze AS (
    SELECT
        'Österreich' AS land,
        'Graz'       AS stadt,
        'Laptop'     AS produkt,
        1000         AS betrag
    FROM dual

    UNION ALL

    SELECT
        'Österreich',
        'Graz',
        'Monitor',
        500
    FROM dual

    UNION ALL

    SELECT
        'Österreich',
        'Wien',
        'Laptop',
        1500
    FROM dual

    UNION ALL

    SELECT
        'Deutschland',
        'München',
        'Laptop',
        1200
    FROM dual
)
SELECT
    CASE
        WHEN GROUPING(land) = 1
        THEN 'Alle Länder'
        ELSE land
    END AS land,

    CASE
        WHEN GROUPING(produkt) = 1
        THEN 'Alle Produkte'
        ELSE produkt
    END AS produkt,

    SUM(betrag) AS umsatz
FROM umsaetze
GROUP BY CUBE(land, produkt)
ORDER BY
    GROUPING(land),
    land,
    GROUPING(produkt),
    produkt;

Das Ergebnis enthält neben den Einzelwerten auch Summen nach Land, Summen nach Produkt und die Gesamtsumme:

Der wesentliche Unterschied zu ROLLUP besteht darin, dass CUBE nicht nur eine Hierarchie verarbeitet. Es erzeugt auch Auswertungen, bei denen über die jeweils andere Dimension zusammengefasst wird.

Unterschied zwischen ROLLUP und CUBE

Bei:

GROUP BY ROLLUP(land, produkt)

werden diese Gruppierungen erzeugt:

Land und Produkt
Land
Gesamtsumme

Bei:

GROUP BY CUBE(land, produkt)

werden diese Gruppierungen erzeugt:

Land und Produkt
Land
Produkt
Gesamtsumme

ROLLUP ist daher meist für hierarchische Berichte geeignet. CUBE ist sinnvoll, wenn mehrere Dimensionen unabhängig voneinander ausgewertet werden sollen.

Gezielte Gruppierungen mit GROUPING SETS

Nicht immer werden sämtliche Kombinationen eines CUBE benötigt. Mit GROUPING SETS lassen sich nur die tatsächlich benötigten Gruppierungen anfordern.

WITH umsaetze AS (
    SELECT
        'Österreich' AS land,
        'Graz'       AS stadt,
        'Laptop'     AS produkt,
        1000         AS betrag
    FROM dual

    UNION ALL

    SELECT
        'Österreich',
        'Graz',
        'Monitor',
        500
    FROM dual

    UNION ALL

    SELECT
        'Österreich',
        'Wien',
        'Laptop',
        1500
    FROM dual

    UNION ALL

    SELECT
        'Deutschland',
        'München',
        'Laptop',
        1200
    FROM dual
)
SELECT
    land,
    produkt,
    SUM(betrag) AS umsatz
FROM umsaetze
GROUP BY GROUPING SETS (
    (land, produkt),
    (land),
    ()
);

Die leeren Klammern:

()

stehen für die Gesamtsumme.

Diese Abfrage erzeugt:

Land und Produkt
Land
Gesamtsumme

Eine zusätzliche Gruppierung nur nach Produkt wird nicht berechnet.

Wann welche Variante verwendet wird

ROLLUP ist geeignet für:

• Zeiträume wie Jahr, Monat und Tag
• geografische Hierarchien
• Organisationsstrukturen
• Berichte mit Zwischensummen und Gesamtsumme

CUBE ist geeignet für:

• mehrdimensionale Analysen
• Auswertungen nach allen Kombinationen
• Produkt-, Regionen- und Zeitvergleiche
• Data-Warehouse-Berichte

GROUPING SETS ist geeignet für:

• gezielt benötigte Gruppierungen
• Vermeidung unnötiger Kombinationen
• Berichte mit individuell definierten Summenebenen

GROUPING ist geeignet für:

• Kennzeichnung von Zwischen- und Gesamtsummen
• Unterscheidung zwischen echten und künstlich erzeugten NULL-Werten
• verständliche Beschriftungen in Berichten

Wichtiger Performancehinweis

Die Anzahl der von CUBE erzeugten Kombinationen steigt mit jeder zusätzlichen Gruppierungsspalte.

Bei zwei Spalten entstehen vier Kombinationen:

2² = 4

Bei drei Spalten entstehen acht Kombinationen:

2³ = 8

Bei sechs Spalten wären es bereits 64 Kombinationen.

Ein vollständiger CUBE sollte deshalb nur verwendet werden, wenn tatsächlich alle Kombinationen benötigt werden. Für gezielte Berichte ist GROUPING SETS häufig die präzisere Lösung.

Näheres dazu im Beitrag ROLLUP, CUBE und GROUPING SETS in Oracle SQL richtig verwenden.

Autonome Transaktionen mit PRAGMA AUTONOMOUS_TRANSACTION

Eine autonome Transaktion besitzt einen eigenen Transaktionskontext. Änderungen können unabhängig von der aufrufenden Haupttransaktion bestätigt oder zurückgesetzt werden.

Typischer Einsatzbereich ist die dauerhafte Fehler- oder Audit-Protokollierung, wenn die Haupttransaktion später mit ROLLBACK beendet wird.

CREATE OR REPLACE PROCEDURE schreibe_log (
    p_meldung IN VARCHAR2
) AS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO verarbeitungs_log (
        meldung
    )
    VALUES (
        p_meldung
    );

    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END schreibe_log;
/

Wichtige Eigenschaften:

Das COMMIT betrifft nur die autonome Transaktion.
Ein späteres ROLLBACK der Haupttransaktion löscht den Logeintrag nicht.
Die autonome Transaktion sieht unbestätigte Änderungen der Haupttransaktion normalerweise nicht.
Sie muss immer mit COMMIT oder ROLLBACK abgeschlossen werden.
Sie sollte bevorzugt in einer separaten Logtabelle arbeiten, um Sperren und Deadlocks zu vermeiden.
Für fachlich zusammengehörige Geschäftsdaten ist sie normalerweise ungeeignet.

Mehr Infos im Artikel Autonome Transaktionen in Oracle PL/SQL mit PRAGMA AUTONOMOUS_TRANSACTION

Tabellen mit CREATE TABLE und CTAS erstellen

Mit CREATE TABLE wird eine neue Tabelle mit festgelegten Spalten, Datentypen und Integritätsregeln erstellt.

Soll die neue Tabelle gleichzeitig mit dem Ergebnis einer Abfrage befüllt werden, kann CREATE TABLE AS SELECT verwendet werden. Diese Variante wird meist mit CTAS abgekürzt.

Leere Tabelle mit CREATE TABLE anlegen

Bei einer normalen CREATE TABLE-Anweisung werden die Spalten, Datentypen und Einschränkungen ausdrücklich definiert.

CREATE TABLE kunden (
    kunden_id     NUMBER
                  GENERATED BY DEFAULT AS IDENTITY,

    name          VARCHAR2(100) NOT NULL,

    email         VARCHAR2(255),

    erstellt_am   TIMESTAMP
                  DEFAULT SYSTIMESTAMP
                  NOT NULL,

    aktiv         CHAR(1)
                  DEFAULT 'J'
                  NOT NULL,

    CONSTRAINT pk_kunden
        PRIMARY KEY (kunden_id),

    CONSTRAINT uq_kunden_email
        UNIQUE (email),

    CONSTRAINT ck_kunden_aktiv
        CHECK (aktiv IN ('J', 'N'))
);

Die Tabelle enthält nach dem Erstellen noch keine Datensätze.

Die wichtigsten Bestandteile:

kunden_id
Automatisch erzeugte numerische ID.

name
Pflichtfeld mit maximal 100 Zeichen.

email
Optionale E-Mail-Adresse.

erstellt_am
Zeitpunkt der Anlage. Ohne ausdrücklichen Wert wird SYSTIMESTAMP verwendet.

aktiv
Kennzeichnung mit J oder N. Der Standardwert ist J.

PRIMARY KEY
Identifiziert jeden Datensatz eindeutig.

UNIQUE
Verhindert doppelte E-Mail-Adressen.

CHECK
Erlaubt für aktiv nur die definierten Werte J und N.

Ein Datensatz kann anschließend eingefügt werden:

INSERT INTO kunden (
    name,
    email
)
VALUES (
    'Anna Beispiel',
    '[email protected]'
);

Für kunden_id, erstellt_am und aktiv verwendet Oracle die in der Tabellendefinition festgelegten automatischen beziehungsweise standardmäßigen Werte.

Tabelle und Daten mit CTAS erstellen

Mit CREATE TABLE AS SELECT wird eine Tabelle aus dem Ergebnis einer Abfrage erstellt.

CREATE TABLE aktive_kunden AS
SELECT
    kunden_id,
    name,
    email,
    erstellt_am
FROM kunden
WHERE aktiv = 'J';

Oracle führt dabei zwei Schritte innerhalb einer Anweisung aus:

1. Die Struktur der neuen Tabelle wird aus der SELECT-Liste abgeleitet.
2. Die von der Abfrage zurückgegebenen Datensätze werden eingefügt.

Die neue Tabelle aktive_kunden enthält damit nur aktive Kunden.

SELECT *
FROM aktive_kunden;

CTAS erzeugt eine einmalige Kopie. Spätere Änderungen an der Tabelle kunden werden nicht automatisch in aktive_kunden übernommen.

Spalten beim Erstellen umbenennen

Die Spaltennamen der neuen Tabelle können über Aliase festgelegt werden:

CREATE TABLE kunden_export AS
SELECT
    kunden_id AS id,
    name      AS kundenname,
    email     AS kontaktadresse
FROM kunden;

Die neue Tabelle besitzt danach die Spalten:

ID
KUNDENNAME
KONTAKTADRESSE

Ausdrücke sollten immer einen eindeutigen Alias erhalten.

CREATE TABLE kunden_statistik AS
SELECT
    aktiv,
    COUNT(*) AS anzahl_kunden
FROM kunden
GROUP BY aktiv;

Ohne geeigneten Alias können automatisch abgeleitete Spaltennamen unverständlich oder für die Tabellenerstellung ungeeignet sein.

Nur die Tabellenstruktur kopieren

Soll die neue Tabelle zunächst leer bleiben, kann eine Bedingung verwendet werden, die niemals erfüllt ist:

CREATE TABLE kunden_archiv AS
SELECT
    kunden_id,
    name,
    email,
    erstellt_am,
    aktiv
FROM kunden
WHERE 1 = 0;

Die Tabelle wird erstellt, aber es werden keine Datensätze übernommen.

Kontrolle:

SELECT COUNT(*) AS anzahl
FROM kunden_archiv;

Ergebnis:

ANZAHL
------
0

Diese Methode eignet sich beispielsweise für:

• Archivtabellen
• temporäre Arbeitskopien
• Importtabellen
• Testtabellen
• Staging-Bereiche

Dabei wird jedoch nicht die vollständige ursprüngliche Tabellendefinition kopiert.

Datentypen bei CTAS gezielt festlegen

Bei CTAS leitet Oracle Datentypen und Spaltenlängen aus den Ausdrücken der SELECT-Liste ab.

Mit CAST können die gewünschten Datentypen ausdrücklich festgelegt werden:

CREATE TABLE kunden_schnittstelle AS
SELECT
    CAST(kunden_id AS NUMBER(10))
        AS kunden_id,

    CAST(name AS VARCHAR2(100))
        AS kundenname,

    CAST(email AS VARCHAR2(255))
        AS email,

    CAST(erstellt_am AS TIMESTAMP)
        AS erstellt_am
FROM kunden
WHERE aktiv = 'J';

Das ist besonders sinnvoll, wenn:

• berechnete Ausdrücke verwendet werden,
• Zeichenketten zusammengesetzt werden,
• Daten aus mehreren Tabellen stammen,
• eine Schnittstelle feste Datentypen erwartet,
• die Zielstruktur dauerhaft verwendet werden soll.

Beispiel mit einer berechneten Zeichenkette:

CREATE TABLE kunden_anzeige AS
SELECT
    kunden_id,

    CAST(
        kunden_id || ' - ' || name
        AS VARCHAR2(150)
    ) AS anzeigetext
FROM kunden;

Ohne CAST sollte geprüft werden, welchen Datentyp und welche Länge Oracle für den Ausdruck erzeugt hat.

Welche Eigenschaften CTAS nicht vollständig übernimmt

CTAS kopiert das Abfrageergebnis, aber nicht automatisch die vollständige Definition der Ursprungstabelle.

Insbesondere werden normalerweise nicht übernommen:

• Primärschlüssel
• Unique Constraints
• Fremdschlüssel
• Check Constraints
• Indizes
• Spalten-Standardwerte
• Partitionierungsdefinitionen
• die Identity-Eigenschaft einer Spalte

Bei ausdrücklich definierten NOT NULL-Constraints kann Oracle die Einschränkung unter bestimmten Voraussetzungen übernehmen. Darauf sollte bei einer dauerhaft verwendeten Zieltabelle jedoch nicht ungeprüft vertraut werden.

Nach einer CTAS-Anweisung muss die erzeugte Struktur deshalb kontrolliert werden.

Constraints nach CTAS ergänzen

Nach dem Erstellen können benötigte Einschränkungen mit ALTER TABLE ergänzt werden.

Primärschlüssel:

ALTER TABLE aktive_kunden
ADD CONSTRAINT pk_aktive_kunden
PRIMARY KEY (kunden_id);

Pflichtfeld:

ALTER TABLE aktive_kunden
MODIFY name NOT NULL;

Unique Constraint:

ALTER TABLE aktive_kunden
ADD CONSTRAINT uq_aktive_kunden_email
UNIQUE (email);

Check Constraint:

ALTER TABLE kunden_archiv
ADD CONSTRAINT ck_kunden_archiv_aktiv
CHECK (aktiv IN ('J', 'N'));

Ein Fremdschlüssel wird ebenfalls nachträglich angelegt:

ALTER TABLE auftrag_archiv
ADD CONSTRAINT fk_auftrag_archiv_kunde
FOREIGN KEY (kunden_id)
REFERENCES kunden (kunden_id);

Vor dem Aktivieren einer Einschränkung müssen die bereits vorhandenen Daten die jeweilige Regel erfüllen.

Index nach CTAS erstellen

Normale Indizes der Ursprungstabelle werden durch CTAS nicht automatisch übernommen.

Ein Index muss deshalb separat erstellt werden:

CREATE INDEX idx_aktive_kunden_email
ON aktive_kunden (email);

Ob ein Index erforderlich ist, hängt von den späteren Abfragen ab. Indizes sollten nicht allein deshalb kopiert werden, weil sie auf der Ursprungstabelle vorhanden sind.

Erzeugte Spalten kontrollieren

Die Spaltendefinitionen können über USER_TAB_COLUMNS kontrolliert werden:

SELECT
    column_id,
    column_name,
    data_type,
    data_length,
    data_precision,
    data_scale,
    nullable,
    data_default
FROM user_tab_columns
WHERE table_name = 'AKTIVE_KUNDEN'
ORDER BY column_id;

Oracle speichert nicht in Anführungszeichen gesetzte Objektnamen standardmäßig in Großbuchstaben. Deshalb wird in der Abfrage verwendet:

'AKTIVE_KUNDEN'

Constraints kontrollieren

Die vorhandenen Constraints zeigt USER_CONSTRAINTS:

SELECT
    constraint_name,
    constraint_type,
    status
FROM user_constraints
WHERE table_name = 'AKTIVE_KUNDEN'
ORDER BY constraint_name;

Wichtige Werte für CONSTRAINT_TYPE:

P = Primary Key
U = Unique Constraint
R = Foreign Key
C = Check Constraint oder NOT NULL

Die zugehörigen Spalten können über USER_CONS_COLUMNS ermittelt werden:

SELECT
    constraint_name,
    column_name,
    position
FROM user_cons_columns
WHERE table_name = 'AKTIVE_KUNDEN'
ORDER BY
    constraint_name,
    position;

Indizes kontrollieren

SELECT
    index_name,
    index_type,
    uniqueness,
    status
FROM user_indexes
WHERE table_name = 'AKTIVE_KUNDEN'
ORDER BY index_name;

Vorsicht: CREATE TABLE führt ein implizites COMMIT aus

CREATE TABLE und CREATE TABLE AS SELECT sind DDL-Anweisungen.

Oracle führt vor einer syntaktisch gültigen DDL-Anweisung ein implizites COMMIT aus. Nach einer erfolgreich ausgeführten DDL-Anweisung erfolgt ebenfalls ein implizites COMMIT.

Beispiel:

UPDATE kunden
SET aktiv = 'N'
WHERE kunden_id = 10;

CREATE TABLE kunden_test (
    kunden_id NUMBER
);

Durch das nachfolgende CREATE TABLE kann die zuvor ausgeführte Änderung dauerhaft bestätigt werden, obwohl kein ausdrückliches COMMIT zwischen den beiden Anweisungen steht.

Ein anschließendes:

ROLLBACK;

macht weder die Tabellenerstellung noch die bereits bestätigte Änderung rückgängig.

DDL-Anweisungen sollten deshalb nicht unüberlegt innerhalb einer laufenden fachlichen Transaktion ausgeführt werden.

CREATE TABLE oder CTAS verwenden

Eine normale CREATE TABLE-Anweisung ist geeignet, wenn:

• die Zielstruktur genau definiert werden soll,
• Primär- und Fremdschlüssel benötigt werden,
• Standardwerte festgelegt werden,
• eine dauerhaft verwendete Geschäftstabelle entsteht,
• Datentypen und Spaltenlängen ausdrücklich kontrolliert werden müssen.

CTAS ist geeignet, wenn:

• eine Tabelle aus einem Abfrageergebnis erzeugt wird,
• Daten für Tests oder Analysen kopiert werden,
• eine Archiv- oder Arbeitstabelle benötigt wird,
• aggregierte Ergebnisse gespeichert werden,
• eine Ausgangsstruktur schnell aus vorhandenen Daten erstellt werden soll.

Kurze Zusammenfassung

Leere, vollständig definierte Tabelle:
CREATE TABLE tabellenname (...)

Tabelle aus einer Abfrage:
CREATE TABLE tabellenname AS
SELECT ...

Nur Struktur, keine Datensätze:
CREATE TABLE tabellenname AS
SELECT ...
WHERE 1 = 0

Datentyp gezielt bestimmen:
CAST(ausdruck AS datentyp)

Wichtig bei CTAS:
Constraints, Indizes, Standardwerte und Identity-Eigenschaften prüfen

Transaktionsverhalten:
CREATE TABLE ist DDL und führt ein implizites COMMIT aus

Datumswerte in Oracle SQL

Monatsreihen zwischen zwei Datumswerten erzeugen

Mit ADD_MONTHS, MONTHS_BETWEEN, TRUNC und CONNECT BY LEVEL lässt sich in Oracle SQL eine lückenlose Monatsreihe zwischen zwei Datumswerten erzeugen.

WITH parameter AS (
    SELECT
        DATE '2026-03-15' AS von_datum,
        DATE '2026-08-20' AS bis_datum
    FROM dual
)
SELECT
    ADD_MONTHS(
        TRUNC(von_datum, 'MM'),
        LEVEL - 1
    ) AS monatsbeginn
FROM parameter
WHERE bis_datum >= von_datum
CONNECT BY LEVEL <=
    MONTHS_BETWEEN(
        TRUNC(bis_datum, 'MM'),
        TRUNC(von_datum, 'MM')
    ) + 1;

Ergebnis:

01.03.2026
01.04.2026
01.05.2026
01.06.2026
01.07.2026
01.08.2026

TRUNC(datum, 'MM') setzt beide Datumswerte auf den jeweiligen Monatsanfang. MONTHS_BETWEEN berechnet die Monatsdifferenz. Das zusätzliche + 1 sorgt dafür, dass auch der Endmonat ausgegeben wird.

Diese Methode benötigt keine Systemansicht wie DBA_OBJECTS und funktioniert deshalb ohne zusätzliche Leseberechtigungen.

Für Auswertungen mit Uhrzeiten sollte eine exklusive Obergrenze verwendet werden:

buchungsdatum >= monatsbeginn
AND buchungsdatum < ADD_MONTHS(monatsbeginn, 1)

Dadurch werden sämtliche Datensätze eines Monats berücksichtigt, ohne das Monatsende künstlich auf eine bestimmte Uhrzeit setzen zu müssen.

Weiterführender Artikel: Monatsreihen zwischen zwei Datumswerten erzeugen

JSON

Sometimes there is the need to query a json value, cause one value has more than just one information. Also for dynamic purposes json values are often needed. Here is a little tutorial, for more information see https://docs.oracle.com/en/database/oracle/oracle-database/18/adjsn/

To start, we just generate a json value with name, age and an address. Mention, that the address is again a json value with the fields city and country.

SELECT '{"UserName":"David","Age":26,"Address":{"City":"Graz", "Country":"Austria"} }' po_document
FROM DUAL;

To get a json value, you can use the json_value parameter. The first argument is the column of your table, the second argument is the path of the field value. In the first example you will get the age, in the second example the country.

SELECT json_value(po_document, '$.Age') 
FROM (SELECT '{"UserName":"David","Age":26,"Address":{"City":"Graz", "Country":"Austria"} }' po_document 
      FROM DUAL);
=> Output: 26

SELECT json_value(po_document, '$.Address.Country') 
FROM (SELECT '{"UserName":"David","Age":26,"Address":{"City":"Graz", "Country":"Austria"} }' po_document 
      FROM DUAL);
=> Output: Austria

Last Day

Often you need to now the last day. If you have the last day. You can get the last day with the help of trunc. So you add a month and truncate the date. This means you have the first date of the next month which will be the first of month x. Now you can subtract one day and have the last day. But more, there is a function called LAST_DAY which will help you.

SELECT 
   DISTINCT(last_day(TO_DATE(da.end_date + 1 - rownum))) Last_Day
FROM 
   all_objects,
   (SELECT TO_DATE('01.01.2018', 'dd.mm.yyyy') start_date
          ,TO_DATE('31.12.2018', 'dd.mm.yyyy') end_date
    FROM DUAL) da
WHERE 
   TRUNC(da.end_date + 1 - rownum,'MM') >= TRUNC(da.start_date,'MM')
ORDER BY 1

Default if no row is returned (SQL)

Sometimes default values are necassary. Normally you can use NVL or NVL2 when you get NULL values as return value. But it is also possible, that no row is returned. In this case, NVL or NVL2 is not working, cause you get no value back – not even NULL. Therefore, there are some options – like producing a NULL value.

-- Use the COALESCE statement, MAX produces automatic a NULL value
SELECT COALESCE(MAX(dummy), 'Z') result
FROM DUAL
WHERE DUMMY = 'Y'; -- 'X'

-- Use the NVL statement, MAX produces automatic a NULL value
SELECT NVL(MAX(dummy), 'Z') result
FROM DUAL
WHERE DUMMY = 'Y'; -- 'X'

-- Use the EXISTS statement in combination with CASE
SELECT CASE
    WHEN EXISTS(SELECT 1
                FROM DUAL
                WHERE DUMMY = 'Y') -- 'X'
    THEN (SELECT DUMMY
          FROM DUAL
          WHERE DUMMY = 'Y') -- 'X'
    ELSE 'Z' result
    END
FROM DUAL;

-- Use the COUNT function in combination with CASE
-- MIN is needed to identify the correct value
SELECT CASE
    WHEN COUNT(1) > 0
        THEN MIN(DUMMY)
        ELSE 'Z'
    END result
FROM DUAL
WHERE DUMMY = 'Y'; -- 'X'

Check out the blog article about the SQL default value if no row is returned.

Keywords: SQL Default if no rows returned, oracle sql select default value if null

Dupletten / Doppelte Werte

SELECT #column(s), COUNT(*) 
FROM #tableName
GROUP BY #column(s)
HAVING COUNT(*) > 1; 

Exceptions

SQL Customer Exceptions

Customer Exceptions can have an error number betweenn -20000 and -20999 (1.000 possibilites). After the error number you can define an error text.

THEN RAISE_APPLICATION_ERROR(-20001,'My exception was raised');

SQL Exceptions in Loop

You can also use exceptions in loops. Use the following code when you want that the loop continues (just possible when an own PL/SQL Block with begin and end statement is inside the loop:

FOR ... LOOP
   BEGIN
       EXCEPTION
   END;
END LOOP;

Flashback Query

The query of a specific value from the past (often just minutes) can be reproduced with the keywords “AS OF TIMESTAMP” and a specifc date or time interval. However, the AS OF clause always refers to a table, so for a JOIN, an AS OF clause must be specified for each table.

SELECT * 
FROM emp AS OF TIMESTAMP systimestamp - INTERVAL '5' MINUTE
WHERE  empno = 1234;

Formula (Math Functions)

If you want to calculate a value with a function which is stored in the table (row or column) you can use the follwing code to execute the math formula. Replace placeholders with the values you need.

DECLARE
    v_return NUMBER;
    pi_val VARCHAR2(50) := '2*6*10';
BEGIN
    EXECUTE IMMEDIATE 'select '||pi_val||' from dual' INTO v_return;
    dbms_output.put_line(v_return);
END;

Example Code for a function:

set serveroutput on
DECLARE
    v_return NUMBER;
    pi_val VARCHAR2(50) := '2*6*10';
    pi_val2 VARCHAR2(50) := 'ROUND(x/12,2)';
    pi_x NUMBER := 12.558;
BEGIN
    -- EXECUTE IMMEDIATE 'select '||pi_val||' from dual' INTO v_return;
    -- dbms_output.put_line(v_return);
    pi_val2 := REPLACE(pi_val2, 'x', TO_CHAR(pi_x, '9G990D00', 'NLS_NUMERIC_CHARACTERS = ''.,'''));
    dbms_output.put_line(pi_val2);
    EXECUTE IMMEDIATE 'select '||pi_val2||' from dual' INTO v_return;
    dbms_output.put_line(v_return);
END;

CREATE OR REPLACE FUNCTION Calc(pi_x NUMBER, pi_formula VARCHAR2) 
RETURN NUMBER IS
    v_return NUMBER;
    BEGIN
        pi_formula := REPLACE(pi_formula, 'x', TO_CHAR(pi_x, '9G990D00', 'NLS_NUMERIC_CHARACTERS = ''.,'''));
        EXECUTE IMMEDIATE 'select '||pi_formula||' from dual' INTO v_return;
        RETURN v_return;
    END;
    
SELECT Calc(12.558, 'ROUND(x/12,2)')
FROM DUAL;

Check out the blog article about the SQL mathematic function.

Grant

To grant privilages to a specific user.

GRANT privilege_name 
ON object_name 
TO {user_name |PUBLIC |role_name} 
[WITH GRANT OPTION];  

f.e.
GRANT ALL
ON table_name
TO user_name;

IN List

The normal IN List can handle a maximum of one thausand values. But there is an option (the multi value IN Liste) where you can add as much values as you want.

SELECT * 
FROM emp 
WHERE 1=1 
   AND empNo IN (1, 2, ... <maximum 1000 values>);

If you want to use the multi value IN List it is important, that you use multiple values. Use f.e. 1 as first value and compare it with the other 1 as first value in your multi value In List. 1=1 is always true, so just the second parameter is used for comparing.

SELECT * 
FROM emp 
WHERE 1=1  
   AND (1,empNo) IN ((1, 1), (1,2));

Level

Organisations Chart

SELECT LPAD(last_name, LENGTH(last_name) + (LEVEL*2) - 2, '_') AS chart
FROM employees
START WITH emp_id = 1234 --CEO
CONNECT BY PRIOR emp_id = manager_id

LOOP

Label

Loops can be labeled with the statement <<label>>, where label is the name. Labeling should happen in nested loops. Labeling will be necassary when using the GO TO or EXIT statement.

<<OUTER_LOOP>>
LOOP
   <<INNER_LOOP>>
   LOOP
      -- EXIT OUTER_LOOP WHEN 1=1
   END LOOP;
END LOOP;

CONTINUE

The CONTINUE statement jumps out of the current loop and start the next iteration. Is is possible to use CONTINUE as well as CONTINUE WHEN. It is also possible to jump to an outer loop (labeled). The CONTINUE statement will skip the code following and will start the next iteration.

BEGIN 
   <<outerLoop>> 
   FOR outer_index IN 1 .. 3 
   LOOP 
      CONTINUE WHEN MOD (outer_index, 2) = 0; 
 
      DBMS_OUTPUT.put_line ('OUTER: ' || outer_index); 
 
      FOR inner_index IN 1 .. 3 
      LOOP 
         CONTINUE outerLoop WHEN inner_index = 2; 
 
         DBMS_OUTPUT.put_line ('INNER ' || inner_index); 
      END LOOP; 
 
      DBMS_OUTPUT.put_line ('Rest of outer loop'); 
   END LOOP outer; 
END; 

OUTER 1
INNER 1
OUTER 3
INNER 1

GO TO

It is possible to jump to a labeled statement. Transfer can only be done within the subprogram. Is is not possible to jump from the exception part to the execution part.

EXIT WHEN

The EXIT statement exits the current loop. If the loop is nested and the inner loop has an EXIT statement, just the inner loop is exited – but not the outer loop. EXIT WHEN <boolean> = true will exit the current loop.

NULL Values

NVL

The normal NVL statement checks if a value is NULL. If so, the second statement value is outputet, otherwise the value which is checked (in case the value is not NULL). The datatypes have to be the same. NVL handels the following code.

IF 
   expr1 IS NOT NULL     
THEN expr1 
END IF; 

IF 
   exp1 IS NULL     
THEN expr2 
END IF;

NVL2

When using NVL2 you have one more possibility: You can also define a value, when the test value IS NOT NULL. So you have two parameters (NULL and NOT NULL) plus your test value, which you want to test. The NVL2 statement does nothing else then the following code:

IF 
   expr1 IS NOT NULL     
THEN expr2
END IF; 

IF 
   expr1 IS NULL     
THEN expr3 
END IF;

Use the follwing code example as comparision between NVL and NVL2:

NVL(expr1, expr2) = NVL2(expr1, expr1, expr2)

LISTAGG

LISTAGG

LISTAGG(#col1#, ',') WITHIN GROUP(ORDER BY  #col1# )

SQL Distinct LISTAGG

The distinct SQL Listagg is used, when the normal Listagg produces too much values. With the Distinct Listagg each value occurs just once.

RTRIM(REGEXP_REPLACE(LISTAGG(#col1#, ',') WITHIN GROUP(ORDER BY  #col1# ) OVER (PARTITION BY  
#col2# ), '([^,]+)(,\1)+', '\1'), ',') as "Name"

ORDER BY

Conditional ORDER BY

Important when using conditional ORDER BY is, that on the same level exists the same data type. There is one big problems: Converting Numbers to Char and order them. But there is an easy solution.

SELECT myCharValue, myText, myNumber
FROM testTable
WHERE 1=1
ORDER BY myCharValue, 
   CASE
      WHEN myCharValue IS NOT NULL
      THEN myTest
      ELSE lpad(TO_CHAR(myNumber),50) -- 50 is the maximal length of the number
   END;

If you want to sort the numbers from the highest number to the lowest, you can use the following work around:

SELECT myCharValue, myText, myNumber
FROM testTable
WHERE 1=1
ORDER BY myCharValue, 
   CASE
      WHEN myCharValue IS NOT NULL
      THEN myTest
      ELSE lpad(TO_CHAR(10000-myNumber),50) -- 50 is the maximal length of the number, 10000 is the highest possible number
   END;

OUTPUT

Activate Display Output

To activate the display output for Oracle, PL/SQL and Oracle SQL Developer just use the following code snippet.

SET SERVEROUTPUT ON;

Display Output

To output number, char oder date values you can use the dmbs_output.put_line statement.

DBMS_OUTPUT.PUT_LINE(v_result);

It is also possible to not start a new line. Then you need to serve the following code snippet.

DBMS_OUTPUT.PUT(v_result);

If you just want to test some output code (f.e. not inside a PL/SQL block) you have to use the execute statement.

EXECUTE DBMS_OUTPUT.PUT_LINE('Not in a PL/SQL block');

Example Code to see the functionality:

BEGIN
    dbms_output.put_line('Hello');
    dbms_output.put_line('how');
    dbms_output.put('are');
    dbms_output.put('you');
    dbms_output.put_line('?');
END;

-- Output
Hello
how
areyou?

Puffer

The default size of the output puffer is 20.000 bytes, but you can increase the size to a maximum of 1.000.000 bytes. You can do this with the following statement:

DBMS_OUTPUT.ENABLE(1000000);

OVER Clause

Preceding / Following

INTERVAL '1' MONTH FOLLOWING

PL/SQL Block

The simplest form of the PL/SQL block is BEGIN and END. You can also declare variables in the DECLARE section.

DECLARE
   v_return NUMBER;
   v_string VARCHAR2(50 char) := 'xyz'
BEGIN
   -- code goes here
END;

Primary Key

SYS_GUID()

If you don’t want to generate an Sequence or Id by your own, you can use SYS_GUID(). It generates a globally unique identfier (RAW, 16 Bytes). Its generated using host identfier , process or thread identfier and a non repeating value.

INSERT INTO my_primary_table(primary_key_id) VALUES(SYS_GUID());

Rekursion

REGEXP

SQL REGEXP_LIKE

If you need to use the LIKE clause but you have a pattern – so you don’t know exactly the value. If you don’t know the exact value, you can’t use the LIKE clause. Instead you can use the REGEXP_LIKE clause. The first argument is the expression and the second parameter the pattern.

The whole guide is available at Oracle.

 WHERE regexp_like(#col#,'^Sta|^Anf|^Abc');  

The third parameter is the match parameter. Use ‚i‘ for case-insensitive matching and ‚c‘ for case-sensitive matching.

Trigger

Compound  Trigger

A compound trigger has four timing points:

• before the trigger is firing
• before each row that the firing statement affects
• after each row that the firing statement affects
• after the trigger has fired

When using compund triggers, statement level as well as row level actions can be but in a single trigger. The
compound trigger has to be an DML trigger.

CREATE OR REPLACE TRIGGER co_trigger
FOR [INSERT|DELETE]UPDATE [OF column] ON table
COMPOUND TRIGGER

BEFORE STATEMENT IS
BEGIN
   NULL;
END BEFORE STATEMENT;

BEFORE EACH ROW IS
AFTER EACH ROW IS
AFTER STATEMENT IS

END co_trigger;

Another possibility is to declare the trigger autonomous. You can do this by adding the following statement:

DECLARE 
    pragma autonomous_transaction;

Total

If you want to calculate the total of the preceding rows you can use the PRECEDING statement. This will select all the rows between the defined rows (current row, rows from beginning, …). The following example will output the total with the next id.

SELECT id, accid, val_date, amount, 
    SUM(amount) OVER (PARTITION BY accid ORDER BY val_date, id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) total
FROM
(
SELECT 1 id, 8000 accid, sysdate val_date, 1 amount FROM DUAL
UNION ALL
SELECT 2 id, 8000 accid, sysdate val_date, 2 amount FROM DUAL
UNION ALL
SELECT 3 id, 8000 accid, sysdate - 1 val_date, 3 amount FROM DUAL
UNION ALL
SELECT 4 id, 5000 accid, sysdate val_date, 3 amount FROM DUAL
);

If you want to ouput the balance in the same row, you can use the follwoing code (delete the 1 PRECEDING argument). In this example you have the commulative total (but seperated per account id).

SELECT id, accid, val_date, amount, 
    SUM(amount) OVER (PARTITION BY accid ORDER BY val_date, id ROWS UNBOUNDED PRECEDING ) total
FROM
(
SELECT 1 id, 8000 accid, sysdate val_date, 1 amount FROM DUAL
UNION ALL
SELECT 2 id, 8000 accid, sysdate val_date, 2 amount FROM DUAL
UNION ALL
SELECT 3 id, 8000 accid, sysdate - 1 val_date, 3 amount FROM DUAL
UNION ALL
SELECT 4 id, 5000 accid, sysdate val_date, 3 amount FROM DUAL
);

View

Materialized View

A normal view is just a virtual construct. When you update or insert a value into a view, the database automatically insert the value into the table in the background. If you just display the view, the query is executed (computing time and power is needed). Another option is to create a materialized view. This is a view which is in fact a copy of the table (or the SQL Query). That means, that storage is needed (you trad computing time with storage). The view updates periodically, meaing that some data can be out of date and some data is not yet available.

CREATE MATERIALIZED VIEW my_mat_view
   REFRESH FAST 
   START WITH SYSDATE
   NEXT SYSDATE + 1 AS 
      SELECT * 
      FROM <table_name>;