Immer wieder ORA-01722 (invalid number)

Read this article in English →

Ein Problem, zu dem ich immer wieder befragt werde, ist das Auftauchen eines ORA-01722 in Abfragen, bei denen der Entwickler davon ausging, die Daten richtig eingegrenzt zu haben.

Hier ein Beispielszenario:

  • In einer Tabelle wird eine VARCHAR2-Spalte durchsucht, die u.a. auch rein numerische Werte enthält.
  • Die WHERE-Klausel schränkt die durchsuchten Zeilen so ein, daß eigentlich nur Werte mit Ziffern zurückgeliefert werden sollten.
  • Dennoch kommt es zum ORA-01722: invalid number (ungültige Zahl)

Und das ist die Ursache:

  • Beim Vergleich alphanumerischer  mit numerischen Werten führt Oracle ein implizites TO_NUMBER durch.
  • Siehe hierzu die Oracle-Doku: “When comparing a character value with a numeric value, Oracle converts the character data to a numeric value.”
  • Dieses implizite TO_NUMBER findet vor der Anwendung der WHERE-Klausel statt.
  • Dadurch kommt es zum Abbruch bei Zeilen, die eigentlich gar nicht in die Ergebnismenge gehören.

Beispiel

Wir erstellen uns eine Beispieltabelle mit einer VARCHAR-Spalte, die auch alphanumerische Werte enthält. Zur Unterscheidung zwischen numerischen und alphanumerischen Werten wird zusätzlich noch eine Spalte für den Typ angelegt:

CREATE TABLE t1( content_type VARCHAR2(1), mycontent VARCHAR2(10) )
/
INSERT INTO t1 VALUES( 'N', '12345' );
INSERT INTO t1 VALUES( 'T', 'a1234' );
COMMIT
/

Es ist noch ziemlich offensichtlich, warum dieses SQL fehlschlägt:

SELECT count(*)
  FROM t1
 WHERE mycontent > 1;

FEHLER in Zeile 3:
ORA-01722: invalid number

In der WHERE-Bedingung wurde zum Vergleich der numerische Wert 1 angegeben. Dies veranlasst Oracle, beim Durchsuchen der Spalte “mycontent” ein implizites TO_NUMBER durchzuführen. Trifft dieses implizite TO_NUMBER auf einen alphanumerischen Wert, bricht das Statement ab.

Nun könnten wir versuchen, das Problem zu umgehen, indem wir über die Typ-Spalte nur die numerischen Werte heraussuchen:

SELECT count(*)
  FROM t1
 WHERE mycontent > 1
   AND content_type = 'N';

  COUNT(*)
----------
         1

Glück gehabt.

Aber Vorsicht! Es ist nicht gewährleistet, daß Oracle die Prädikate einer Abfrage immer zu unseren Gunsten auswertet. In komplexeren Joins kann es durchaus vorkommen, daß der Vergleich mycontent > 1 vor content_type = 'N' ausgeführt wird. Wir können dies simulieren, indem wir mit einem Hint Oracle dazu zwingen, die Prädikate in der geschriebenen Reihenfolge auszuwerten:

SELECT /*+ ORDERED_PREDICATES */ count(*)
  FROM t1
 WHERE mycontent > 1
   AND content_type = 'N'
;
ERROR:
ORA-01722: invalid number

Update vom 16.12.2008: In einem Artikel von J. Lewis wird erwähnt, daß speziell ab Oracle 10g dieses Szenario verstärkt auftaucht, da das “CPU Costing” des Optimizers für eine veränderte Abfolge der Prädikate sorgen kann. Jonathan empfiehlt hier eine Änderung des Datenmodells (siehe Ansatz 3 unten).

Gegenmaßnahmen

Ansatz 1: Wir filtern die Werte heraus, die nur aus Ziffern bestehen. Dafür bietet sich die Funktion translate an. Wir benötigen allerdings noch ein zusätzliches Zeichen, das wir 1:1 konvertieren, denn wenn wir einen Leerstring zur Konvertierung einsetzen, werden alle Ergebnisse NULL. Im folgenden Beispiel gehen wir davon aus, daß das Zeichen ‘#’ nicht in der durchsuchten Spalte vorkommt und verwenden daher dieses als “Dummy”:

SELECT ROWID, mycontent
  FROM t1
 WHERE translate( mycontent, '#0123456789','#' ) IS NULL;

Ansatz 1a: Ab Oracle 10g können in SQL auch Regular Expressions verwendet werden. Damit lässt sich die Filterung noch eleganter umsetzen. Beispielsweise können Vorzeichen und auch noch genau ein “.” als Dezimaltrenner berücksichtigt werden:

SELECT ROWID, mycontent
  FROM t1
 WHERE REGEXP_LIKE( mycontent, '^[\+-]?\d+(\.\d+)?$' );

Ansatz 2: Es wird ein Constraint angelegt, das alle ROWIDs der fehlerhaften Datensätze in eine separate Tabelle “exceptions” schreibt, die man mit dem von Oracle mitgelieferten Skript “utlexcpt.sql” einrichten kann.

ALTER TABLE t1 ADD
CONSTRAINT check_numeric
 CHECK ( translate( mycontent, '#0123456789','#' ) IS NULL ) ENABLE
 VALIDATE
 EXCEPTIONS INTO exceptions;

Anhand der so in der Tabelle “exceptions” gespeicherten ROWIDs können die fehlerhaften Daten nun entfernt werden.

Ansatz 3: Nach einer Datenbereinigung mit einem der o.g. Ansätze ein Redesign vornehmen, d.h. entweder den Datentyp der Spalte anpassen oder den passenden Datentyp im Code verwenden.

About these ads

23 Gedanken zu „Immer wieder ORA-01722 (invalid number)

  1. Klaus

    Hallo oraculix,

    danke für diesen Artikel – er hat mich auf die richtige Spur gebracht.

    Ich hatte in einme Crystal Report plötzlich diese Fehlermeldung.
    Des Rätsels Lösung: Es wurde ein View verwendet, der eine Tabelle aus einem andere Datentopf bereitstellt. Allerdinsg wurde der View um eine Spalte ergänzt:

    to_number(sfanr) als sfanrnr

    sfanr ist ein alphanummerisches Feld. Sinn war es, den View über sfanrnr mit einer Tabelle verknüpfen zu können, bei der das entsprechende Feld numerisch ist.
    Leider wurde das Feld sfanr (Bezeichnung in der Maske der Applikation: “Lieferantennummer”!) von einem User plötzlich dazu benutzt, zusätzlich zur Kundennummer auch noch Buchstaben einzufügen. Funktionierte auch, da alphanummerisches Feld. Aber der View konnte damit nicht aufgebaut werden und Crystal meldete ORA-01722…

    Gruß
    lefty

    Gefällt mir

    Antwort
    1. oraculix Autor

      Hallo Klaus,
      danke für die positive Rückkopplung! Sei Dir versichert, daß Du mit diesem Problem bei weitem nicht alleine bist, denn dieser Artikel hier ist der einsame Spitzenreiter in meiner Blogstatistik.
      Letzten Endes läuft es oft auf eine ungenaue Typisierung der Daten hinaus. Deswegen bevorzuge ich auch den Ansatz 3 für eine dauerhafte Beseitigung des Problems. Mit der richtigen Definition des Datentyps kann man so viele Probleme vor vornherein vermeiden – eine Binsenweisheit, sollte man meinen, und doch wird immer wieder aus Bequemlichkeit VARCHAR2 genommen, wo NUMBER(x,y) hingehört.

      Viele Grüße,
      Uwe

      Gefällt mir

      Antwort
  2. newspatch

    Hallo oraculix,

    wir haben leider hier in der DB einige Spalten, in denen sowohl Zahlen als auch Strings stehen. Bei where-Bedingungen wie

    to_number(tab1.bez) = 17.2

    bekomme ich dann immer wieder 0ra-01722. Dein Ansatz 1 mit dem translate hat mich letztlich auf die Lösung gebracht. Die where-Bedingung muss lauten:

    case when trim(translate( tab1.bez, ‘#0123456789′,’#’ )) is null then to_number(tab1.bez) else null end = 17.2

    Viele Grüße
    newspatch

    Gefällt mir

    Antwort
    1. oraculix Autor

      Hallo und danke für Deinen Beitrag, newspatch!
      Das passt zum Titel “Immer wieder…”, denn solche Konstrukte sehe ich immer wieder im Praxiseinsatz. Meine Meinung dazu ist ganz klar: Hier ist ein Fehler im Datenmodell oder auch in der Art, wie die Anwendung das Datenmodell nutzt.
      Ein Softwareentwickler lernt früh, in seinem Code die richtigen Datentypen für Konstanten und Variablen zu wählen. Seltsamerweise hört dieses Wissen dann an der Schranke zur Datenbank auf. Die Folge ist, daß eine Menge unnötigen (und potentiell fehlerhaften) Codes erstellt werden muss, um nachträglich den passenden Datentyp herauszufiltern. Ganz zu schweigen von dem unnötigen Speicherbedarf in der DB, der langsameren Verarbeitung von Strings gegenüber Zahlen, nicht oder nur umständlich DB-seitig abzubildender Datenintegrität, etc. pp.
      Daher ist für mich der o.g. Ansatz 3 stets zu bevorzugen.
      Viele Grüße, Uwe

      Gefällt mir

      Antwort
  3. flashlight

    Hi Oraculix,

    geht eigentlich auch sowas zur abprüfung ob ein Wert nur nummern enthält: LENGTH(TRIM(TRANSLATE(number_to_check, ‘0123456789’,’ ‘))) IS NULL ??

    Oder gibt es mit dem Leerzeichen Probleme?

    Gefällt mir

    Antwort
    1. Uwe M. Küchler Autor

      Ja, das geht auch – das Leerzeichen wird ja durch TRIM wieder weggekürzt. D.h., bei rein numerischen Werten kommt NULL raus, bei alphanumerischen ein Wert >= 1.
      Der Ansatz erscheint mir aufwendiger im Hinblick auf Rechenzeit, hat aber den Charme, daß er auch funktioniert, wenn der Ausgangswert bereits Leerzeichen enthält (“select to_number(‘ 123 ‘) from dual” funktioniert fehlerfrei).

      Gefällt mir

      Antwort
      1. flashlight

        Danke für die Antwort. Habe den Ansatz wieder verworfen. Die aufwendige Rechenzeit ist doch ein gutes Argument dagegen.

        Gefällt mir

  4. MacJo

    Servus Uwe,

    Ich habe eine Tabelle in der Stunden als Datentyp varchar2 abgelegt sind
    ich bekomme den genannten Fehler bei einer simplen Abfrage:

    select
    sum(to_number(stunden))
    from my_table

    ne Idee wie ich das lösen könnte?

    Gruß
    MacJo

    Gefällt mir

    Antwort
      1. MacJo

        Guten Morgen Uwe,

        ich bekomme zwar keine Fehlermeldung – aber auch keine Ergebnisse, wenn ich die Abfrage ausführe.
        Es muss an dem Spaltentyp VARCHAR2 liegen.

        :-(

        Gefällt mir

      2. Uwe M. Küchler Autor

        Dann werden wohl alle Inhalte alphanumerisch sein (z.B. “3h”, “4h”, “5h”)? Das wäre dann ein Fall für eine vorausgehende Bereinigung. Oder SUM( TO_NUMBER( )). Aber ohne Kenntnis der Inhalte kann ich Dir nicht viel mehr sagen.
        Ciao,
        Uwe

        Gefällt mir

  5. MacJo

    Der Inhalt sind Zahlen in einer VARCHAR2 Spalte
    was ich auch anstelle…ich kann VARCHAR2 einfach nicht in Number konvertieren.
    in der VARCHAR Spalte werden die Nummern als Text abgelegt. Das Zeigt das Kopieren aus dem jDeveloper ins Notepad.
    Das Ergebnis steht in “Gänsefüßchen”

    Gruß
    MacJo

    Gefällt mir

    Antwort
      1. MacJo

        ORA-00911: Ungültiges Zeichen
        00911. 00000 – “invalid character”
        *Cause: identifiers may not start with any ASCII character other than
        letters and numbers. $#_ are also allowed after the first
        character. Identifiers enclosed by doublequotes may contain
        any character other than a doublequote. Alternative quotes
        (q’#…#’) cannot use spaces, tabs, or carriage returns as
        delimiters. For all other contexts, consult the SQL Language
        Reference Manual.
        *Action:

        Gefällt mir

      2. Uwe M. Küchler Autor

        Hmmm… das lag jetzt wohl aber am cut&paste… die Anführungszeichen müssen allesamt ein einfaches Anführungszeichen sein (das über dem #).
        Davon mal ganz abgesehen: Warum ist diese Spalte überhaupt ein VARCHAR2? Der richtige Datentyp für diese Spalte wäre die beste Maßnahme in diesem Fall.

        Gefällt mir

  6. MacJo

    Ich versuche das Beispiel von Carsten Czarski(CSV-Import) für meine Bedürfnisse umzumodeln.
    Leider werden da nur VARCHAR” Spalten angewendent. Ich bräuchte da allerdings auch eine Number Spalte und weiss nicht wie ich die hinbekomme.
    Daher der Versuch mit to_number umzuwandeln.

    Ich hatte da auch schon gepostet..Carsten ist aber wohl zu beschäftigt ;-)

    http://www.cc13.com/wordpress_21/2009/06/17/apex-csv-import-fur-den-anwender/

    Gefällt mir

    Antwort
    1. Uwe M. Küchler Autor

      Servus MacJo,
      Du meinst Carsten Cerny?
      Den Code konnte ich eben nur flüchtig betrachten, meine aber, daß es angemessen wäre, in Deiner Tabelle eine NUMBER-Spalte zu verwenden und die entsprechende Stelle im INSERT-Teil des Codes mit einem TO_NUMBER() zu versehen. Wenn dann über den Import immer noch Character-Werte kommen, fällt das spätestens beim TO_NUMBER() auf die Nase, was Du dann wiederum in der EXCEPTION behandeln könntest.
      Auch wenn ich mich wiederhole: Bei der Verwendung der richtigen Datentypen kann dieses Problem nicht auftauchen!
      Viele Grüße,
      Uwe

      Gefällt mir

      Antwort
      1. MacJo

        Servus Uwe!

        Ja, Sorry Natürlich Cerny! Mit der ganzen Stöberei kommt man schon durcheinander :-P

        Ich werde mich des Insert Codes annehmen. Auf das es was wird. Danke dass Du dir die Zeit genommen hast dich mit meinem Prob zu beschäftigen und natürlich auch für die Tipps :)

        Ciao & Grüße
        M@cJo

        Gefällt mir

  7. MacJo

    Servus Uwe,

    meine Lösung des Problems sieht wie folgt aus:
    Beim Einfügen in die Tabelle werden die Stunden mit

    TO_BINARY_DOUBLE(STUNDEN)

    in eine numerische Spalte eingefügt.

    Viele Grüße
    MacJo

    Gefällt mir

    Antwort
  8. Hannes

    Hallo Zusammen,
    ich bin noch neu in der Programmierung und habe nun den Fehler ORA-01722: Ungültige zahl.
    Dies kommt ja wenn man eine Zahl in eine Varchar2 Spalte schreiben will – glaube ich – und in meinen Logg-Dateien bin ich auch schon auf die richtige Stelle gestoßen und weißt auf einen InsertFehler hin, doch auch durch das ändern in der SQL-Datenbank kommt der Fehler ständig neu.

    mein Insert:
    String insertsql = “insert into WETTERDATEN (Markt_nr, datum, bedingungen, grad_celsius, luftfeuchtigkeit, windgeschwindigkeit, picture) values ” + “(‘” + cw.getMarktId() + “‘,'” + datum.format(now) + “‘,'” + cw.getDescription() + “‘,'” + cw.getTemp() + “‘,'” + cw.getPressure() + “‘,'” + cw.getSpeed() + “‘,'” + cw.getIcon() + “‘)”;

    Bitte um Hilfe

    Gefällt mir

    Antwort
    1. Uwe M. Küchler Autor

      Hallo Hannes,
      im Falle Deines Inserts vermutest Du wohl richtig, daß hier ein String in eine Number-Spalte eingefügt wird.
      Allerdings ist Dein Beispiel unvollständig: Die Tabellendefinition fehlt, ebenso ein konkreter, fertig zusammengesetzter SQL-String. Erst dann lässt sich ein Konflikt zwischen Datentypen feststellen.
      Alternativ könntest (und vermutlich auch: solltest) Du PreparedStatements mit Binds verwenden. Das hat u.a. den Vorteil, daß die Datentypen, die von den einzelnen get-Methoden geliefert werden, nicht einfach stur in einen String umgewandelt werden.
      Viele Grüße,
      Uwe

      Gefällt mir

      Antwort
      1. Hannes

        Hallo Uwe,
        ich weiß nun dass der Fehler daran liegt, dass ich einen Wert mit Punkt trennung in die Datenbank übergebe doch es müsste ein Komma sein. Um das zu beheben fällt mir spontan .replaceAll(“.”, “,”) ein.
        Leider will dies nicht Funktionieren, da meine Methode vom Typ Double ist:

        public double getTemp() {

        return temp;
        }

        public void setTemp(double temp) {
        this.temp = Math.rint((temp – 273.15) * 10) / 10.;

        }

        gibt es andere Möglichkeiten ?

        Grüße Hannes

        Gefällt mir

      2. Uwe M. Küchler Autor

        Also, Du hast den richtigen Datentyp (Double bzw. Number), willst ihn dann in einen String wandeln und dabei auch noch die Trennzeichen mit String-Operationen anpassen? Das ist “von hinten durch die Brust ins Auge”…
        Wie gesagt: PreparedStatement ist Dein Freund, es wäre die sauberste Sache.
        Viele Grüße,
        Uwe

        Gefällt mir

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ photo

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s