To main content
Featured image of Oracle: CLOB und VARCHAR2 untereinander umwandeln

Oracle: CLOB und VARCHAR2 untereinander umwandeln

Veröffentlicht von Benjamin Marwell am
Database Piktogramm

Wer ein ORACLE-Datenbankfeld versehentlich als CLOB angelegt hat, aber nur ein VARCHAR2 benötigt (oder umgekehrt), kann diese einfach untereinander konvertieren. Das ist nicht im laufenden Betrieb einer Anwendung, wohl aber mit vergleichsweise geringer Downtime möglich. Die Möglichkeit wird etwas unübersichtlich in einem Beitrag von Abhijit Bashetti beschrieben - mit Code-Formatierung ist diese Anleitung hier nun auch auf Deutsch zu lesen.

Für den Artikel gehen wir von der fiktiven Spalte DESCRIPTION_TEXT  aus. Die Datenbank soll in diesem Beispiel TEST  heißen. Eventuelle Präfixe der Schemata werden zuvor mit dem use -Keyword gesetzt oder per Schemata.TEST  angegeben.

VARCHAR2 in CLOB konvertieren

-- Anlegen einer temporären Arbeitsspalte TEMP_…
ALTER TABLE TEST ADD (TEMP_DESCRIPTION_TEXT  CLOB);

-- Die Werte werden in die neue Spalte kopiert
UPDATE TEST SET TEMP_DESCRIPTION_TEXT=DESCRIPTION_TEXT;
COMMIT;

-- Die alte Spalte wird entfernt - die Datenbank
-- steht nun kurzzeitig ohne diese Spalte dar.
ALTER TABLE TEST DROP COLUMN DESCRIPTION_TEXT;

-- Die neue Arbeitsspalte wird umbenannt.
ALTER TABLE TEST RENAME COLUMN TEMP_DESCRIPTION_TEXT TO DESCRIPTION_TEXT;
COMMIT;

CLOB in VARCHAR2 umwandeln

Der Rückweg ist etwas komplizierter. Das Problem hierbei ist, dass möglicherweise Teile der bestehenden Spalte abgeschnitten werden. Hintergrund ist die auf etwa 4 Kilobinärbyte beschränkte Größe von VARCHAR2. Wer sicherstellen kann, dass er keine überlangen Feldwerte hat oder mit dem Beschneiden der Inhalte leben kann, wendet folgende SQL-Befehle an.

-- Auch hier wird zunächst ein Arbeitsfeld angelegt
ALTER TABLE TEST ADD (TEMP_DESCRIPTION_TEXT  VARCHAR2(4000 BYTE));

-- Die Kopie erfolgt mit der Funktion substr, so dass
-- überlange Feldwerte gekürzt werden.
-- substr(Feld, Länge, Start)
UPDATE TEST SET TEMP_DESCRIPTION_TEXT = DBMS_LOB.SUBSTR(DESCRIPTION_TEXT, 4000, 1);
COMMIT;

-- Die ursprüngliche Spalte wird abgeworfen
-- Die Anwendung kann bis zum nächsten Befehl
-- nicht mit diesen Werten arbeiten.
ALTER TABLE TEST DROP COLUMN DESCRIPTION_TEXT;

-- Die Arbeitsspalte wird zur ursprünglichen umbenannt.
ALTER TABLE TEST RENAME COLUMN TEMP_DESCRIPTION_TEXT TO DESCRIPTION_TEXT;
COMMIT;

Weiterführende Informationen