Migration von Oracle LONG- zu LOB-Datentypen

Immer häufiger kommt es zu einer Vergrößerung des Datenvolumens innerhalb einzelner Tabellenspalten. Bisher reichte für die Speicherung großer Volumen in der Datenbank der LONG- bzw. LONG RAW-Datentyp aus. Durch ihre Beschränkung auf die Größe von zwei Gigabyte und ihre maximale Anzahl von einer LONG-Datentypspalte pro Tabelle, ist es nun häufiger notwendig, zu einem größeren Datentypformat zu migrieren. Hier bietet sich der LOB-Datentyp an, den wir in der ORDIX News 2/2006 bereits in Grundzügen vorgestellt haben. Im Folgenden wird dargestellt, wie eine Umstellung der Datentypen innerhalb der Datenbank durchgeführt werden kann. Ebenso wird auf die Bedeutung der Konvertierung für PL/SQL eingegangen.

Vorteile des LOB gegenüber dem LONG-Datentyp

Für eine Migration sprechen die folgenden Vorteile des LOB-Datentyps gegenüber dem LONG-Datentyp:

• Die Anzahl der LOB-Spalten pro Tabelle ist unbegrenzt.
• Die Größe des zu speichernden Datenvolumens ist nicht auf zwei Gigabyte, wie beim LONG-Datentyp, beschränkt.
• LOB-Datentypen können bei Replikation eingesetzt werden.

Das Einfachste: Die ALTER TABLE Anweisung

Mit der ALTER TABLE Anweisung kann eine LONG-Spalte in eine CLOB/NCLOB-Spalte geändert werden. Ebenso kann eine BLOB-Spalte aus einer LONG_RAW-Spalte generiert werden (siehe Abbildung 1).

ALTER TABLE [<schema>.]<table_name>
   MODIFY ( <long_column_name> { CLOB | BLOB | NCLOB } 
  [DEFAULT <default_value>]) [LOB_storage_clause];

CREATE TABLE mitarbeiter_lob (mitarbeiternr NUMBER,  lob_foto LONG RAW);
ALTER TABLE  mitarbeiter_lob MODIFY (lob_foto BLOB);

Dabei ist es möglich, ein DEFAULT-Constraint explizit anzugeben. Auch das Setzen der STORAGE-Klausel für die neue LOB-Spalte ist erlaubt. Bereits vorhandene Constraints auf einer LONG-Spalte werden bei der Migration übernommen.

Einschränkungen des LOB-Datentyps

Bei der Migration ist zu beachten, dass LOB-Spalten im Gegensatz zu LONG-Datentypen nicht in "geclusterten" Tabellen enthalten sein dürfen. Ebenso gilt, dass in der Auswahlliste eines UPDATE OF Trigger keine LOB-Spalten vorhanden sein dürfen. Es muss also vorher geprüft werden, ob die zu migrierende Tabelle diesen Triggertyp besitzt und in der UPDATE OF-Klausel Bezug auf LONG- bzw. LONG RAW-Spalten genommen wird.

Darüber hinaus ist zu beachten, dass bei der Migration von LONG- zu LOB-Datentypen die Indizes manuell neu aufgebaut werden müssen.

Besonders problematisch kann dies bei funk­tionsbasierten Indizes sein. Wenn eine Applika­tion damit arbeitet, muss sie nach der Migration nicht angepasst werden.

Für einen DOMAIN-Index gilt, dass dieser vor der Migration erst gelöscht werden muss! Für die entsprechende Erweiterung ist dann der Neu­eintrag vorzunehmen.

Re-Migration von LOB zu LONG

Es ist zu beachten, dass eine Konvertierung über Alter Table wieder zurück von LOB zu LONG nicht möglich ist. Ein Workaround bietet dabei allerdings die Möglichkeit, die Daten aus der LOB-Spalte über eine OCI-Applikation in eine neu generierte LONG-Spalte einzulesen. Danach kann die LOB-Spalte wieder gelöscht werden.

Verwendung von utldtree.sql

Für die Prüfung einer Anwendung (z. B. Pa­ckages), die sich auf LONG-Spalten bezieht, ist der Einsatz des Skriptes utldtree.sql sehr hilfreich. Damit wird geprüft, ob Teile der Anwendung möglicherweise neu geschrieben werden müssen. Zu finden ist das Skript im Verzeichnis $ORACLE_HOME/- rdbms/admin.

Das Skript erlaubt, alle Datenbankobjekte zu sehen, die rekursiv abhängig sind von einem anderen Datenbankobjekt. Es werden dabei nur diejenigen Datenbankobjekte angezeigt, für die auch eine Zugriffsberechtigung besteht.

Die Dokumentation ist im Skript selbst hinterlegt. Die Prüfung sollte immer vor der Migration der Tabellenspalten stattfinden. Das utldtree.sql Skript ist nur für PL/SQL erforderlich. Für SQL und OCI müssen die Bestandteile der entsprechenden Anwendung nicht geändert werden.

||, CONCAT()

Ja

Ja

= , !=, >, >=, 
<, <=, <>, ^=

Nein

Ja

IN, NOT IN

Nein

Ja

SOME, ANY, ALL

Nein

N/A

BETWEEN

Nein

Ja

LIKE [ESCAPE]

Ja

Ja

IS [NOT] NULL

Ja

Ja

INITCAP, NLS_INITCAP

CNV

CNV

LOWER, NLS_LOWER, 
UPPER, NLS_UPPER

Ja

Ja

LPAD, RPAD

Ja

Ja

TRIM, LTRIM, RTRIM

Ja

Ja

REPLACE

Ja

Ja

SOUNDEX

CNV

CNV

SUBSTR

Ja

Ja

TRANSLATE

CNV

CNV

ASCII

CNV

CNV

INSTR

Ja

Ja

LENGTH

Ja

Ja

NLSSORT

CNV

CNV

INSTRB, SUBSTRB, LENGTHB

Ja

Ja

REGEXP_LIKE

Ja

Ja

REGEXP_REPLACE

Ja

Ja

REGEXP_INSTR

Ja

Ja

REGEXP_SUBSTR

Ja

Ja

CHARTOROWID

CNV

CNV

COMPOSE

CNV

CNV

DECOMPOSE

CNV

CNV

HEXTORAW

Nein

CNV

CONVERT

Ja

CNV

TO_DATE

CNV

CNV

TO_NUMBER

CNV

CNV

TO_TIMESTAMP

Nein

CNV

TO_MULTI_BYTE

CNV

CNV

TO_SINGLE_BYTE

TO_CHAR

Ja

Ja

TO_NCHAR

Ja

Ja

TO_LOB

N/A

N/A

TO_CLOB

Ja

Ja

TO_NCLOB

Ja

Ja

COUNT

Nein

N/A

MAX, MIN

Nein

N/A

GROUPING

Nein

N/A

GREATEST, LEAST

Nein

CNV

DECODE

CNV

CNV

NVL

Ja

Ja

DUMP

Nein

N/A

VSIZE

Nein

N/A

INSTR2, SUBSTR2, 
LENGTH2, LIKE2

Nein

CNV

INSTR4, SUBSTR4, 
LENGTH4, LIKE4

Nein

CNV

INSTRC, SUBSTRC, 
LENGTHC, LIKEC

Nein

CNV

PL/SQL-Schnittstelle

Mit PL/SQL ist es möglich, die folgenden SQL-Statements abzusetzen:

• SELECT INTO-Statement mit einer CLOB Tabellenspalte in eine alphanumerische Variable, wie z. B. CHAR, LONG, oder VAR­CHAR2
• SELECT INTO-Statement mit einer BLOB Tabellenspalte in eine binäre Variable, wie z. B. eine RAW und LONG RAW Variable

Ebenso ist es möglich, die folgenden Zuweisungen vorzunehmen:

• CLOB-Variable zu einer VARCHAR2-Variablen
• BLOB-Variable zu einer RAW-Variablen
• VARCHAR2-Variable zu einer CLOB-Variablen
• RAW-Variable zu einer BLOB-Variablen

Dadurch können Zuweisungen als aktuelle Parameterwerte mit einem LOB (CLOB, BLOB) Datentyp zu einem formalen Parameter eines anderen Datentyps (VARCHAR2, RAW) einer Datenbankfunktion durchgeführt werden.

Ebenso ist der Aufruf von PL/SQL Built-In-Funktionen mit LOB möglich. Die PL/SQL Built-In-Funktionen, die den Datentyp CLOB als Eingabeparameter und Rückgabewert benutzen, sind in Abbildung 2 dargestellt. In dieser Übersicht der Funktionen bedeutet die Angabe "CNV", dass erst implizit eine Konvertierung in einen alphanumerischen Datentyp vorgenommen wird. Die Angabe "N/A" bedeutet, dass bisher keinerlei Angaben vorhanden sind. Zu beachten ist dabei, dass im SQL-Umfeld nur bis 4 KB und im PL/SQL bis zu 32 KB dargestellt werden können.

Implizite Datentypkonvertierung

Besonders zu beachten ist, dass die implizite Datentypkonvertierung auch in PL/SQL erlaubt ist:

• CLOB-Variablen nach VARCHAR2, CHAR oder LONG-Variablen und umgekehrt.
• BLOB-Variablen nach RAW und LONG RAW-Variablen und umgekehrt.

Die Konvertierung von NUMBER, ROW_ID, BINARY_INTEGER, DATE und PLS_INTEGER nach LONG ist ebenfalls erlaubt. Allerdings gibt es nicht die Möglichkeit der impliziten Konvertierung zu einem LOB-Datentyp. Hierzu ist es erforderlich, mit der Konvertierungsfunktion TO_CHAR zu arbeiten. Erst dadurch wird erreicht, dass eine Konvertierung innerhalb eines Programms fehlerfrei erfolgt. Somit ist insbesondere der Quellcode von gespeicherten Datenbankobjekten, wie Packages, Prozeduren, Funktionen und Triggern, auf implizite Konvertierungen zu überprüfen.

Explizite Datentypkonvertierung

Eine Übersicht über die explizit zu verwendenden Konvertierungsfunktionen in PL/SQL finden Sie in Abbildung 3.

TO_CLOB()

TO_NCLOB()

TO_BLOB()

TO_CHAR()

TO_NCHAR()

CAST

Die Fehlerbehandlung bei der Konvertierung gestaltet sich wie folgt: Wenn mit einer dieser Funktionen versucht wird, in den entsprechenden Zeichensatz der Datenbank zu konvertieren und der konvertierte Wert größer ist als der maximal zu speichernde Wert, so wird eine Fehlermeldung ausgegeben. Das gleiche gilt auch für die implizite Datentypkonvertierung.

Overloading

Generell darf ein Overloading existieren, solange ein Unterschied zweier Prozeduren bezüglich Anzahl, Namen, Reihenfolge und/oder Datentyp der formalen Parameter existiert.

Wir nehmen einmal an, es existierten vor der Migration zwei Prozeduren mit gleichem Namen und nur mit der Differenzierung durch LOB- und LONG-Datentyp. Nach einer Konvertierung von LONG zu LOB würden dann innerhalb des Programms zwei Prozeduren mit gleicher Anzahl, Namen, Reihenfolge und Datentyp der formalen Parameter existieren. Dies würde einen Oracle-Fehler auslösen und somit dem Konzept des Overloading widersprechen.

Verwendung von %TYPE und %ROWTYPE

Eine Besonderheit stellt die Parameterübergabe durch %TYPE und %ROWTYPE dar. Hierdurch ist es möglich, nach einer Konvertierung einer Tabellenspalte zu einem LOB-Datentyp mit den neuen Werten zu arbeiten (siehe Abbildung 4). Die schon oben erwähnten Konvertierungsrichtungen sind dabei möglich.

CREATE TABLE test_long (long_sp LONG); -- Wechsel von LONG nach LOB
	
DECLARE
  var_1 VARCHAR2(100);
  var_2 test_long.long_sp%type; -- Diese Variable wechselt von LONG nach CLOB
	
BEGIN
  SELECT * 
    INTO var_2
    FROM test_long;
	
  var_1 := var_2;  -- Wechsel von VARCHAR2 := LONG nach VARCHAR2 := CLOB
  var_2 := var_1;  -- Wechsel von LONG := VARCHAR2 nach CLOB := VARCHAR2
END;
/

Fazit

Das Entscheidende an der Migration von LONG- zu LOB-Datentypen sind die entsprechenden Vorüberlegungen, die angestellt werden müssen. Die Migration selbst ist relativ simpel, aber die Auswirkung ("Einmal LOB, immer LOB") ist relativ schwerwiegend.

Klaus Günther (info@ordix.de).

Seitenanfang

Seite drucken

Seite weiterempfehlen