Oracle APEX 18.2 Tutorial Teil 2 – SQL Commands

Im zweiten Teil des Einführungstutorials geht es um die Ausführung von DDL Anweisungen über das Modul SQL Commander. 

In diesem Teil des Tutorials soll der Rest des Datenmodells angelegt werden. Hierzu kommt zu Anschauungszwecken das Werkzeug SQL Commands zum Einsatz.

SQL Commands

Im ersten Teil der Beitragsreihe ging es darum eine Tabelle mit dem Objects Browser zu erzeugen. APEX bietet unter dem SQL-Workshop neben diesem Tool mit dem SQL Commands ein weiteres Werkzeug mit dem Sie direkt mit der Datenbank interagieren können.

Abbildung 9: SQL Commands
Abbildung 9: SQL Workshop – SQL Commands

Über das Tool SQL Commands ist es möglich einfach Freihand SQL Befehle und SQL-Scripte auszuführen.

Abbildung 10: SQL Commands
Abbildung 10: SQL Commands

Im oberen Textfeld können Sie SQL bzw. PL/SQL Befehle eingeben. Über den Button Run führen Sie diese aus.

Mit Find Tables können Sie sich die Tabellen Ihres Workspaces anzeigen lassen. Klicken Sie in der Übersicht von “Find Tables” auf den Tabellennamen wird Ihnen die Struktur der Tabelle präsentiert.

Abbildung 11: Find Tables
Abbildung 11: Find Tables

Die Skripte, die Sie mit SQL Commands erstellt haben, lassen sich in APEX über die Schaltfläche Save speichern. Laden können Sie diese dann über den Tab Saved SQL.

Tabellen und Trigger anlegen

Das folgende SQL-Skript (Listing 1) erzeugt für die noch fehlenden Tabellen ingredients, processing_steps und product_classification. Zu jeder Tabelle wird je ein Trigger angelegt, über den ein künstlicher Schlüssel als Primärschlüssel erzeugt wird.

Das Skript habe ich mit einem weiteren APEX-Tool namens Quick SQL erzeugt. Anschließend habe ich noch einige kleine manuellen Anpassungen vorgenommen. Wenn Sie sich näher mit der schnellen Erzeugung von Datenmodellen beschäftigen möchten, werden Sie doch einfach mal einen (lohnenswerten) Blick auf den Beitrag Oracle Apex – Quick SQL.

-- create tables
create table co_processing_step (
  id number not null constraint co_processing_step_id_pk primary key,
  name varchar2(255),
  description varchar2(4000),
  media_name varchar2(255),
  media_object blob,
  media_filename varchar2(255),
  media_mime_type varchar2(48),
  media_size number,
  media_content_type varchar2(4000),
  order_number number,
  created date not null,
  created_by varchar2(255) not null,
  updated date not null,
  updated_by varchar2(255) not null
)
;

create table co_ingredients (
  id number not null constraint co_ingredients_id_pk primary key,
  processing_step_id number
  constraint co_ingredient_processing_st_fk
  references co_processing_step on delete cascade,
  name varchar2(255),
  quantity number,
  quantity_unit varchar2(10),
  calories number,
  product_classification_id number,
  created date not null,
  created_by varchar2(255) not null,
  updated date not null,
  updated_by varchar2(255) not null
)
;

create table co_product_classification (
  id number not null constraint co_product_classif_id_pk primary key,
  name varchar2(255),
  description varchar2(4000),
  created date not null,
  created_by varchar2(255) not null,
  updated date not null,
  updated_by varchar2(255) not null
)
;

CREATE SEQUENCE co_processing_step_seq ORDER;

CREATE SEQUENCE co_ingredients_seq ORDER;

CREATE SEQUENCE product_classification_seq ORDER;

-- triggers
create or replace trigger co_processing_step_biu
   before insert or update
   on co_processing_step
   for each row
 begin
   if :new.id is null then
      select co_processing_step_seq.nextval into :new.id from dual;
   end if;
   if inserting then
     :new.created := sysdate;
     :new.created_by := nvl(sys_context('APEX$SESSION','APP_USER'),user);
   end if;
   :new.updated := sysdate;
   :new.updated_by := nvl(sys_context('APEX$SESSION','APP_USER'),user);
end co_processing_step_biu;
/

create or replace trigger co_ingredients_biu
   before insert or update
   on co_ingredients
   for each row
  begin
      if :new.id is null then
         select co_ingredients_seq.nextval into :new.id from dual;
      end if;
      if inserting then
        :new.created := sysdate;
        :new.created_by := nvl(sys_context('APEX$SESSION','APP_USER'),user);
      end if;
      :new.updated := sysdate;
      :new.updated_by := nvl(sys_context('APEX$SESSION','APP_USER'),user);
end co_ingredients_biu;
/

create or replace trigger co_product_classification_biu
   before insert or update
   on co_product_classification
   for each row
  begin
    if :new.id is null then
        select product_classification_seq.nextval into :new.id from dual;
    end if;
    if inserting then
      :new.created := sysdate;
      :new.created_by := nvl(sys_context('APEX$SESSION','APP_USER'),user);
    end if;
    :new.updated := sysdate;
    :new.updated_by := nvl(sys_context('APEX$SESSION','APP_USER'),user);
end co_product_classification_biu;
/

-- indexes
create index co_ingredients_i1 on co_ingredients (processing_step_id);
create index co_product_classification_i1 on co_product_classification (ingredient_id);

Listing 1: Create ingredients , processing_steps, …

Kopieren Sie den Inhalt des Skriptes in den Eingabebereich des SQL Commands und klicken Sie dann auf Run.

Abbildung 12: Skript ausführen
Abbildung 12: Skript ausführen

Sie bekommen anschließend eine Zusammenfassung und mögliche Fehlermeldungen angezeigt.

Select-Statements ausführen

Über das Tool SQL Commands lassen sich aber auch Select-Anweisungen entwickeln und ausführen.

Um zu prüfen, welche Tabellen in Ihrem Schema vorhanden sind, können Sie beispielsweise dieses Select-Staement bemühen.


select *
  from user_tables
 where table_name like 'CO%'

Führen Sie es einfach in SQL Commands aus. Das Ergebnis wird Ihnen als Tabelle angezeigt.

Abbildung 13: Select Statement in SQL Commands
Abbildung 13: Select Statement in SQL Commands

Beitragsreihe

Oracle Datenbankentwicklung: DDL-Trigger zur Auditierung von Strukturänderungen

Oracle bietet mit sogenannten DDL-Triggern eine einfache Möglichkeit Änderungen an Datenbankobjekten zu protokollieren. Eine nützliche Methode für Entwicklerteams.

Eine automatische Auditierung von DDL (Data Definition Language) -Aktionen in einem Datenbank-Schema ist in vielerlei Situationen nützlich. Z.B. können Sie auf diese Weise in einer Entwicklungsumgebung alle Strukturänderungen, die nach einem bestimmten Zeitpunkt erfolgt sind, nachvollziehen. Auf Basis so eines Protokolls sind Sie dann beispielsweise in der Lage ein Upgrade-Skript zu erstellen, mit dem Sie anschließend das produktive System aktualisieren können. Auch aus Sicherheitsgründen kann es sinnvoll sein, strukturelle Änderungen an einer Datenbank zu protokollieren.

Das Thema der DDL- oder auch Systemtrigger ist umpfangreich, soll hier aber nicht in Gänze betrachtet werden. In diesem Beitrag geht es um die Entwicklung  eines Auditierungsmechanismusses, der alle Änderungen an einem Schema protokolliert. Das sind vor allem create, alter, drop Befehle.

Eine Oracle-Datenbank bietet verschiedene Möglichkeiten der Auditierung. Die hier vorgestellte Variante ist aber recht leichtgewichtig und bietet durch ihre Anpassbarkeit viele Gestaltungsmöglichkeiten. So können Sie beispielsweise auch eine DSGVO angepasste, datensparsame  Variante erstellen.

Die dargestellten Beispiele sind mit viel Sorgfalt entstanden, allerdings übernehme ich keine Gewähr für etwaige Fehler oder Probleme, die bei der Nutzung auftreten. Bitte prüfen Sie selber die Beispiele vor der Nutzung.

Datenmodell

Das Datenmodell für dieses Szenario besteht aus zwei Tabellen.

Die Tabelle ddl_log nimmt für jede Änderung der Datenbankobjekte deren Name und Besitzer, den Typ des Objektes, den Zeitpunkt der Änderung und die Art der Änderung auf. Auch die Benutzerdaten sowie das Programm, mit dem die Änderung durchgeführt wurde, werden vermerkt. Zur Erzeugung eines künstlicher Schlüssel wird später eine Sequence verwendet.

Die zweite Tabelle ddl_log_sql ergänzt diese Information um das eigentliche DDL-Statement, welches  ausgeführt wurde.

CREATE TABLE ddl_log
(
  event_id NUMBER (10, 0),
  event_date DATE,
  user_id VARCHAR2 (30 BYTE),
  object_name VARCHAR2 (30 BYTE),
  owner VARCHAR2 (30 BYTE),
  object_type VARCHAR2 (30 BYTE),
  system_event VARCHAR2 (30 BYTE),
  machine VARCHAR2 (64 BYTE),
  program VARCHAR2 (64 BYTE)
)
/

CREATE TABLE ddl_log_sql
(
  event_id NUMBER (10, 0),
  sqlline NUMBER (10, 0),
  sqltext VARCHAR2 (4000 BYTE)
)
/

CREATE SEQUENCE ddl_log_id INCREMENT BY 1
  START WITH 1
  MINVALUE 1
  MAXVALUE 9999999999999999999999999999
  NOCYCLE
  ORDER
  CACHE 20
/

Listing 1: Datenmodell anlegen

Mit Listing 1 können Sie die beschriebenen Objekte anlegen. Dies sollten Sie in dem Schema tun, in dem Sie die Änderungen protokollieren möchten. Alternativ können Sie aber auch ein eigenes Schema dafür definieren. Allerdings müssen Sie dann den Schema-Namen im Quellcode des Triggers ergänzen und denn Tabellen sowie der Sequence voranstellen.

DDL-Trigger

Ein DDL-Trigger wird anders als ein DML-Trigger bei DDL- bzw. Systemereignissen ausgeführt. Das können z.B. Strukturänderungen wie das Erstellen einer Tabelle oder eines PL/SQL Packages, das Droppen einer Tabelle oder das Anlegen einer View aber auch das Anmelden eines Benutzers sein.

Der Ausführungspunkt so eines Triggers kann vor (BEFORE) oder nach (AFTER) der Abarbeitung des DDL-Statements festgelegt werden.

Sie können einen DDL-Trigger global für die komplette Datenbank (DATABASE) oder nur für ein bestimmtes SCHEMA definieren.

Einen DDL-Trigger, der alle Strukturellen Änderungen an Ihrem Schema protokolliert, können Sie – wie in Listing 2 gezeigt – erzeugen. Führen Sie dazu das Skript mit einem Datenbanktool (sqlplus, SQL Developer, APEX SQL Workshop,…) aus. Melden Sie sich dabei mit dem SCHEMA Account an.

Tauschen Sie das keyword SCHEMA durch DATABASE, so wird der Trigger bei allen Änderungen an der gesamten Datenbank gefeuert. Aber Achtung, Sie müssen dafür dann das Schema, in denen sich die verwendeten Tabellen befinden, in dem Trigger hinzufügen.


CREATE OR REPLACE TRIGGER trg_ddl_log
 AFTER DDL
 ON SCHEMA
DECLARE
  l_sql ora_name_list_t;
  l_id NUMBER (10, 0);
BEGIN
  SELECT ddl_log_id.NEXTVAL INTO l_id FROM DUAL;

  INSERT INTO ddl_log (event_id,
    event_date,
    user_id,
    object_name,
    owner,
    object_type,
    system_event,
    machine,
    program)
  (SELECT l_id,
          SYSDATE,
          ora_login_user,
          ora_dict_obj_name,
          ora_dict_obj_owner,
          ora_dict_obj_type,
          ora_sysevent,
          UPPER (SYS_CONTEXT ('USERENV', 'TERMINAL')),
          SYS_CONTEXT ('USERENV', 'MODULE')
       FROM DUAL);

FOR l IN 1 .. ora_sql_txt (l_sql)
  LOOP
    INSERT INTO ddl_log_sql (event_id, sqlline, sqltext)
      VALUES (l_id, l, l_sql (l));
  END LOOP;
END;
/

Listing 2: Create DDL-Audit-Trigger

Der Trigger wird nach allen Änderungen, die an Objekten des jeweiligen Schemas ausgeführt werden, gefeuert.

Zu Begin des Triggers wird mit Hilfe der Sequence ddl_log_id ein künstlicher Schlüssel erzeugt und in der Variable l_id gespeichert.

Das erste Insert-Statement protokolliert die Art der Änderung in der Tabelle ddl_log. Dabei wird dem Datensatz die zuerst erzeugte ID aus der Variablen l_id als Primärschlüssel zugeordnet. Hier werden neben dem Zeitpunkt der Änderung und das geänderte Datenbankobjekt, auch die Benutzerdaten und das verwendete Programm ermittelt und gespeichert.

Im nächsten Schritt wird das DDL-Statement, welches zur Ausführung des Triggers geführt hat, zeilenweise in der Tabelle ddl_log_sql eingefügt. Jeder Zeile wird neben einer Nummerierung auch die l_id als Fremdschlüssel zugeordnet.

Sollten Sie auf die persohnenpersonen Daten – wie den Benutzername und den Maschinennamen des Clients – verzichten wollen, können Sie den Trigger in einer DSGVO freundlicheren Version mit dem Listing 3 anlegen. Dabei werden diese beiden Spalten einfach mit null vorbelegt.


CREATE OR REPLACE TRIGGER trg_ddl_log
 AFTER DDL
 ON SCHEMA
DECLARE
  l_sql ora_name_list_t;
  l_id NUMBER (10, 0);
BEGIN
  SELECT ddl_log_id.NEXTVAL INTO l_id FROM DUAL;

  INSERT INTO ddl_log (event_id,
    event_date,
    user_id,
    object_name,
    owner,
    object_type,
    system_event,
    machine,
    program)
  (SELECT l_id,
          SYSDATE,
          null,
          ora_dict_obj_name,
          ora_dict_obj_owner,
          ora_dict_obj_type,
          ora_sysevent,
          null,
          SYS_CONTEXT ('USERENV', 'MODULE')
       FROM DUAL);

FOR l IN 1 .. ora_sql_txt (l_sql)
  LOOP
    INSERT INTO ddl_log_sql (event_id, sqlline, sqltext)
      VALUES (l_id, l, l_sql (l));
  END LOOP;
END;
/

Listing 3: DSGVO freundlicherer DDL-Audit-Trigger

Möchten Sie den Trigger inklusive der zugehörigen Datenbankobjekte aus Ihrem Schema entfernen, können Sie dafür das Listing 4 nutzen. Wichtig dabei ist die vorgegebene Ausführungsreihenfolge. Würden Sie die Tabellen zuerst zu droppen, so würden Sie dem DDL-Trigger seine abhängigen Objekte entziehen. Anschließend würde der Trigger invalid und zu einem Fehler führen.


DROP TRIGGER trg_ddl_log;
DROP TABLE ddl_log;
DROP TABLE ddl_log_sql;
DROP SEQUENCE ddl_log_id;

Listing 4: Trigger und Tabellen löschen

Generell sollten Sie bei der Verwendung von DDL-Triggern etwas Vorsicht walten lassen, da Sie bei fehlerhafter Verwendung, sich quasi bestimmter Datenbank-Funktionalitäten entledigen könnten.

Anwendungsbeispiele

Ich habe in dem Beitrag Oracle Application Express – Quick SQL bespielhaft ein einfaches Datenmodell für eine Projektverwaltung erstellt. Das zugehörige DDL-Skript habe ich exemplarisch ausgeführt und mit Hilfe des DDL-Audit-Triggers aus Listing 3 protokollieren lassen.

Im Folgenden finden Sie einige einfachen select-Anweisungen, mit deren Hilfe Sie verschiedene Fragestellungen beantworten können.

Wie Sie der Abbildung 1 entnehmen können, wurden folgende Änderungen an der Datenbank vorgenommen.

Abbildung 1: DDL-Log Änderungshistorie

Wenn Sie sich dieses Log ansehen möchten, genügt folgende select-Statement Anweisung:


select event_id,
       event_date,
       user_id,
       object_name,
       owner,
       object_type,
       system_event,
       machine,
       program
 from ddl_log
  order by event_id

Listing 5: Änderungshistorie anzeigen

Das konkrete DDL-Statement, dass für Anlegen der Tabelle projects verantwortlich war,  wurde in der Tabelle ddl_log_sql protokolliert. Eine kleine Unschönheit bringt dieses Protokoll mit sich. Bitte achten Sie darauf, dass die zeilenweise Speicherung teilweise mitten in einer SQL-Anweisung die Zeile umbricht. Dies muss bei der Rekonstruktion des Skriptes beseitigt werden.

Abbildung 2: DDL-Statement

Dieses läßt sich für einen einzelnen Schritt – wie in Listing 6 gezeigt – ermitteln.


select event_id, 
       event_date, 
       object_type,
       sqltext
from ddl_log_sql 
  where event_id = 2
 order by sqlline

Listing 6: DDL-Anweisungen für event_id =2 ermitteln

Interessieren Sie sich für die Änderungen,  die zwischen zwei Releaseständen Ihres Datenbank-Schemas  durchgeführt wurden, können Sie die select-Anweisungen um folgende where Klausel ergänzen. Alternativ können Sie auch die Einschränkungen über den Zeitstempel vornehmen.


select l.event_id, 
       event_date, 
       object_type,
       sqltext
from ddl_log l, 
     ddl_log_sql s
where l.event_id = s.event_id
  and l.event_id > 2 and l.event_id < 10
order by l.event_id, 
         sqlline

Listing 7: Alle DDL-Anweisungen die nach event_id 2 bis zur event_id 5 ausgeführt wurden

Auf Basis so eines Protokolls, können Sie anschließend ein Upgrade-Skript erarbeiten oder die Release-Notes für die neue Softwareversion ergänzen.

Auch die Änderungen an einem bestimmten Datenbankobjekt, hier der Tabelle TO_DOS, können nachvollzogen werden. Dazu finden Sie in Listing 8 ein einfaches Beispiel.


select l.event_id , 
       l.object_name, 
       event_date, 
       object_type,  
       sqltext
from ddl_log l, 
     ddl_log_sql s
where l.event_id = s.event_id
  and l.object_name = 'TO_DOS'
order by l.event_id, 
         sqlline

Listing 8: DDL-Statement zum Erstellen der Tabelle TO_DOS ermitteln

So einen DDL-Audit-Trigger ist gerade auch für Entwicklerteams hilfreich. Hier ist für alle Programmierer leicht nachvollziehbar, welche Änderungen an dem Datenmodell durchgeführt wurden. Auch zum prüfen von Installationsvorgängen lässt sich die Protokollierung einsetzen.

Wie Sie meine ScreenShots entnehmen konnten, setzte ich diesen Trigger auch gerne bei der Entwicklung von APEX-Anwendungen ein. Gerade durch die Beschränkung seiner Ausführung auf die Änderungen eines Datenbankschemas können so geziehlt alle Entwicklungsschritte auf Datenbankebene nachvollzogen werden. Auf Basis der Tabelle ddl_log_sql lassen sich dann Scripte für die Supported Objects erstellen.