故障处理｜Oracle数据库 ORA-01722: invalid number 解决方法

您是否收到“ORA-01722 invalid number”错误？我将在本文中解释这个错误是什么以及如何解决它。

ORA-01722 原因

ORA-01722 错误是由试图将字符串转换为数字引起的。这可能是因为您的表达式中的数字字符无效，或者您正在尝试将文本值添加到数字列中。

您已经运行了一个 SQL 查询（例如，可以是 SELECT、INSERT、UPDATE），并且出现了这个错误：

ORA-01722: invalid number

此错误的原因是 Oracle 已尝试将字符串转换为数字，但无法执行转换。

例如，无法将“Hello”的值转换为数字。

有效数字包含以下字符：

• 数字 0 到 9
• 可能是小数点
• 一个 + 或 - 符号
• 一个 E 或 e 字符

该错误可能出现在以下查询中：

SELECT TO_NUMBER('123,100') FROM dual;

SELECT 'DatabaseStar' - 2016 FROM dual;

有几种方法可以解决这个错误，让我们来看看。

ORA-01722 解决方案

“invalid number”错误消息的解决方案可能是以下几种情况之一：

• 查询中的字符或错字不正确
• 数据库中的错误数据
• 查询逻辑有问题

1 – 插入查询中的数据类型不匹配

您是否尝试使用INSERT INTO VALUES将数据插入到表中？

如果是这样，请检查您的列是否与您的值对齐。我的意思是，确保包含数字的列的位置与您尝试插入的数字相匹配。

这个查询会产生一个错误（假设 score 是一个数字）：

INSERT INTO student_results (student_id, subject_name, score, comments)
VALUES (1, 'Science', 'Pretty good', 95);

我在这里的列错误的方式。为了更正查询，我需要将分值 95 移到主题名称和评论之间。

INSERT INTO student_results (student_id, subject_name, score, comments)
VALUES (1, 'Science', 95, 'Pretty good');

2 – 使用子查询插入或更新

您是否使用子查询在表中插入或更新值？

此错误可能更难检测，因为您没有明确说明要插入的值。

发生错误是因为在子查询中找到的值之一试图插入数字列，而该值不是数字。

要查找错误原因，您可以自行运行子查询并添加 WHERE 条件：

WHERE UPPER(column) != LOWER(column)

将“列”替换为您怀疑包含错误数据的列。UPPER 和 LOWER 函数将从字符串返回不同的值，并且应该留下具有字符串值的行。

如果您想进一步挖掘，或者如果这不起作用：

1. 自己运行子查询查看结果
2. 查看您希望为数字的列中的值，以识别任何看起来像明显字符的值。
3. 如果您不能轻易分辨，您可以对列执行TO_NUMBER以查找错误。
4. 如果这没有帮助，请尝试获取子查询中每一列的 DISTINCT 列表，然后执行 TO_NUMBER。
5. 您还可以使用 WHERE 子句来限制子查询的结果，因此您一次只查看一个小数据集。

找到导致问题的值后，您可以更新错误数据，或更新查询以处理此数据。

3 – Select 语句中的隐式转换

您在运行 SELECT 语句时是否收到此“ORA-01722 invalid number”错误？

这个错误有几个原因：

1. WHERE 子句中的隐式转换
2. 使用 TO_NUMBER 时格式掩码无效

在大多数情况下，这是由于 WHERE 子句中的隐式转换。隐式转换是 Oracle 正在转换一个值但您没有指定它的地方。

要解决此问题，请检查与字符列进行比较的数字列。

例如：

SELECT ...
WHERE number_col = varchar_col;

这将导致 VARCHAR 列隐式转换为数字，这可能会导致无效数字错误。

如果您在查询中使用 TO_NUMBER 函数，请确保格式掩码包含可接受的字符。

前面的示例会导致错误。

4 – 其他可能的解决方案

此错误还有其他一些解决方案：

• 包含空格的字段无法转换，因此请确保您TRIM 此数据，将其转换为 NULL 或零。
• 两个数据库之间的数字数据库格式不匹配。例如，欧洲数字数据使用 12.345,67，其中美国格式为 12,345.67。检查您的 NLS_LANG 设置以确保这不会导致任何问题。
• 表上基于函数的索引可能会导致错误。查看您的表以查看是否有任何基于函数的索引可以转换数据。

所以，这就是您在 Oracle 中解决 ORA-01722 invalid number错误的方法！

原文标题：ORA-01722 invalid number Solution

原文作者：Ben Brumm

原文链接：https://www.databasestar.com/ora-01722/