UPDATE mit REPLACE - Spalte zu schmal

[StephanH]

Hallo zusammen,

ich habe ein Problem, das eigentlich keins sein sollte.

In einer Tabelle möchte ich eine Spalte updaten, wobei nur ein Teil der im Feld enthaltenen Daten verändert werden soll.

Das Problem, auf das ich stoße, ist die Spaltenbreite.

Hier erstmal mein Script:

update tabelle
set spalte=replace(spalte,'A-DORF','B-STADT-A-DORF')
where length(spalte)<=69
and spalte like '%12345 A-DORF%'

Das Script bricht mit einem Fehler ab: Value too large for Column- act: 82, max: 80

So weit, so gut.

Mir scheint, daß Oracle versucht, den REPLACE in allen Feldern aus SPALTE zu ersetzen, ohne dabei die vorgegebene Länge zu berücksichtigen.

Ein

select spalte from tabelle
where length(spalte)>69
and spalte like '%12345 A-DORF%'

gibt keinen Datensatz aus. Die Spaltenbreite beträgt 80 Zeichen und ich habe vorher alle Datenfelder auf max. 69 getrimmt, weshalb das Script eigentlich fehlerfrei laufen sollte.
Da dies nicht der Fall ist, mache ich entweder einen Denkfehler, oder Oracle berücksichtigt nicht die Bedingungen der where-Anweisungen.

Hat jemand ne Idee?
Dank im Voraus.

Gruß,

Stephan

[kuemmelchen]

Schreib folgende SQL-Anweisung zum Testen:

SELECT replace(spalte,'A-DORF','B-STADT-A-DORF') AS spalteninhalt,
length(replace(spalte,'A-DORF','B-STADT-A-DORF') ) AS spaltenbreite
FROM tabelle;

Ich vermute mal, dass in einem oder mehreren Datensätzen nicht nur einmal sondern mehrmals ersetzt wurde.

kuemmelchen

[StephanH]

Hallo Kuemmelchen,

habe mit deinem Script getestet, aber es lief nicht nur einwandfrei, es war kein Datensatz dabei, der länger als 80 Zeichen ist, trotz korrekter Ersetzung.
Eine mehrmalige Ersetzung habe ich nicht feststellen können.

*kopfkratz*

Stephan

[TomT]

Du musst unterschieden zwischen der Länge in Bytes und der tatsächlichen Länge eines Zeichens.
Wenn ihr einen Mehrbyte Zeichensatz verwendet, kann es Zeichen geben, die mehr als ein Byte belegen.
LENGTH liefert die Anzahl Zeichen, wenn Du die Anzahl Bytes haben möchstest kannst Du LENGTHB verwenden.
Dann kommt es noch darauf an, wie ihr die Spalte definiert habt. VARCHAR2(80) CHAR oder VARCHAR2(80) BYTES. Ersteres erlaubt dir bis zu 80 Zeichen abzuspeichern, letzteres erlaubt dir bis zu 80 Bytes abzuspeichern.

Evtl. liegt dort dein Problem.

[StephanH]

Hi TomT,

danke für den Hinweis. Habe nachgesehen, es sind VARCHAR2(80) CHAR.

Prüft denn Oracle eigentlich nur die nach WHERE selektierten Datensätze oder wird die update/set-Maske über alle ausgelesenen Datensätze gesetzt?

Wenn kuemmelchen meint, es fänden Mehrfachersetzungen statt, so verschließt sich mir dies, weil es ja zu keiner abschließenden Ausführung kommt und eine Ergebniskontrolle somit nicht stattfinden kann. Das o.a. Testscript (von kuemmelchen) läuft jedenfalls einwandfrei durch.

[TomT]

Hi,

der Update wird nur über die Daten gemacht, die über die WHERE Klausel ermittelt werden.
Du kannst LOG ERRORS verwenden um dir die abgelehnten Datensätze wegzuloggen:

begin
 dbms_errlog.DBMS_ERRLOG.CREATE_ERROR_LOG('deinetabelle');
end;
/

UPDATE DEINETABELLE
   SET SPALTE=REPLACE(SPALTE,'A-DORF','B-STADT-A-DORF')
 WHERE LENGTH(SPALTE)<=69
   AND SPALTE LIKE '%12345 A-DORF%'
LOG ERRORS INTO ERR$_DEINETABELLE REJECT LIMIT UNLIMITED;

In der Tabelle ERR$_DEINETABELLE findest dann die abgelehnten Sätze.

Ein Tipp: Kommt der Begriff 'A-DORF' vielleicht mehr als einmal im String vor?

[StephanH]

A-DORF kann doppelt dann vorkommen, wenn es z.B. die A-DORFERSTR gibt.

Da die PLZ gleich bleibt, sollte mann dann vielleicht im REPLACE

SET SPALTE=REPLACE(SPALTE,'12345 A-DORF','12345 B-STADT-A-DORF')

verwenden? Die PLZ kommt garantiert nur einmal in der Zeichenkette vor.

[StephanH]

Problem gelöst!

Es war so, wie TomT und kuemmelchen vermutet hatten. Die Ersetzungen passierten mehrfach.

Nachdem ich, wie oben vermutet, die Postleitzahl mit dazugegeben habe, wurden die 3396 Datensätze einwandfrei 'replaced' :-)

Vielen Dank für eure Unterstützung - manchmal (zu oft) sieht man den Wald vor lauter Bäumen nicht...