ORA-01722 means Oracle tried to convert a string to a number and the string was not a valid number. The conversion can be explicit, with TO_NUMBER, or implicit, when a text column is compared with a number. In Oracle AI Database 26ai the message shows the offending value and the column, which makes the bad row easy to find.
Reproduce the Error
READING is a text column holding numbers, one value that is not a number, and one with a decimal comma:
Example:
create table demo_readings (sensor varchar2(10), reading varchar2(10));
insert into demo_readings values ('S1', '21.5');
insert into demo_readings values ('S2', 'n/a');
insert into demo_readings values ('S3', '19,8');
commit;
select sensor, to_number(reading) as value from demo_readings;
select sensor from demo_readings where reading > 20;Output:
Table DEMO_READINGS created. 1 row inserted. 1 row inserted. 1 row inserted. Commit complete. Error starting at line : 7 In command - select sensor, to_number(reading) as value from demo_readings Error at Command Line : 7 Column : 16 Error report - SQL Error: ORA-01722: unable to convert string value containing 'n' to a number: READING ORA-03302: (ORA-01722 details) invalid string value: n/a Error starting at line : 8 In command - select sensor from demo_readings where reading > 20 Error at Command Line : 8 Column : 40 Error report - SQL Error: ORA-01722: unable to convert string value containing 'n' to a number: READING ORA-03302: (ORA-01722 details) invalid string value: n/a
Both queries fail on n/a. The second has no TO_NUMBER at all: comparing READING with the number 20 makes Oracle convert every reading to a number.
Fix: Convert Safely
DEFAULT ... ON CONVERSION ERROR returns a value of your choice for strings that cannot be converted, and VALIDATE_CONVERSION tells which strings are valid numbers:
Example:
-- convert what can be converted and get NULL for the rest
select sensor, reading,
to_number(reading default null on conversion error) as value,
validate_conversion(reading as number) as is_number
from demo_readings;Output:
SENSOR READING VALUE IS_NUMBER _________ __________ ________ ____________ S1 21.5 21.5 1 S2 n/a 0 S3 19,8 0
19,8 is not a valid number either, because the session uses a period as the decimal separator; it needs a format model or a different NLS setting to be read correctly.
Other Common Causes
- Comparing a text column with a number, as in WHERE code = 123; compare with a string, '123', instead.
- Numbers stored with thousands separators or currency symbols; give TO_NUMBER a format model such as '999G999D99'.
- A query that worked until a row with bad data arrived: the conversion fails only when that row is read.
Things to Know
- Store numbers in NUMBER columns; text columns with numbers lead to this error sooner or later.
- In PL/SQL, the same failure assigning a string to a number variable raises ORA-06502 instead.
Related Guides
Conclusion
ORA-01722 means a string could not be converted to a number, explicitly or through an implicit comparison. Find the value in the 26ai message, convert safely with DEFAULT ON CONVERSION ERROR and VALIDATE_CONVERSION, and compare text columns with text.
See my articles more often when you search Google.
