Neuerungen in der Oracle Database 12.2 (Teil I) SQL- und PL/SQL-Neuerungen in Oracle 12.2

Lange Objektnamen

• Disk Group
• Pluggable Database (PDB) 
• Rollback Segment 
• Tablespace
• Tablespace Set

Case insensitive Datenbank

Für den Vergleich und Sortierung von Zeichen (engl. collation) stellt Oracle zwei Vergleichstypen zur Verfügung: binär und linguistisch. Bei dem binären Vergleich werden die Zeichen nach der numerischen Reihenfolge in der entsprechenden Zeichensatztabelle sortiert bzw. miteinander verglichen. Der linguistische Zeichenvergleich dagegen sortiert die Zeichen nach der alphabetischen Reihenfolge der entsprechenden Sprache und ist in der Regel aufwändiger als ein binärer Zeichenvergleich. Zusätzlich kann der Vergleich der Zeichen bzgl. Großund Kleinschreibung und Akzent unempflindlichkeit (Insensitivität) beeinflusst werden, z.B.:

• BINARY_CI - binary case insensitive  
• BINARY_AI - binary accent and case insensitive
• BINARY_CS - binary case and accent sensitivity

Bis Oracle 12.1 konnten die Collation-Einstellungen nur auf der Session- und SQL-Ebene eingestellt werden, z.B. über die Initialisierungsparameter NLS_COMP und NLS_SORT. Ab Oracle 12.2 kann der Vergleich von Zeichen zusätzlich auf der Schema- und Objektebene wie Tabellen, Views und Stored Procedures und sogar auf der Spaltenebene festgelegt werden (siehe Abbildung 1). Die Voraussetzung dafür ist die Aktivierung der Extended Datatypes (MAX_STRING_SIZE=EXTENDED), die allerdings nicht rückgängig gemacht werden kann und aus diesem Grund nur nach ausführlichen Tests zu empfehlen ist.

Durch die Möglichkeit der Anpassung von Collation zum Beispiel auf der Tabellenspaltenebene, kann das Verhalten einer Applikation dediziert pro Tabellenspalte bzgl. Großund Kleinschreibung und Akzent Insensitivität verändert werden, ohne den Code inkl. der SQL-Anweisungen anpassen zu müssen. Die Anpassungen von Collation auf der Tabellenebene sind für die Applikation dabei völlig transparent und eine Anpassung der Anwendung wegen der nicht-sensitiven Datenverarbeitung mit den typischen Funktionen wie UPPER oder LOWER ist hierbei nicht notwendig. Zu berücksichtigen ist allerdings, dass dabei die WHERE-Klausel der SQL-Anweisungen um den Ausdruck nlssort (,'nls_sort=') erweitert wird und dadurch in der Regel ein entsprechender funktionsbasierter Index notwendig wird.

create table tb_company (
  name varchar2(30),
  name_ci varchar2(30) collate binary_ci
);
insert into tb_company values('ORDIX','ORDIX');
insert into tb_company values('Ordix','Ordix');
insert into tb_company values('ORdiX','ORdiX');
commit;
SQL> select * from tb_company where name_ci='ordix';
NAME NAME_CI
---------- ----------
ORDIX ORDIX
Ordix Ordix
ORdiX ORdiX
SQL> select count(distinct name) cnt_name,
     2 count(distinct name_ci) cnt_name_ci
     3 from tb_company;
     CNT_NAME CNT_NAME_CI
---------- -----------
         3           1 

Abb. 1: Groß- /Kleinschreibung Insensitivität auf Tabellenspaltenebene

Fehlertoleranz

Die Konvertierungsfunktionen wie TO_NUMBER, TO_DATE oder TO_TIMESTAMP wurden um die Übergabe eines Default-Wertes erweitert, der im Fehlerfall statt einer Fehlermeldung zurückgeliefert wird. Damit kann eine Verarbeitung trotz eines Konvertierungsfehlers fortgesetzt werden, ohne dass eine Fehlermeldung geworfen und die Abarbeitung eines SQL-Statements abgebrochen wird (siehe Abbildung 2). Individuelle Lösungen, die in der Praxis häufig zu finden sind, werden damit überflüssig. Soll lediglich nur überprüft werden, ob eine Datentypkonvertierung erfolgreich sein wird, kann die neue Funktion VALIDATE_CONVERSION verwendet werden. Diese Funktion liefert den Wert 1 falls eine Konvertierung möglich ist und den Wert 0 falls eine Konvertierung fehlschlägt (siehe Abbildung 3).

Auch die Funktion LISTAGG, mit der eine mit Trennzeichen getrennte Liste von Werten einer Spalte erzeugt werden kann, wurde um einen Default-Wert mit einer Überlaufbehandlung erweitert. Der Default-Wert tritt bei dieser Funktion dann in Kraft, wenn die maximale Länge von 4.000 Bytes überschritten wird. Zusätzlich zu dem Default- Wert kann auch noch ein Zähler ausgegeben werden, der die Anzahl überzähliger Zeichen beinhaltet. Die überschüssigen Zeichen können allerdings nicht ausgegeben und auch nicht gespeichert werden. Da ein Werteüberlauf bei der Funktion LISTAGG standardmäßig zu einem Fehler und somit zu einem Abbruch der Verarbeitung führt, ist die Erweiterung um den Default-Wert mit der Überlaufbehandlung sehr hilfreich, weil man damit die potenziellen Fehlerquellen in der Verarbeitung eliminieren kann.

with data (tag) as (
   select '10.01.2017' from dual union all
   select '30.02.2017' from dual union all
   select 'text' from dual
)  select tag, to_date(
      tag default '01.01.2017' on conversion error,
      'dd.mm.yyyy') as tag_def
from data;
TAG        TAG_DEF
---------- ----------
10.01.2017 10.01.2017
30.02.2017 01.01.2017
text 01.01.2017
3 Zeilen ausgewählt. 

Abb. 2: Beispiel mit Default bei Konvertierungsfunktionen

with data (tag) as (
   select '10.01.2017' from dual union all
   select '30.02.2017' from dual union all
   select 'text' from dual
)  select tag,
   validate_conversion(tag as date, 'dd.mm.yyyy') as conv_ok
from data;
TAG     CONV_OK
---------- ----------
10.01.2017    1
30.02.2017    0
text          0  

Abb. 3: Beispiel mit der Funktion VALIDATE_CONVERSION

SQL*Plus-Befehlshistorie

Das SQL*Plus-Programm wurde um die langersehnte Befehlshistorie erweitert. Befehle die innerhalb einer Session eingegeben und ausgeführt wurden, werden innerhalb der Session zwischengespeichert und können über die Angabe der Nummer aus der Befehlshistorie einfach erneut ausgeführt werden (siehe Abbildung 4). 

Standardmäßig ist die Befehlshistorie allerdings nicht aktiv. Erst nach der Angabe von SET HIST[ORY] ON wird die Befehlshistorie für 100 (maximal 100.000) Befehle aktiv geschaltet. Da jede SQL*Plus Session eine eigene Befehlshistorie hat, können Befehle von einer vorangegangenen SQL*Plus Session nicht verwendet werden.

SQL> -- Befehlshistorie für max. 100 Befehle aktivieren...
SQL> set hist on
SQL> -- SQL-Anweisung ausfuehren...
SQL> select 'test1' from dual;
'TEST
-----
test1
SQL> -- Inhalt der Befehlshistorie anzeigen...
SQL> hist
1 select 'test1' from dual;
SQL> -- 1. Befehl aus der Befehlshistorie ausführen...
SQL> hist 1 run
'TEST
-----
test1 

Abb. 4: Beispiel mit SQL*Plus-Befehlshistorie

Pragma Deprecated

-- Warnungen aktivieren...
alter session set plsql_warnings='enable:all';
-- Stored Procedure mit pragma deprecate anlegen...
create or replace procedure p_test is
pragma deprecate( p_test,
'p_test is deprecated please use p_test_2 instead.' );
begin
dbms_output.put_line('p_test');
end p_test;
/
show err
LINE/COL ERROR
-------- -----------------------------------------------------
3/3 PLW-06019: Entity P_TEST ist veraltet
create or replace procedure p_test2 is
begin
p_test;
end p_test2;
/
show err
LINE/COL ERROR
-------- ---------------------------------------------------
3/3 PLW-06020: Referenz auf eine veraltete Entity: P_TEST
deklariert in Einheit P_TEST[1,11]. p_test is deprecated
please use p_test_2 instead. 

Statische Ausdrücke in Variablendeklarationen

Auch im Bereich von Variablendeklarationen in einem PL/SQL-Programm hat Oracle eine Verbesserung eingeführt. Im Wertebereich von Variablen-, Konstanten und SUBTYPE-Deklarationen können ab diesem Release statische Ausdrücke verwendet werden. Ein statischer Ausdruck ist ein Ausdruck, der zum Kompilierzeitpunkt bestimmt sein muss, wie es z.B. bei einer Konstanten oder einem Ausdruck wie „1+1" der Fall ist (siehe Abbildung 6). Variablen und Funktionsaufrufe sind keine statischen Ausdrücke und können im Wertebereich von Variablendeklarationen nach wie vor nicht verwendet werden. Mit Hilfe dieser Erweiterung können festkodierte Längen-angaben bei Variablen und Konstanten vermieden werden.

declare
   c1 constant varchar2(2) := 'AB';
   c2 constant number( length(c1) + 2 ) := 1234;
   v_proc_name varchar2( (ORA_MAX_NAME_LEN*2)+1 );
begin
   v_proc_name := 'ora00.pro_mitarbeiter';
end;
/
PL/SQL-Prozedur erfolgreich abgeschlossen. 

Abb. 6: Beispiel mit statischen Ausdrücken in Variablendeklarationen

Feingranulare PL/SQL-ACCESSIBLE-BY-Klausel

Die ACCESSIBLE-BY-Klausel für PL/SQL-Objekte ist mit der Oracle 12.1 eingeführt worden. Mit der Klausel ACCESSIBLE BY kann festgelegt werden, dass ein PL/SQL-Objekt wie Prozedur, Funktion oder Package, nur von bestimmten PL/SQL-Objekten aufgerufen werden kann. Ein typischer Anwendungsfall sind Hilfsfunktionen, die nur von ausgewählten PL/SQL-Objekten aufgerufen werden können oder sogar dürfen, mit dem Ziel eventuelle Seiteneffekte zu verhindern. 

Bis Oracle 12.1 war die Zugriffsbeschränkung für PL/SQLPackages allerdings nur auf der Package-Ebene möglich. Eine Zugriffsbeschränkung für einzelne Prozeduren oder Funktionen eines PL/SQL-Package war nicht möglich. Mit Oracle 12.2 ist die Einschränkung nun entfallen (siehe Abbildung 7).

create or replace package pck_ma as
   function f_create(p_name varchar2) return number
     accessible by (p_main);
   procedure p_delete(p_nr number)
     accessible by (p_main_priv);
end pck_ma;
/
Package wurde erstellt. 

Abb. 7: Beispiel mit der ACCESSIBLE-BY-Klausel

Fazit