PL/SQL: Über die Bedeutung von Best Practices
Dieser Artikel ist ein weiterer einer kleinen Reihe von Fundstellen, die mich bei der Lektüre von Fachliteratur zu Widerspruch gereizt haben. Mir geht es natürlich nicht darum, mit dem Finger auf andere Autoren zu zeigen (ein Teil dieser Reihe behandelt auch eine Fundstelle in meinem PL/SQL-Buch), sondern stellen den Anker dar, an dem ich ein Thema erläutern möchte, von dem ich glaube, dass es für viele Leser interessant sein könnte. Hier nun eine Fundstelle in einem Computercode und die Frage: Welchen Vorteil bietet die Beachtung von Best Practices?
Die Fundstelle
Diesmal ist die Fundstelle ein Stück PL/SQL-Code, das ein sehr merkwürdiges Verhalten zeigte. Der Code lief Monate lang einwandfrei und auf einmal warf er einen eher suspekt wirkenden Fehler. Hier ist der – im Grundsatz triviale – Code:
Listing 1: Testprozedur
create or replace procedure do_something as cursor my_cur is select * from some_view@dblink v join some_table t on v.id = t.id; begin for rec in my_cur loop --do_something; null; end loop; end; /
So einen Code wird man doch wohl noch schreiben dürfen, ohne Fehler befürchten zu müssen, oder? Dennoch kam nach geraumer Zeit folgende Fehlermeldung, die sich anschließend immer wieder zeigte:
ORA-06502: PL/SQL: numerischer oder Wertefehler: Bulk Bind: Truncated Bind ORA-06512: in "DOAG.DO_SOMETHING", Zeile 9
Nanu?
Das Problem
Zugegeben: Dieser Fehler gehört definitiv in die Kategorie "Wer weiß denn so etwas?", er ist aber auch nicht aus der Welt und kann so oder sehr ähnlich immer wieder einmal auftreten. Zunächst einmal ist der Code ganz normal, das Einzige, was auffällt, ist der Datenbanklink auf eine andere Datenbank in der Deklaration des Cursors. Da andererseits das Öffnen des Cursors bereits den Fehler wirft, muss das Problem irgendwo in dieser Ecke liegen. Auch die Fehlermeldung Bulk Bind weist hierauf hin, denn seit Version 11g der Datenbank wird das Öffnen eines Cursors in einer Cursor-For-Schleife durch eine Bulkoperation optimiert (wofür der ungewollte Fehler also gleich einen schönen Beleg liefert).
Erste Anlaufstelle: Google. Dort erfährt man, dass dieser Fehler auftritt, wenn ein type mismatch vorliegt, wenn also zum Beispiel innerhalb einer Bulk-Operation eine Variable mit einer Länge von 10 mit 11 Zeichen beladen wird. Schön. Aber das kann hier ja wohl ganz offensichtlich nicht das Problem sein, denn der Record, der eine Zeile des Cursors aufnimmt, wird implizit als my_cur%ROWTYPE definiert, entspricht also exakt der Definition des Cursors. Woher die Deklaration des Typs kommt, ist auch schnell geklärt: Aus dem Data Dictionary nämlich, den Tabelleneigenschaften der zu Grunde liegenden Tabellen.
Im "richtigen" Code habe ich, um den Fehler einzugrenzen, nun ermittelt, ob es irgendwelche Probleme mit den Daten des Cursors gibt. Allerdings konnte ich hier schnell Entwarnung geben: Die select-Anweisung des Cursors funktioniert, für sich ausgeführt, ohne Probleme. Nur im Zusammenhang mit der Prozedur wird konstant der oben gezeigte Fehler geworfen. Die nächste Aktion, um den Fehler einzugrenzen, bestand darin, die Spalte zu finden, die den Fehler wirft. Es ist einfach, dies zu tun, man muss nur die Cursor-Deklaration so ändern, dass der Fehler kontrolliert geworfen oder verhindert werden kann. Im lebenden Beispiel fand sich schließlich eine Zeile, die einen Kommentar zu einem Dokument enthielt, als Übeltäter.
Doch was war so speziell an dieser Spalte? Falls Sie selbst einmal einen solchen Fehler hatten, könnte es an der Verwendung von LONG-Datentypen oder so etwas liegen, doch in diesem Datenmodell wird LONG ausschließlich noch von Oracle selbst verwendet, nicht aber von den Datenbankmodellierern. Die Spalte war schlicht vom Typ varchar2(40 byte).
varchar2(40 byte)? Warum Byte? Das mag zunächst nicht so scheinen, aber diese Frage bringt uns auf die richtige Fährte. Warum Byte? Alternativ hätte dort stehen können: varchar2(40 char).
Hintergrund: Unterschied zwischen Byte- und Char-Semantik
Bei Zeichensatzkodierungen mit variabler Länge pro Zeichen (hier ist eigentlich immer UTF-8 gemeint) besteht durchaus ein Unterschied zwischen der Angabe Byte oder Char, bei Zeichensatzkodierungen aus dem ISO-8859-Umfeld jedoch nicht, weil dort jeder Buchstabe genau 1 Byte lang ist. Der Unterschied besteht darin, dass im Fall einer UTF-8-basierten Datenbank die Datenbank für eine Spalte vom Typ varchar2(40 char) 160 Byte Speicherplatz vereinbart, weil ein Unicode-Zeichen maximal 4 Byte lang sein kann. Im Fall der Angabe varchar2(40 byte) ist es in einer Unicode-Datenbank daher möglich, dass lediglich 10 Buchstaben in diese Spalte passen, zum Beispiel, wenn es sich um zentralchinesische Zeichen handelt.
Wenn Sie bei der Anlage der Tabelle nicht definieren, ob Sie 40 Byte oder 40 Char speichern möchten, wird die Entscheidung von einem Initialisierungsparameter mit dem Namen NLS_LENGTH_SEMANTICS abhängig gemacht. Der wiederum steht standardmäßig auf dem Wert BYTE, wenn er nicht durch den Administrator geändert wurde. Da man nicht gern von Standardwerten abhängig ist, hat es sich daher als Best Practice etabliert, die Angabe, ob Byte- oder Char-Semantik zur Speicherung verwendet werden soll, bei der Tabellendeklaration explizit anzugeben. Dann ist beides auch gemischt möglich, in jedem Fall ist es jedoch eindeutig geregelt.
Woher kommt der Fehler?
Und nun keimt der Verdacht für den Fehler: Wir haben einen Datenbanklink, der auf eine fremde Datenbank zeigt. Lokal ist unsere Datenbank in UTF-8 kodiert, aber ist das auch so für die fremde Datenbank? Nein, die fremde Datenbank ist in WIN-1252 kodiert, was kompatibel zu ISO-8859-1 ist und also eine 1-Byte-Zeichensatzkodierung darstellt.
Nun wird das Problem klar:
- Weil in der entfernten Datenbank als Spaltentyp varchar2(40 byte) eingetragen wurde,
- weil nach langer Zeit zum ersten Mal ein langer Kommentar mit 40 Zeichen eingetragen wurde und
- weil in diesem Kommentar auch noch ein Umlaut enthalten war (der 2 Byte Speicherplatz benötigt),
benötigt diese Zeile der Tabelle in der entfernten Datenbank 40 Byte Speicherplatz, in der lokalen jedoch 41 Byte. Da andererseits der Cursor die Spaltenbreite aus dem Data Dictionary der entfernten Datenbank entnommen hat, hat das lokale Kommentar-Attribut des Records die Breite 40 Byte und dort passt die übernommene Zeichenkette nicht hinein: Exception -06052.
Lösungsansätze
Wie gesagt: Wer weiß denn so was? Bevor wir aber sozusagen zur Moral der Geschichte kommen, hier zunächst einige Lösungsansätze:
Ich habe auf der entfernten Datenbank in 1-Byte-Kodierung eine einfache Tabelle angelegt, um das Problem zu demonstrieren:
-- In entfernter Datenbank SQL> create table encoding_test( 2 id number, 3 text varchar2(10 byte), 4 constraint pk_encoding_test primary key(id) 5 ) organization index; SQL> table ENCODING_TEST erstellt. SQL> insert into encoding_test(id, text) 2 select 1, 'Zehn Ohne:' from dual union all 3 select 2, 'Zehn mit Ö' from dual; 2 Zeilen eingefügt. SQL> commit; festgeschrieben.
Die Prozedur in der Multibyte-kodierten, lokalen Datenbank aus Listing 1 wirft nun für Parameter 1 keinen, für Parameter 2 jedoch den Fehler -06052:
-- Erster Test: Test fuer Zeile 1 SQL> call encoding_test(1); encoding_test 1) erfolgreich. -- Erster Test: Test fuer Zeile 1 SQL> call encoding_test(2); Fehler beim Start in Zeile : 16 in Befehl - call encoding_test(2) Fehlerbericht - SQL-Fehler: ORA-06502: PL/SQL: numerischer oder Wertefehler: Bulk Bind: Truncated Bind ORA-06512: in "DOAG.ENCODING_TEST", Zeile 9 06502. 00000 - "PL/SQL: numeric or value error%s" *Cause: *Action:
Um dieses Problem zu lösen, fallen mir zwei Wege ein. Zum einen wäre es möglich gewesen, die Prozedur anzuweisen, die Daten in die neue Zeichensatzkodierung zu konvertieren. Passiert dies innerhalb der select-Anweisung des Cursors, wird damit automatisch auch die Breite des Record-Attributs angepasst. Dies ermöglicht die Funktion CONVERT, aufgerufen wie im folgenden Beispiel:
Listing 2: Geänderte Prozedur
create or replace procedure encoding_test( p_id in number) as cursor encoding_cur(p_id in number) is select id, convert(text, 'AL32UTF8') from encoding_test@dblink where id = p_id; begin for r in encoding_cur(p_id) loop null; end loop; end; /
Als Name der Zeichensatzkodierung, in die übersetzt werden soll, muss der interne Oracle-Bezeichner für die Zeichensatzkodierungen gewählt werden, hier AL32UTF8.
Der Fehler wird nun nicht mehr ausgelöst, die Prozedur funktioniert:
-- Erfolgreicher Test fuer Zeile 2 SQL> call encoding_test(2); encoding_test 2) erfolgreich.
Natürlich wäre es auch möglich, einen ganz einfachen anderen Weg zu gehen: Wenn Sie nämlich die Spalte der entfernten Tabelle als varchar2(40 char) definieren, ist der Umweg über die CONVERT-Funktion nicht mehr erforderlich, der Code funktioniert einfach (natürlich wurde wieder die Testprozedur aus Listing 1 zum Testen verwendet):
-- Zweite Moeglichkeit: Implementierung einer Best Practice SQL> drop table encoding_test; table ENCODING_TEST gelöscht. SQL> create table encoding_test( 2 id number, 3 text varchar2(10 char), 4 constraint pk_encoding_test primary key(id) 5) organization index; table ENCODING_TEST erstellt. SQL> insert into encoding_test(id, text) 2 select 1, 'Zehn Ohne:' from dual union all 3 select 2, 'Zehn mit Ö' from dual; 2 Zeilen eingefügt. SQL> commit; festgeschrieben. -- Erfolgreicher Test fuer Zeile 2 SQL> call encoding_test(2); encoding_test 2) erfolgreich.
Zusammenfassung
Es gibt viele Beispiele für eine eher entmutigende Tatsache: Die möglichen Fehlerursachen sind vielfältig und zum Teil nur schwer zu testen. Natürlich hätten entsprechend sorgfältig erstellte Testdaten dieses Problem aufdecken können, aber eigentlich nur, wenn Sie den Fehler bereits kennen. Wer würde sonst daran denken, in Testdaten die maximale Breite einer Spalte nicht nur auszunutzen, sondern auch noch Umlaute zu integrieren? Natürlich ist es auch immer ein guter Rat, sich weiter und weiter mit der Datenbank auseinanderzusetzen, um besser darin zu werden, die Arbeitsweise und mögliche Fehlerursachen frühzeitig zu erkennen. Doch eigentlich sind dies alles wohlfeile Ermahnungen.
Wirklich hilfreich sind allerdings Best Practices. Denn wieder einmal zeigt sich, dass hier ein Problem auftaucht, das nur durch einen Verstoß gegen Best Practices möglich wurde: Sie verheimlichen der Datenbank, welche Daten Sie in der Datenbank zu speichern gedenken. Natürlich ist die Frage, worin der Unterschied zwischen der Byte- und Char-Semantik von Datenbankspalten in einer Single-Byte-Zeichensatzkodierung liegen soll, doch ist spätestens seit der Unterstützung von Unicode innerhalb der Datenbank (und der Zusammenarbeit mit Java- oder XML-Code, die normalerweise immer Unicode-basiert sind) die Anforderung hinzugekommen, stets genau zu definieren, ob Zeichen oder Bytemengen gemeint sind, wenn eine Tabellenspalte definiert wird.
Da wir in der Datenbankmodellierung nicht von Standardeinstellungen abhängig sein möchten, gehört also spätestens seit Version 9 der Datenbank (also seit dem Jahrtausendwechsel) die Angabe CHAR oder BYTE zu einer Tabellenspaltendeklaration einfach dazu. Meine Best Practice lautet übrigens stets CHAR, hiervon weiche ich allerdings ab, wenn ich sicher sein kann, dass nur Byte gespeichert werden müssen (zum Beispiel bei Schemabezeichnern, die maximal 30 Byte lang sind oder bei definierten Werten wie Y|N, die durch check-Constraints geschützt werden). Dann verwende ich BYTE um anzuzeigen, dass ich mir diesbezüglich sicher bin. Ausnahmen mögen auch zulässig sein, wenn Sie aus Gründen der Speicherplatzoptimierung oder der kontrollierten Umgebung sicher sind, es ausschließlich mit 1-Byte-Semantik zu tun zu haben. Aber auch dann macht die Definition als varchar2(40 char) die Dinge nicht komplexer, wohl aber zukunftssicherer.
Sollten Sie darüber hinaus einen Anwendungsfall haben, den ich nicht berücksichtigt habe und der die Verwendung der Byte-Semantik erfordert, gilt die übliche salvatorische Klausel, aber dann haben Sie sich so eingehend mit diesem Problem beschäftigt, dass Ihnen hiermit ohnehin kein Fehler unterläuft...
Dies ist Teil einer Artikelserie zu "Fundstellen in der Fachliteratur":
Trigger or not to Trigger
Wann PL/SQL nicht verwendet werden sollte
Packages und ihre Initialisierung
Oracle Database: Deterministische Funktionen in PL/SQL
Publikationen
- Oracle SQL: Das umfassende Handbuch
- Oracle PL/SQL: Das umfassende Handbuch
- Oracle APEX: Das umfassende Handbuch für Entwickler
