Logo Oracle Deutschland   Application Express Community
Oracle 12c Release 1 ist verfügbar: Neue Features (nicht nur) für APEX-Entwickler

Oracle12c Release 1 steht zum Download bereit. Darin ist out-of-the-box die Version APEX 4.2.0 enthalten. Als ersten Einblick in die neue Datenbankversion haben wir im Folgenden eine kleine Auswahl interessanter Neuerungen speziell für Entwickler zusammengestellt. Bei den Kollegen der DBA Community finden Sie entsprechend eine Übersicht mit den für Administratoren und den Datenbankbetrieb interessanten Neuerungen. Auch die Kollegen des APEX Development-Teams haben eine Informationsseite zum Thema APEX und Oracle12c zusammengestellt.

  1. Oracle Multitenant: Mehrere APEX-Versionen auf einem Datenbankserver
  2. 32K VARCHAR2 - überall
  3. Automatische Sequences und Identity Columns
  4. SQL und PL/SQL: Erweiterungen und Verbesserungen
  5. Verbessertes PL/SQL Error-Handling: Package UTL_CALL_STACK
  6. PL/SQL: Rechte, Rollen und mehr
  7. Daten "verstecken": Mit Data Redaction
  8. SQL Pattern Matching
  9. Blättern im SQL Ergebnis: LIMIT und OFFSET
  10. Tabellenspalten: Sichtbar und unsichtbar
  11. Wann ist die Zeile gültig: Valid Time Temporal
  12. Java in der Datenbank: Was ist neu?

  13. Zurück zur Community-Seite

Hinweis: Dieser Tipp enthält keinerlei Lizenzhinweise - er beschränkt sich auf die technische Funktionalität der neuen Features. Detaillierte Informationen zur Lizenzierung finden Sie in Oracle Database Licensing Information 12c Release 1 (12.1).

Oracle Multitenant: Mehrere APEX-Versionen auf einem Datenbankserver

Eins der schon im Vorfeld meistdiskutierten Themas in Oracle12c ist Oracle Multitenant. Oracle Multitenant erlaubt es, kurz gesagt, mehrere Oracle-Datenbanken ("Pluggable Databases ") in einer Container-Datenbank zu betreiben. Man hat also die Betriebssystem-Prozesse und damit die Oracle-Instanz nur einmal - diese "betreibt" dann jedoch mehrere, voneinander getrennte Datenbanken (Abbildung 1).

Wenn Sie APEX in einer Oracle12c Multitenant Architektur nutzen, ist mindestens APEX 4.2 erforderlich - ältere Versionen können nicht verwendet werden. Am besten nehmen Sie gleich das aktuelle Patchset APEX 4.2.2.

Oracle Multitenant: APEX in der Pluggable Database installieren ...

Abbildung 1: Oracle Multitenant: APEX in der Pluggable Database installieren ...

Ist man an der Container Database angemeldet, so kann man -global- alle eingehängten Pluggable Databases betrachten. In einer Pluggable Database selbst hat man den Eindruck einer "normalen" Oracle-Datenbank. Die Inhalte einer anderen Pluggable Database sieht man nicht. Demzufolge können künftig, innerhalb einer Container-Datenbank, mehrere Pluggable Databases mit unterschiedlichen APEX-Versionen betrieben werden. Da eine neue Pluggable Database sehr einfach als Klon einer existierenden erzeugt werden kann, ist das Testen eines APEX-Upgrades mit dieser Architektur sehr viel einfacher.

Für APEX-Umgebungen bietet sich noch eine zweite Variante an: Wird APEX in der Container-Datenbank installiert, so "scheint" die APEX-Engine in alle Pluggable Databases "durch". Die APEX-Anwendungen in den Pluggable Databases selbst sind "privat" - und in anderen Pluggable Databases nicht sichtbar. Die APEX-Engine dagegen ist nur einmal vorhanden. Ein APEX Upgrade oder Patching wirkt daher sofort auf alle Pluggable Databases durch. Dieses Verfahren eignet sich sehr gut für Umgebungen, in denen einzelne Projekte nicht nur APEX-Workspaces, sondern ganze Datenbanken für sich bekommen. Auch hier können nun mehrere Projekte auf den gleichen Datenbankserver gelegt werden - für das Projekt sieht alles genauso aus, wie vorher.

Oracle Multitenant: APEX in der Container Database installieren ...

Abbildung 2: Oracle Multitenant: APEX in der Container Database installieren ...

Das "In-Place-Patchen" der in der 12c-Datenbank enthaltenen APEX Version ist derzeit (noch) nicht möglich; Sie können aber die APEX-Version 4.2.2 aus dem OTN herunterladen, die in Oracle12c vorhandene APEX-Installation entfernen und APEX 4.2.2 neu installieren. Zu einem späteren Zeitpunkt wird es einen Patch geben, der nach Einspielen sowohl das "In-Place-Patchen" der APEX-Installation als auch die lokale Installation in eine Pluggable Database erlaubt. Mehr zum Thema finden Sie im Blog von Jason Straub.

Oracle Multitenant kann übrigens auch mit dem aktuellen SQL Developer bedient werden; sobald Sie sich mit einer Oracle12c Container-Datenbank verbinden, taucht im Bereich DBA der entsprechende Menüpunkt auf - hier können Sie nun neue Pluggable Databases erzeugen oder bestehende verwalten.

Oracle Multitenant mit dem SQL Developer verwalten

Abbildung 3: Oracle Multitenant mit dem SQL Developer verwalten

Oracle Multitenant eröffner völlig neue Möglichkeiten für Setup und Betrieb von APEX-Umgebungen - das Betreiben mehrerer Oracle-Instanzen ist nicht mehr unbedingt nötig.

Weitere Informationen

Zurück zum Anfang des Artikels

32K VARCHAR2 - überall

Auf dieses Feature haben SQL- und PL/SQL Entwickler lange gewartet. VARCHAR2-Spalten einer Tabelle können ab Oracle12c bis zu 32767 Bytes aufnehmen und verhalten sich damit genauso wie in PL/SQL. Wenn man also abschätzen kann, dass die benötigte Länge unterhalb von 32Kb bleibt, kann man sich den (komplizierteren) Einsatz von CLOBs sparen. 

create table meine_tabelle(
  id         number(10),
  langertext varchar2(6000)
)
/

Ganz ohne den DBA geht es dann aber doch nicht: Damit das wirklich funktioniert, muss dieser den Datenbankparameter MAX_STRING_SIZE auf EXTENDED setzen und pro Datenbank einmal das Skript $ORACLE_HOME/rdbms/admin/utl32k.sql laufen lassen (ausführliche Information in der Oracle Database Reference: MAX_STRING_SIZE). In einer APEX Umgebung sollte danach zusätzlich das Skript $APEX_HOME/core/collection_member_resize.sql gestartet werden, damit das neue Limit auch in APEX Collections genutzt werden kann.

Weitere Informationen

Zurück zum Anfang des Artikels

Automatische Sequences und Identity Columns

Der Umgang mit Sequences und das Generieren eindeutiger Werte wird im Oracle12c nochmals einfacher. So ist es nun möglich, den "nächsten Wert" der Sequence als Default für eine Tabellenspalte zu hinterlegen. Das bislang nötige und lästige Erstellen des Triggers gehört damit der Vergangenheit an. 

create sequence seq_id
/

create table meine_tabelle(
  id      number(10)      default seq_id.nextval,
  text    varchar2(6000)
)
/

insert into meine_tabelle (text) values ('Text 1');
insert into meine_tabelle (text) values ('Text 2');

select * from meine_tabelle
/

SQL> select * from meine_tabelle;

    ID TEXT
------ ------------------------------
     1 Text 1
     2 Text 2

2 Zeilen ausgewählt.

Es geht sogar noch einfacher: Mit der GENERATED AS IDENTITY-Klausel kann die Definition der Sequence zu der der Tabelle genommen werden. Das explizite Erzeugen einer Sequence fällt damit weg. 

create table meine_tabelle(
  id      number(10)      generated always as identity start with 1 increment by 1,
  text    varchar2(6000)
)
/

Die GENERATED ... AS IDENTITY Klausel kennt noch einige Variationen. Die dargestellte Variante GENERATED ALWAYS nimmt immer den Wert der Sequence für die Tabellenspalte, auch wenn im SQL INSERT explizit ein Wert vorgegeben wurde. Alternativ lässt sich ein GENERATED BY DEFAULT oder BY DEFAULT ON NULL spezifizieren.

Weitere Informationen

Zurück zum Anfang des Artikels

SQL und PL/SQL: Erweiterungen und Verbesserungen

Im Bereich SQL und PL/SQL gibt es noch weitere Verbesserungen im Detail.

  • Die WITH-Klausel einer SQL-Abfrage kann nun mit der Definition von Funktionen versehen werden. Eine so definierte Funktion gilt dann nur für diese Abfrage. 
    with
  function half_sal(p_sal in number) return number 
  is 
  begin 
    return p_sal/2; 
  end;
select empno, sal, half_sal(sal) from emp
/

     EMPNO        SAL HALF_SAL(SAL)
---------- ---------- -------------
      7369        800           400
      7499       1600           800
      7521       1250           625
      7566       2975        1487,5
    Achtung: Wenn Sie das mit einem älteren SQL*Plus ausprobieren, setzen Sie vorher das Kommando set sqlterminator off oder set sqlterminator # ab. Ansonsten denkt es, dass die SQL-Abfrage beim ersten PL/SQL Semikolon zu Ende ist. SQL*Plus 12.1 hat damit natürlich keine Probleme.

  • PL/SQL-Funktionen, deren Parameter PL/SQL Datentypen erwarten (bspw. BOOLEAN), können nun aus SQL heraus aufgerufen werden ... 
    create or replace function sql_print_boolean (
  p_bool in boolean 
) return varchar2 is
begin
  if p_bool then return 'TRUE'; else return 'FALSE'; end if;
end sql_print_boolean;
/

FUNCTION sql_print_boolean RETURNS VARCHAR2
 Argument Name                  Typ                     In/Out Defaultwert?
 ------------------------------ ----------------------- ------ --------
 P                              BOOLEAN                 IN

declare
  l_boolean boolean := true;
  l_varchar varchar2(100) := '';
begin
  select sql_print_boolean(l_boolean) into l_varchar from dual;
  dbms_output.put_line(l_varchar);
end;
/
    Die Rückgabe von PL/SQL-Datentypen nach SQL ist dagegen nach wie vor nicht möglich.

  • Mit der ACCESSIBLE BY-Klausel können Sie festlegen, dass ein PL/SQL-Package, -Prozedur oder Funktion nur von einem (oder mehreren) anderen PL/SQL-Objekt(en) - und nicht direkt - aufgerufen werden kann. Das ist besonders nützlich für "Helper-Packages", die nicht zum direkten Aufruf und nur zur Nutzung durch andere Packages vorgesehen sind. Die neue Klausel verhindert das versehentliche (oder absichtliche) Aufrufen und dadurch eventuell entstehende Seiteneffekte. 
    create or replace package pkg_helper accessible by (toplevel_function) is
  function get_hello return varchar2;
end pkg_helper;
/

create or replace package body pkg_helper is
  function get_hello return varchar2 is
  begin
    return 'Hello World';
  end;
end pkg_helper;
/

create or replace function toplevel_function return varchar2 is
begin
  return 'Call via function: ' || pkg_helper.get_hello;
end toplevel_function;
/

SQL> select toplevel_function from dual;

TOPLEVEL_FUNCTION
-------------------------------------
Call via function: Hello World

SQL> select pkg_helper.get_hello from dual;

select pkg_helper.get_hello from dual
       *
FEHLER in Zeile 1:
ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-904: insufficient privilege to access object PKG_HELPER

Weitere Informationen

Zurück zum Anfang des Artikels

Verbessertes PL/SQL Error-Handling: Package UTL_CALL_STACK

Wir jedes Datenbankrelease führt auch Oracle12c neue PL/SQL-Pakete ein. Für Entwicker hochinteressant ist das neue Paket UTL_CALL_STACK , welches beim PL/SQL Exception Handling wertvolle Dienste leisten kann. Mit UTL_CALL_STACK lassen sich eine Menge Details über den aufgetretenen Fehler auslesen - das geht weit über die in Oracle11g vorhandenen Möglichkeiten hinaus. 

declare
  procedure level0 is 
  begin
    dbms_output.put_line('Ausgabe: Level 0');
    raise_application_error(-20000, 'Fehler auf Level 0', true);
  end level0;
  procedure level1 is
  begin
    dbms_output.put_line('Ausgabe: Level 1');
    level0;
  exception when others then 
    raise_application_error(-20001, 'Fehler auf Level 1', true);
  end level1;
begin
  level1;
exception
  when others then
    dbms_output.put_line(
     'Errorhandler: ' || 
     utl_call_stack.error_depth ||
     ' Ebenen auf dem Fehlerstack'
    );
    for i in 1..utl_call_stack.error_depth loop
      dbms_output.put_line('Errorhandler: Fehler #'||i||': '||utl_call_stack.error_number(i));
    end loop;
end;
/

Ausgabe: Level 1
Ausgabe: Level 0
Errorhandler: 3 Ebenen auf dem Fehlerstack
Errorhandler: Fehler #1: 20001
Errorhandler: Fehler #2: 6512
Errorhandler: Fehler #3: 20000

Weitere Informationen

Zurück zum Anfang des Artikels

PL/SQL: Rechte, Rollen und mehr

Im Bereich der Rechte, Rollen und deren Gültigkeit bei der Ausführung von PL/SQL in Oracle12c einiges getan. In Oracle11g gelten hier folgende Regeln.

  • PL/SQL-Objekte (Funktionen, Prozeduren) können mit den Rechten des aufrufenden Nutzers (AUTHID CURRENT_USER) oder denen des Eigentümers (AUTHID DEFINER) ablaufen. Default ist AUTHID DEFINER.
  • Läuft ein PL/SQL-Objekt mit den Rechten des Eigentümers ab, so sind alle Rollen des Eigentümers abgeschaltet - es sind also nur direkt vergebene Privilegien aktiv. Läuft die Prozedur dagegen mit den Rechten des aufrufenden Users, so sind dessen Rollen aktiv.
  • Wird eine PL/SQL-Funktion, die mit den Rechten des aufrufenden Users abläuft, in einer View verwendet, so wird diese de-facto immer mit den Rechten des Eigentümers der View aufgerufen, denn eine View läuft stets mit den Rechten des Eigentümers ab.

In Oracle12c können Rollen direkt an eine Prozedur vergeben werden. Voraussetzung ist, dass der Eigentümer der Prozedur die Rolle ebenfalls hat und es um Standard-Datenbankrollen geht. Die Rolle muss entweder von SYS oder vom Eigentümer an das PL/SQL Objekt vergeben werden.

Damit wird es möglich, PL/SQL-Code mit zusätzlichen Privilegien auszustatten, die der aufrufende Nutzer normalerweise nicht hat. Ein Beispiel: Die folgende Funktion zählt alle Tabellen in der Datenbank und gehört SYS. Da sie als AUTHID CURRENT_USER definiert ist, läuft sie stets mit den Rechten des aufrufenden Nutzers ab - es besteht also keine Gefahr, dass ein unterprivilegierter Nutzer sie missbrauchen kann. 

create or replace function get_total_table_count
return varchar2 authid current_user is
  l_cnt number := 0;
begin
  select count(*) into l_cnt from dba_tables;
  return 'User: '||sys_context('userenv','current_user')|| ': '||
         l_cnt ||' tables in the database';
end get_total_table_count;
/

grant execute on get_total_table_count to public
/

Führt man diese Funktion nun als Nutzer ohne DBA-Privilegien (SCOTT) aus, so bekommt man dieses Ergebnis. Das ist folgerichtig: SCOTT ist kein DBA, er kann die View DBA_TABLES demnach nicht sehen. 

SQL> select sys.get_total_table_count from dual;
select sys.get_total_table_count from dual
       *
FEHLER in Zeile 1:
ORA-00942: Tabelle oder View nicht vorhanden
ORA-06512: in "SYS.GET_TOTAL_TABLE_COUNT", Zeile 5

In Oracle11g wäre die einzige Möglichkeit gewesen, diese Prozedur mit den Rechten des Eigentümers auszuführen (AUTHID DEFINER). Dann würde aber die komplette Funktion mit allen Rechten von SYS ausgeführt - was mitunter nicht erwünscht ist. In Oracle12c gibt es nun eine weitere Möglichkeit: 

grant dba to function sys.get_total_table_count
/

Die DBA-Rolle wird also nur an die Funktion vergeben. Ruft man diese nun wieder als Nutzer ohne DBA-Privilegien (SCOTT) auf, so bekommt man dennoch ein Ergebnis. 

SQL> select sys.get_total_table_count from dual;

GET_TOTAL_TABLE_COUNT
--------------------------------------------------------
User: SCOTT: 2470 tables in the database

1 Zeile wurde ausgewählt.

Das direkte Abfragen von DBA_TABLES schlägt nach wie vor fehl ... 

SQL> select * from dba_tables;
select * from dba_tables
              *
FEHLER in Zeile 1:
ORA-00942: Tabelle oder View nicht vorhanden

Wird dieser Funktionsaufruf nun in eine View gepackt, würde das AUTHID CURRENT_USER wirkungslos werden. Angenommen, die View gehört dem User SCOTT. 

create or replace view v_total_table_count
as select sys.get_total_table_count from dual
/

grant select on v_total_table_count to public
/

Die SELECT-Privilegien dieser View wurden wiederum öffentlich gemacht (GRANT ... TO PUBLIC). Und nun ist es egal, mit welchem User wir die View aufrufen, die Ausgabe ist stets die gleiche: 

SQL> select * from scott.v_total_table_count;

GET_TOTAL_TABLE_COUNT
------------------------------------------------
User: SCOTT: 2470 tables in the database

1 Zeile wurde ausgewählt.

Es steht dort immer der User SCOTT, da die View stets mit den Privilegien des Eigentümers ausgeführt wird - und diese Eigenschaft "schlägt" auf die PL/SQL-Funktion durch. Die AUTHID CURRENT_USER-Klausel der PL/SQL-Funktion ist faktisch wirkungslos geworden. Diese Lücke wird mit der BEQUEATH-Klausel, die nun für eine View vergeben werden kann, geschlossen. 

create or replace view v_total_table_count bequeath current_user
as select sys.get_total_table_count from dual
/

Nun wird die Identität des aufrufenden Nutzers quasi "durch die View" an die verwendete PL/SQL-Funktion durchgereicht. Das Ergebnis hängt nun wieder vom aufrufenden User ab. 

USERNAME1@DB SQL> select * from scott.v_total_table_count;

GET_TOTAL_TABLE_COUNT
------------------------------------------------
User: USERNAME1: 2470 tables in the database

1 Zeile wurde ausgewählt.

Weitere Informationen

Zurück zum Anfang des Artikels

Daten "maskieren": Data Redaction

Das neue Feature Data Redaction erlaubt es, Ergebnisse einer SQL-Abfrage nur für die Ausgabe zu maskieren. Data Redaction ist von der Bedienung und Nutzung her recht ähnlich zur Virtual Private Database, im Gegensatz zu dieser blendet Data Redaction die Daten jedoch nicht aus, sondern es maskiert sie. Als Beispiel sollen die Inhalte der Spalte ENAME der Tabelle EMP geschützt werden - und zwar, in dem die ersten drei Zeichen durch ein Sternchen ersetzt werden. Das geht wie folgt: 

BEGIN
 DBMS_REDACT.ADD_POLICY(
   object_schema          => 'SCOTT', 
   object_name            => 'EMP', 
   column_name            => 'ENAME',
   policy_name            => 'redact_ename', 
   function_type          => DBMS_REDACT.REGEXP,
   expression             => '1=1',
   regexp_pattern         => '(...)(.*)',
   regexp_replace_string  => '***\2',
   regexp_position        => DBMS_REDACT.RE_BEGINNING,
   regexp_occurrence      => 0,
   regexp_match_parameter => 'i');
END;
/

In diesem Beispiel ist der Schutz immer aktiv (siehe Parameter expression) - das kann natürlich vom angemeldeten User, dem Daten oder anderen Bedingungen abhängig gemacht werden. Selektiert man nun die Tabelle EMP, so sieht das Ergebnis so aus: 

SQL> select empno, ename, sal from emp

     EMPNO ENAME                       SAL
---------- -------------------- ----------
      7369 ***TH                       800
      7499 ***EN                      1600
      7521 ***D                       1250
      7566 ***ES                      2975

Das interessante ist nun, dass die Tabellenspalte für WHERE-Klauseln weiterhin normal funktioniert - es wird allein die Ausgabe maskiert. 

SQL> select empno, ename, sal from emp where ename='KING';

     EMPNO ENAME                       SAL
---------- -------------------- ----------
      7839 ***G                       5000
 
SQL> select empno, ename, sal from emp where substr(ename,1,1)='S';

     EMPNO ENAME                       SAL
---------- -------------------- ----------
      7369 ***TH                       800
      7788 ***TT                      3000

Data Redaction ist also ein sehr einfach anwendbares Feature zum Schutz sensitiver Daten. An der Anwendung muss man nichts ändern - nach Einspielen der Policy ist der Schutz sofort aktiv. Allerdings sollte man vorher prüfen, ob die Anwendung nicht noch mehr mit den Daten tut, als sie nur anzuzeigen - sprich: Es könnte sein, dass die Anwendung intern auf die Daten im Klartext angewiesen ist - Data Redaction muss dann mit Umsicht eingesetzt werden.

Weitere Informationen

Zurück zum Anfang des Artikels

SQL Pattern Matching

Eine der interessantesten Neuerungen im Bereich der SQL-Funktionen ist das SQL Pattern Matching. Dieser Satz an SQL-Funktionen erlaubt es Ihnen, Muster in Tabellendaten zu finden - und hier kommt es vor allem auf Muster an, die sich über mehrere Tabellenzeilen hinweg ergeben.

Ein Beispiel für solche Muster wären Web-Sessions, die durch Klicks in einer Logdatei repräsentiert werden. Die Log-Datei enthält nur die einzelnen Klicks, etwa wie folgt: 

10.165.244.126 - - [05/May/2010:12:33:02 +0200] "GET / HTTP/1.1" 200 10003
10.165.244.126 - - [05/May/2010:12:33:02 +0200] "GET /spatialde_logo.png HTTP/1.1" 200 2094
10.165.244.126 - - [05/May/2010:12:33:02 +0200] "GET /oraclemaps.png HTTP/1.1" 200 113295
10.165.244.126 - - [05/May/2010:12:34:02 +0200] "GET / HTTP/1.1" 200 10003
10.165.244.126 - - [05/May/2010:12:34:02 +0200] "GET /spatialde_logo.png HTTP/1.1" 200 2094
10.165.244.126 - - [05/May/2010:12:34:02 +0200] "GET /oraclemaps.png HTTP/1.1" 200 113295
10.165.244.126 - - [05/May/2010:12:35:02 +0200] "GET / HTTP/1.1" 200 10003
10.165.244.126 - - [05/May/2010:12:35:02 +0200] "GET /spatialde_logo.png HTTP/1.1" 200 2094

Wie man sehen kann, enthält die Logdatei nur "Einzelaufnahmen" - eben das Abrufen einer Webseite (was auf einem Klick auf einen Link basieren mag). Eine Web-Session wird durch mehrere Zeilen in dieser Tabelle repräsentiert - explizit ist die Information nicht enthalten. Man muss die Sessions aus den Daten rekonstruieren. Dass die CLIENT_IP in einer Session dieselbe sein muss, ist klar. Das Ende der Session ist aber nicht direkt aus den Daten ableitbar - es wird angenommen, dass die Session endet, wenn die Zeitspanne bis zum nächsten Klick eine bestimmter Dauer (5 Minuten) übersteigt (natürlich vereinfacht dieses Beispiel stark).

Die Zeilen, die zu einer Websession gehören, müssen also wie folgt gefunden werden:

  • Zunächst müssen die Daten nach CLIENT_IP und ZEITSTEMPEL sortiert werden
  • Die allererste Zeile markiert den Beginn der ersten Session
  • Wenn die nächste Zeile die gleiche CLIENT_IP enthält und die Differenz der Zeitstempel nicht mehr als 5 Minuten beträgt, dann gehört die nächste Zeile zur gleichen Session, ansonsten ist sie der Beginn der nächsten Session.

Man kann sich nun vorstellen, dass es nicht einfach ist, eine solche Aufgabe mit Standard-SQL zu lösen. Das in Oracle12c neue SQL Pattern Matching macht die Aufgabe leicht - und ein Beispiel soll das demonstrieren: Für das Beispiel braucht es eine Tabelle mit den Inhalten einer Logdatei. Wenn Sie (für APEX) den Apache Webserver mit mod_plsql verwenden, können Sie dessen Logdatei access_log zum Ausprobieren hernehmen. Kopieren Sie diese auf den Datenbankserver und erzeugen Sie eine externe Tabelle für die Datei wie folgt. 

create directory apache_log_dir as '/path/to/folder/with/apache/access_log'
/

drop table ohs_access_log
/

CREATE TABLE ohs_access_log (
  client_ip   varchar2(200),
  client_id   varchar2(200),
  remote_user varchar2(200),
  zeitstempel timestamp with time zone,
  request     varchar2(500),
  http_code   number,
  content_length number
)
ORGANIZATION EXTERNAL (
  TYPE oracle_loader
  DEFAULT DIRECTORY apache_log_dir
  ACCESS PARAMETERS (
  RECORDS DELIMITED BY NEWLINE 
  nologfile nobadfile
    fields MISSING FIELD VALUES ARE NULL(
      client_ip      char terminated by whitespace ltrim,
      client_id      char terminated by whitespace ltrim,
      remote_user    char terminated by whitespace ltrim,
      zeitstempel    char terminated by whitespace enclosed by '[' and ']' 
        date_format timestamp with time zone "DD/MON/YYYY:HH24:MI:SS:+TZHTZM",
      request        char terminated by whitespace enclosed by '"' and '"',
      http_code      char terminated by whitespace, 
      content_length char terminated by whitespace
    )
   )
   LOCATION ('access_log') 
)
REJECT LIMIT UNLIMITED parallel 
/

SQL> select client_ip, zeitstempel, request, http_code 
  2  from ohs_access_log where rownum <= 10;

CLIENT_IP       ZEITSTEMPEL               REQUEST                                   HTTP_CODE
--------------- ------------------------- ---------------------------------------- ----------
10.165.244.126  2010-05-05 12:33:02 +0200 GET / HTTP/1.1                                  200
10.165.244.126  2010-05-05 12:33:02 +0200 GET /spatialde_logo.png HTTP/1.1                200
10.165.244.126  2010-05-05 12:33:02 +0200 GET /oraclemaps.png HTTP/1.1                    200
10.165.244.126  2010-05-05 12:34:02 +0200 GET / HTTP/1.1                                  200
10.165.244.126  2010-05-05 12:34:02 +0200 GET /spatialde_logo.png HTTP/1.1                200
10.165.244.126  2010-05-05 12:34:02 +0200 GET /oraclemaps.png HTTP/1.1                    200
10.165.244.126  2010-05-05 12:35:02 +0200 GET / HTTP/1.1                                  200
10.165.244.126  2010-05-05 12:35:02 +0200 GET /spatialde_logo.png HTTP/1.1                200
10.165.244.126  2010-05-05 12:35:02 +0200 GET /oraclemaps.png HTTP/1.1                    200
10.165.244.126  2010-05-05 12:36:02 +0200 GET / HTTP/1.1                                  200

Anschließend können Sie - mit einer SQL-Abfrage (!) nach Web-Sessions suchen ... 

SELECT client_ip, start_tstamp, end_tstamp, sess_clicks
FROM ohs_access_log
MATCH_RECOGNIZE (
     PARTITION BY client_ip
     ORDER BY zeitstempel asc
     MEASURES  STRT.zeitstempel AS start_tstamp,
               LAST(NXT.zeitstempel) AS end_tstamp,
               FINAL COUNT(nxt.zeitstempel) as sess_clicks
     ONE ROW PER MATCH
     AFTER MATCH SKIP PAST LAST ROW
     PATTERN (STRT NXT+)
     DEFINE
        NXT AS NXT.ZEITSTEMPEL < PREV(NXT.ZEITSTEMPEL) + INTERVAL '300' second
     ) MR
where sess_clicks > 2
order by client_ip, start_tstamp

In guter alter SQL Manier wird der Datenbank nicht mehr gesagt, wie die Aufgabe zu erledigen, sondern dass ein Pattern Matching erledigt werden soll. Die nötigen Angaben erfolgen deklarativ - es wird festgelegt ...

  • ... wie die Daten unterteilt werden sollen (PARTITION BY)
  • ... wie die Daten sortiert werden sollen (ORDER BY)
  • ... welche Berechnungen während des Pattern Matching erfolgen sollen (MEASURES)
  • ... welche Zeilen zurückgegeben werden sollen (ONE ROW PER MATCH)
  • ... wie das Pattern aussehen soll (START NXT+) und (hier:) welche Zeitspanne zwischen zwei Klicks liegen darf (DEFINE)

Als Ergebnis liefert diese SQL-Abfrage eine Übersicht über die gefundenen Sessions zurück. 

CLIENT_IP       START_TSTAMP                    END_TSTAMP                      CLICKS
--------------- ------------------------------- ------------------------------- ------
10.165.114.244  26.04.11 09:57:48,000000 +02:00 26.04.11 09:59:56,000000 +02:00      3
10.165.115.2    21.04.11 11:26:52,000000 +02:00 21.04.11 12:47:28,000000 +02:00    129
10.165.115.30   26.04.11 11:06:13,000000 +02:00 26.04.11 12:42:14,000000 +02:00    166
10.165.115.9    26.04.11 08:11:30,000000 +02:00 26.04.11 08:11:36,000000 +02:00     47
:               :                               :                                    :

Weitere Informationen

Zurück zum Anfang des Artikels

Blättern im SQL Ergebnis: LIMIT und OFFSET

Zwei neue SQL-Klauseln für Abfragen machen das Blättern in einem Bericht wesentlich einfacher: Die LIMIT-Klausel sorgt dafür, dass nur eine bestimmte Anzahl Zeilen abgerufen wird, die OFFSET-Klausel bewirkt, dass vorher eine bestimmte Anzahl geholt wird. Was man bislang also aufwändig mit Subselects und ROWNUM gemacht hat, ist nun wesentlich einfacher und klarer. Die folgende Abfrage selektiert demnach die Zeilen aus der Tabelle EMP mit dem viert-, fünft- und sechsthöchstem Gehalt. 

select empno, ename, sal 
from emp
order by sal asc
offset 3 rows fetch first 3 rows only
/

     EMPNO ENAME                       SAL
---------- -------------------- ----------
      7521 WARD                       1250
      7654 MARTIN                     1250
      7934 MILLER                     1300

Das Abrufen einer bestimmten Teilmenge von Daten ist damit wesentlich leichter als früher.

Weitere Informationen

Zurück zum Anfang des Artikels

Tabellenspalten: Sichtbar und unsichtbar

Tabellenspalten können in Oracle12c "unsichtbar" gemacht werden. 

SQL> alter table EMP modify (COMM invisible, HIREDATE invisible)
/

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

SQL> select * from emp;

     EMPNO ENAME                JOB              MGR        SAL     DEPTNO
---------- -------------------- --------- ---------- ---------- ----------
      7369 SMITH                CLERK           7902        800         20
      7499 ALLEN                SALESMAN        7698       1600         30
      7521 WARD                 SALESMAN        7698       1250         30
      7566 JONES                MANAGER         7839       2975         20

Die Tabellenspalten (hier: HIREDATE und COMM) sind tatsächlich nicht mehr sichtbar. Spricht man sie dagegen explizit an, kann man normal damit arbeiten. 

SQL> select ename, hiredate, sal, comm from emp

ENAME                HIREDATE                   SAL       COMM
-------------------- ------------------- ---------- ----------
SMITH                17.12.1980 00:00:00        800
ALLEN                20.02.1981 00:00:00       1600        300
WARD                 22.02.1981 00:00:00       1250        500
JONES                02.04.1981 00:00:00       2975

Weitere Informationen

Zurück zum Anfang des Artikels

Wann ist die Zeile gültig: Valid Time Temporal

Oracle12c beginnt mit der Einführung eines Valid Time Supports für Tabellenzeilen. Mit diesem Feature kann eine Tabellenzeile mit einem Gültigkeitszeitraum versehen werden. Bei Abfragen wird dann ein Zeitpunkt mitgegeben - und die Abfrage liefert nur die Zeilen zurück, die zu jenem Zeitpunkt gültig waren.

Bitte beachten Sie, dass Valid Time Temporal (noch) nicht in einer Oracle Multitenant Architektur funktioniert; Sie können es nur verwenden, wenn Sie die Datenbank "klassisch", also ohne Container- und Pluggable Datenbank erzeugt haben. 

SQL> alter table emp ADD PERIOD FOR valid_time;

Tabelle wurde geändert.

Oracle fügt der Tabelle nun neue, unsichtbare Spalten hinzu. 

SQL> select column_name, data_type, hidden_column from user_tab_cols where table_name='EMP'

COLUMN_NAME          DATA_TYPE                           HID
-------------------- ----------------------------------- ---
EMPNO                NUMBER                              NO
ENAME                VARCHAR2                            NO
JOB                  VARCHAR2                            NO
MGR                  NUMBER                              NO
HIREDATE             DATE                                NO
SAL                  NUMBER                              NO
COMM                 NUMBER                              NO
DEPTNO               NUMBER                              NO
VALID_TIME_START     TIMESTAMP(6) WITH TIME ZONE         YES
VALID_TIME_END       TIMESTAMP(6) WITH TIME ZONE         YES
VALID_TIME           NUMBER                              YES

Nun kann für jede Tabellenzeile die Gültigkeit gesetzt werden ... das folgenden Update-Kommando macht alle Zeilen mit DEPTNO = 20 nur im Dezember 2012 gültig. 

update emp set 
  VALID_TIME_START = to_timestamp('2012-12-01','YYYY-MM-DD'),
  VALID_TIME_END   = to_timestamp('2012-12-31','YYYY-MM-DD')
where deptno = 20
/

Mit einem Aufruf von DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME kann nun der "Abfragezeitpunkt" gesetzt werden. Daraufhin werden die entsprechenden Zeilen mit DEPTNO = 20 fehlen ... 

begin
  dbms_flashback_archive.ENABLE_AT_VALID_TIME (
    level      => 'ASOF', 
    query_time => to_timestamp('2012-01-01','YYYY-MM-DD')
  );
end;
/

SQL> select deptno, count(empno) anzahl from emp group by deptno;

    DEPTNO     ANZAHL
---------- ----------
        30          6
        10          3

Der "Abfragezeitstempel" kann natürlich auch wieder abgeschaltet werden. 

begin
  dbms_flashback_archive.ENABLE_AT_VALID_TIME (
    level      => 'ALL' 
  );
end;
/

SQL> select deptno, count(empno) anzahl from emp group by deptno;

    DEPTNO     ANZAHL
---------- ----------
        30          6
        20          5
        10          3

Anstatt eine Tabellenzeile zu löschen oder zu verändern, kann man nun die Gültigkeit in den Spalten VALID_TIME_START und VALID_TIME_END setzen. Allerdings beherrscht die Datenbank (noch) nicht das Management eines Temporal Primary Key: Sie müssen also beim Erstellen des Primary Key Constraints die Tatsache berücksichtigen, dass es mehrere Zeilen mit der gleichen ID, aber unterschiedlicher Gültigkeit geben kann. Zusammengesetzte Primärschlüssel können hier helfen.

Weitere Informationen

Zurück zum Anfang des Artikels

Java in der Datenbank: Was ist neu?

Wie die meisten Leser wissen, kann die Oracle-Datenbank nicht nur Stored Procedures und Functions in PL/SQL, sondern auch in Java ausführen, denn seit der Version 8 ist eine Java-Engine Teil der Datenbank. Das gilt für alle Datenbankeditionen ab der Standard-Edition (also nicht OracleXE). Und zahlreiche Tipps der APEX Community basieren auf Java in der Datenbank.

In Oracle12c wurde die Java-Engine auf JavaSE6 aktualisiert. Erstmalig ist es auch möglich, selbstständig ein Upgrade der Datenbank-JVM durchzuführen: Wer also JavaSE7 braucht, findet in der Dokumentation (Java Developers' Guide) eine Anleitung dazu.

Mit dem Java6 hat die Datenbank die sicherlich auch "kuriose" Fähigkeit bekommen, JavaScript-Code auszuführen - denn die Java-Engine kann das ab Version 6, also kann es auch Oracle12c. Der folgende SQL-Code erzeugt die Funktion DO_JAVASCRIPT , welche den übergebenen JavaScript-Code ausführt und das Ergebnis zurückgibt. 

create or replace and compile java source named "MY_JAVASCRIPT" as 
import java.sql.SQLException;
import javax.script.ScriptEngine;
import javax.script.ScriptEngineManager;
import javax.script.ScriptException;
public class DoJavaScript {
  public static String doit(String jscode) throws Exception {
    ScriptEngineManager manager = new ScriptEngineManager();
    ScriptEngine engine = manager.getEngineByName("javascript");
    return String.valueOf(engine.eval(jscode));
  }
}
/
sho err

create or replace function do_javascript(p_scriptcode in varchar2) return varchar2
is language java name 'DoJavaScript.doit(java.lang.String) return java.lang.String';
/

Nun können Sie JavaScript direkt am SQL-Prompt ausführen. 

SQL> select do_javascript('"Oracle,APEX,12c,11g".split(",")[3]') from dual;

DO_JAVASCRIPT('"ORACLE,APEX,12C,11G".SPLIT(",")[3]')
--------------------------------------------------------------------------------
11g

SQL> select do_javascript('var d = 2; d * Math.E;') from dual;

DO_JAVASCRIPT('VARD=2;D*MATH.E;')
------------------------------------------------------------------------
5.43656365691809

1 Zeile wurde ausgewählt.

Weitere Informationen

Zurück zum Anfang des Artikels