Ausführlichere Fehlermeldung ausgeben, um das Problem einzugrenzen

[Wursel]

Guten Tag,

ich muss aus einer Oracle Datenbank 10g einige Abfragen generieren. Die Abfragen erfolgen mit Standard SQL (kein pl/sql oder sqlplus) über ein PHP-Skript über ODBC. Das funktioniert soweit.

Jetzt gibt es nur ein Problem damit. Die Daten sind nicht konsistent. D.h. es gibt sehr viele Zahlenwerte, die als VARCHAR2 gespeichert sind und mit CAST ... AS DECIMAL umgewandelt werden. Nun gibt es zwischendrin einige wenige Zeichen, welche keine Zahl sind. Logischerweise bricht Oracle dann ab mit "ORA-01858: ein nicht-numerisches Zeichen wurde gefunden, wo numerisches erwartet wurde".

Da die Abfrage ziemlich umfangreich ist mit sehr vielen Spalten, weiß ich nicht, wo genau das Problem auftritt - also z.B. bei welcher Spalte. Gibt es irgend eine Möglichkeit, die Sache einzugrenzen?

Danke,
Yusuf

[dbwizard]

Guten Tag,

...D.h. es gibt sehr viele Zahlenwerte, die als VARCHAR2 gespeichert sind und mit CAST ... AS DECIMAL umgewandelt werden. Nun gibt es zwischendrin einige wenige Zeichen, welche keine Zahl sind. Logischerweise bricht Oracle dann ab mit "ORA-01858: ein nicht-numerisches Zeichen wurde gefunden, wo numerisches erwartet wurde".

Hallo,

Folgendes Script gibt dir die Row's aus mit Daten, welche nicht ein ein NUMBER konvertiert werden können. Dies sollte als Ansatz reichen, evtl musst du ein Dynamisches SQL bauen, welches dir alle Spalten deiner Tabelle durchgeht.

SELECT   ID, myField, DUMP (myField) DUMP,
         LENGTH (myField) LENGTH
  FROM   my_Table
 WHERE   LENGTH (TRIM (TRANSLATE (myField, ' 0123456789', ' '))) > 0;

Gruss

[Wernfried]

WHERE   LENGTH (TRIM (TRANSLATE (myField, ' 0123456789', ' '))) > 0;

das funktioniert aber nur bei positiven ganzen Zahlen. Sobald die Zahl negativ ist oder einen Dezimalpunkt oder -komma hat oder gar in Exponentialschreibweise vorliegt liefert dir die Abfrage etwas falsches.

Mein Vorschlag wäre du schreibst einen Funktion welche dir eindeutig liefert ob ein Text in eine Zahl konvertiert werden kann:
[highlight=sql]
CREATE OR REPLACE FUNCTION IsNumeric(Expression IN VARCHAR2) RETURN NUMBER IS
num NUMBER;
BEGIN
num := CAST(Expression AS NUMBER);
RETURN 1;
EXCEPTION
WHEN INVALID_NUMBER THEN
RETURN 0;
END IsNumeric;

SELECT ID, myField, DUMP (myField) DUMP
FROM my_Table
WHERE IsNumeric(myField) = 1;


[/highlight]

[Wernfried]

Ich habe mal eine kleine Prozedur geschrieben, damit kannst du dein SELECT Statement überprüfen:

[highlight=sql]
CREATE OR REPLACE PROCEDURE Check_CAST(sqlstr VARCHAR2) IS
cur INTEGER;
describeColumns DBMS_SQL.DESC_TAB;
columnCount INTEGER;
res INTEGER;
var_mum NUMBER;
var_varchar VARCHAR2(1000);
var_date DATE;
num_cast NUMBER;
Record_ID NUMBER;
BEGIN
cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur, sqlStr, DBMS_SQL.NATIVE);
DBMS_SQL.DESCRIBE_COLUMNS(cur, columnCount, describeColumns);
FOR c IN 1..columnCount LOOP
IF describeColumns(c).col_type = DBMS_TYPES.TYPECODE_VARCHAR THEN
DBMS_SQL.DEFINE_COLUMN(cur, c, var_varchar, describeColumns(c).col_max_len);
ELSIF describeColumns(c).col_type = DBMS_TYPES.TYPECODE_NUMBER THEN
DBMS_SQL.DEFINE_COLUMN(cur, c, var_mum);
ELSIF describeColumns(c).col_type = DBMS_TYPES.TYPECODE_DATE THEN
DBMS_SQL.DEFINE_COLUMN(cur, c, var_date);
-- ELSIF ... see package DBMS_TYPES for more
END IF;
END LOOP;
res := DBMS_SQL.EXECUTE(cur);
WHILE DBMS_SQL.FETCH_ROWS(cur) > 0 LOOP
FOR c IN 1..columnCount LOOP
IF describeColumns(c).col_name = 'ID' THEN
DBMS_SQL.COLUMN_VALUE(cur, c, Record_ID);
ELSE
IF describeColumns(c).col_type = DBMS_TYPES.TYPECODE_VARCHAR THEN
DBMS_SQL.COLUMN_VALUE(cur, c, var_varchar);
BEGIN
num_cast := CAST(var_varchar AS NUMBER);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(var_varchar||' in Spalte '||describeColumns(c).col_name||', Record-ID '||Record_ID||' konnte nicht in NUMBER convertiert werden');
END;
END IF;
END IF;
END LOOP;
END LOOP;
DBMS_SQL.CLOSE_CURSOR(cur);
END;
[/highlight]

Aufrufen kannst du das ganze z. B. so:

EXEC Check_CAST('SELECT * FROM emp');

Da die Query "manuell mit Händen und Füssen" ausgeführt wird ist die Prozedur natürlich langsamer aber das Ergebnis sollte stimmen.

Gruss

[Wursel]

Wow ich bin baff! Echt vielen Dank für die Infos! Damit habe ich die Spalten ausfindig machen können und alles läuft durch. :-)

Danke schön!
Yusuf

[Wernfried]

Kein Problem, lernt man in jedem Oracle PL/SQL Grundkurs

Gruss

[dimitri]

Ich geh mal davon aus, dass in dem PL/SQL Grundkurs nach diesem Beispiel auch erwähnt wurde, dass man das in Oracle 10 natürlich nicht mehr so machen muss

select * from tabelle where regexp_like(spalte,'[-+]?[0-9]*\.?[0-9]+([eE][-+]?[0-9]+)?')

Das ganze in ein not in bzw. not exists bzw. Outer Join gepackt sollte das gleiche Ergebnis bringen - sofern ich mich nicht vertippt hab - bin im Urlaub und eine Oracle Instance ist aktuell nicht greifbar

Dim

[Wernfried]

Das mit dem Grundkurs war natürlich ein Scherz.
Im Gegensatz zu den anderen Lösungen kann man bei meiner Funktion ein beliebiges Select-Statement übergeben und muss nicht erst für jede Spalte eins bauen, das wollte Wursel vermeiden wie er im ersten Beitrag schreibt.

Beim regulären Ausdruck muss man vorher noch überprüfen welches Zeichen als Dezimaltrennzeichen verwendet wird.

Gruss

[jum]

Eine weitere einfache Möglichkeit wäre eine Funktion wie to_number_or_null(), welche die SQL-Standardfunktion to_number() erweitert, so dass keine Fehlemeldung beim Umwandeln von ungültigen Strings ausgegebn wird, sondern NULL:

CREATE OR REPLACE FUNCTION to_number_or_null (p_string IN VARCHAR2)
  RETURN NUMBER AS
BEGIN
  RETURN TO_NUMBER (p_string);
EXCEPTION
  WHEN VALUE_ERROR THEN
    RETURN TO_NUMBER (NULL);
END to_number_or_null;
/