Błąd SQL „ORA-01722: nieprawidłowy numer”

Bardzo łatwy dla kogoś, Poniższa wkładka daje mi plik

ORA-01722: nieprawidłowy numer

czemu?

INSERT INTO CUSTOMER VALUES (1,'MALADY','Claire','27 Smith St Caulfield','0419 853 694');
INSERT INTO CUSTOMER VALUES (2,'GIBSON','Jake','27 Smith St Caulfield','0415 713 598');
INSERT INTO CUSTOMER VALUES (3,'LUU','Barry','5  Jones St Malvern','0413 591 341');
INSERT INTO CUSTOMER VALUES (4,'JONES','Michael','7  Smith St Caulfield','0419 853 694');
INSERT INTO CUSTOMER VALUES (5,'MALADY','Betty','27 Smith St Knox','0418 418 347');

Podczas próby konwersji ciągu znaków na liczbę występuje błąd ORA-01722, którego nie można przekształcić w liczbę.

Wygląda na to, że nie widząc definicji tabeli, próbujesz przekonwertować sekwencję liczb na końcu listy wartości na liczbę, a spacje, które ją ograniczają, generują ten błąd. Ale na podstawie informacji, które nam przekazałeś, może to mieć miejsce na dowolnej dziedzinie (innej niż pierwsza).

Załóżmy, że numer telefonu jest zdefiniowany w taki sposób, NUMBERże spacji nie można zamienić na liczbę:

create table telephone_number (tel_number number);
insert into telephone_number values ('0419 853 694');

Powyższe daje

ORA-01722: nieprawidłowy numer

Oto jeden sposób rozwiązania tego problemu. Usuń znaki nienumeryczne, a następnie przerzuć je jako liczbę.

cast(regexp_replace('0419 853 694', '[^0-9]+', '') as number)

Ponieważ ten błąd pojawia się, gdy próbujesz wstawić wartość nieliczbową do kolumny numerycznej w db, wydaje się, że ostatnie pole może być numeryczne i próbujesz wysłać je jako ciąg w bazie danych. sprawdź ostatnią wartość.

Cóż, może to być również:

SELECT t.col1, t.col2, ('test' + t.col3) as test_col3 
FROM table t;

gdzie do konkatenacji w oracle jest używany operator ||nie +.

W takim przypadku otrzymasz: ORA-01722: invalid number ...

To dlatego, że:

Wykonałeś instrukcję SQL, która próbowała przekonwertować ciąg na liczbę, ale zakończyła się niepowodzeniem.

Jak wyjaśniono w:

• Oracle / PLSQL: Błąd ORA-01722.

Aby rozwiązać ten błąd:

W operacjach arytmetycznych można używać tylko pól numerycznych lub pól znakowych, które zawierają wartości liczbowe. Upewnij się, że wszystkie wyrażenia są obliczane na liczby.

Oracle wykonuje automatyczną konwersję String2number dla wartości kolumn typu String ! Jednak w przypadku porównań tekstowych w języku SQL dane wejściowe muszą być jawnie oddzielone jako ciąg znaków: przeciwna konwersja number2String nie jest wykonywana automatycznie, a nie na poziomie zapytania SQL.

Miałem takie zapytanie:

select max(acc_num) from ACCOUNTS where acc_num between 1001000 and 1001999;

Ten stanowił problem: Error: ORA-01722: invalid number

Właśnie otoczyłem wartości „numeryczne” , aby uczynić je „Ciągami” , po prostu ustawiając je jawnie jako rozgraniczone :

select max(acc_num) from ACCOUNTS where acc_num between '1001000' and '1001999';

... i voilà: Zwraca oczekiwany wynik.

edycja: I rzeczywiście: kolumna acc_numw mojej tabeli jest zdefiniowana jako String. Chociaż nie było to liczbowe, invalid numberzostało zgłoszone. A wyraźne rozgraniczenie numerów ciągów rozwiązało problem.

Z drugiej strony Oracle może traktować ciągi znaków jako liczby. Zatem operacje / funkcje numeryczne można zastosować na łańcuchach, a te zapytania działają:

wybierz max (string_column) z TABLE;

wybierz string_column z TABLE, gdzie string_column między '2' a 'z';

wybierz string_column z TABLE, gdzie string_column > '1';

wybierz string_column z TABLE, gdzie string_column <= 'b';

W moim przypadku błąd konwersji wystąpił w indeksie funkcjonalnym , który utworzyłem dla tabeli.

Wstawiane dane były prawidłowe. Zajęło mi trochę czasu, zanim zorientowałem się, że rzeczywisty błąd pochodzi z błędnego indeksu.

Byłoby miło, gdyby Oracle mógł podać dokładniejszy komunikat o błędzie w tym przypadku.

Jeśli zrobisz insert into...select * from... polecenie, łatwo jest również uzyskać błąd „Nieprawidłowy numer”.

Załóżmy, że masz tabelę o nazwie, FUND_ACCOUNTktóra ma dwie kolumny:

AID_YEAR  char(4)
OFFICE_ID char(5)

Powiedzmy, że chcesz zmodyfikować OFFICE_ID, aby był numeryczny, ale w tabeli istnieją już wiersze, a co gorsza, niektóre z tych wierszy mają wartość OFFICE_ID równą „” (puste). W Oracle nie można modyfikować typu danych kolumny, jeśli tabela zawiera dane, a konwersja znaku „” na 0. wymaga trochę sztuczek. Oto jak to zrobić:

1. Utwórz zduplikowaną tabelę: CREATE TABLE FUND_ACCOUNT2 AS SELECT * FROM FUND_ACCOUNT;
2. Usuń wszystkie wiersze z oryginalnej tabeli: DELETE FROM FUND_ACCOUNT;

3. Gdy nie ma żadnych danych w oryginalnej tabeli, zmień typ danych w jej kolumnie OFFICE_ID: ALTER TABLE FUND_ACCOUNT MODIFY (OFFICE_ID number);

4. Ale tutaj jest trudna część. Ponieważ niektóre wiersze zawierają puste wartości OFFICE_ID, jeśli zrobisz coś prostego INSERT INTO FUND_ACCOUNT SELECT * FROM FUND_ACCOUNT2, pojawi się błąd „ORA-01722 Invalid Number”. Aby przekonwertować „” (puste) OFFICE_ID na 0, instrukcja wstawiania będzie musiała wyglądać następująco:

INSERT INTO FUND_ACCOUNT (AID_YEAR, OFFICE_ID) SELECT AID_YEAR, decode(OFFICE_ID,' ',0,OFFICE_ID) FROM FUND_ACCOUNT2;

Mnie też się to przydarzyło, ale problem był w rzeczywistości inny: kodowanie plików .

Plik był poprawny, ale kodowanie pliku było nieprawidłowe. Został wygenerowany przez narzędzie do eksportu programu SQL Server i zapisałem go jako Unicode.

Sam plik wyglądał dobrze w edytorze tekstu, ale kiedy otworzyłem *.badplik, który program ładujący SQL * wygenerował z odrzuconymi wierszami, zauważyłem, że między każdym oryginalnym znakiem są złe znaki. Potem pomyślałem o kodowaniu.

Otworzyłem oryginalny plik za pomocą Notepad ++ i przekonwertowałem go na ANSI i wszystko zostało poprawnie załadowane.

Błąd ORA-01722 jest dość prosty. Według Toma Kyte'a :

Podjęliśmy próbę bezpośredniej lub niejawnej konwersji ciągu znaków na liczbę i kończy się to niepowodzeniem.

Jednak problem często nie jest widoczny na początku. Ta strona pomogła mi rozwiązać, znaleźć i naprawić mój problem. Wskazówka: szukaj miejsc, w których jawnie lub niejawnie konwertujesz ciąg znaków na liczbę. (Miałem NVL(number_field, 'string')w swoim kodzie.)

spróbuj tego również, gdy masz błąd nieprawidłowej liczby

W tym a.emplid to number, a b.emplid to varchar2, więc jeśli musisz przekonwertować jedną ze stron

gdzie to_char (a.emplid) = b.emplid

Aby usunąć ten błąd, zawsze możesz użyć funkcji TO_NUMBER (). Może to zostać uwzględnione jako wartości INSERT INTO pracowników numer_telefonu (TO_NUMBER ('0419 853 694');