使用RETURNING返回Identity列到变量时触发ORA-01722错误

问题详情 #

向catalog_year表插入记录时，执行RETURNING [column_name] INTO [variable]语句触发ORA-01722: invalid number错误。已确认是显式指定列名，不存在使用变量作为列名的情况。

表结构 #

catalog_year 表结构：
  catalog_year_id    number       (主键，自增列)
  catalog_year       varchar2(10)
  catalog_status_id  number 

执行代码 #

declare
  l_new_calendar_year_id  number;

begin
    insert into catalog_year
      (catalog_year, catalog_status_id)
    values
      ('2022-2023', 5)
    returning catalog_year_id into l_new_calendar_year_id;
end;

排查方向及解决方法 #

• 检查自增列绑定的序列状态
  Oracle 12c+的identity列本质是绑定了自动生成的序列，若序列的缓存值异常或被手动修改为非数值，会导致NEXTVAL返回非法值。执行以下SQL查询序列信息：

  SELECT sequence_name, last_number, increment_by
  FROM user_sequences
  WHERE sequence_name LIKE 'ISEQ$$_%';
  

  若序列异常，可重置序列（替换为实际序列名）：

  ALTER SEQUENCE ISEQ$$_<对应序列名> RESTART WITH 1;
  

• 检查表上的触发器
  如果表存在BEFORE INSERT触发器，且触发器中对catalog_year_id赋值了非数值内容，会触发类型转换错误。查询表的触发器：

  SELECT trigger_name, trigger_type
  FROM user_triggers
  WHERE table_name = 'CATALOG_YEAR';
  

  核对触发器代码，确保对catalog_year_id的赋值为合法数值。

• 确认RETURNING子句的列名拼写
  若列名拼写错误（比如误写为catalog_year），varchar2类型的值赋值给number变量会触发错误。仔细检查RETURNING后的列名是否准确。

• 检查会话NLS数值格式设置
  异常的NLS_NUMERIC_CHARACTERS设置可能导致数值解析错误。查询当前会话设置：

  SELECT value
  FROM nls_session_parameters
  WHERE parameter = 'NLS_NUMERIC_CHARACTERS';
  

  正常应为.,，若异常则重置：

  ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,';
  

内容的提问来源于stack exchange，提问作者McRivers