Oracle存储过程插入含IDENTITY列的表遇ORA-00984错误求解

问题背景 #

执行插入操作时触发错误：

PL/SQL: ORA-00984: column not allowed here

涉及的表结构：

CREATE TABLE "DB"."TABLE" 
(
    "ID" NUMBER(*,0), 
    "TEMPLATE_ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 99999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE , 
    "TEMPLATE_NAME" VARCHAR2(256 BYTE)
) SEGMENT CREATION IMMEDIATE;

两次尝试的插入语句均报错：

1. 初始插入语句：

INSERT INTO "DB"."TABLE" 
VALUES (ID, NULL, V_TEMPLATE_NAME);

2. 修改后的插入语句：

INSERT INTO "DB"."TABLE" (ID, TEMPLATE_NAME)
VALUES (V_ID, V_TEMPLATE_NAME);

需要解决的问题：语句报错的原因是什么？如何通过存储过程向含IDENTITY列的表插入数据？

错误原因拆解 #

第一次插入语句的问题 #

VALUES子句里的ID是列名，Oracle不允许在VALUES中直接引用列名——这里只能放字面量、已声明的变量或合法表达式。把列名ID放在这里，Oracle会判定为非法的列引用，直接触发ORA-00984错误。

另外，虽然TEMPLATE_ID是GENERATED BY DEFAULT ON NULL的IDENTITY列，显式传NULL是符合规则的，但ID的写法错误才是本次报错的核心。

第二次插入语句的问题 #

大概率是你没在存储过程里声明V_ID变量，或者变量名拼写错误。Oracle会把未声明的“变量”默认解析为列名，而表中不存在V_ID这个列，因此同样触发"column not allowed here"错误。

正确的插入方案 #

针对带IDENTITY列的表，有两种合法的插入方式：

方式1：让IDENTITY列自动生成值 #

因为TEMPLATE_ID是GENERATED BY DEFAULT ON NULL，所以可以跳过该列，让Oracle自动生成值。只要确保插入语句里的变量都已正确声明：

-- 明确指定要插入的列，跳过IDENTITY列
INSERT INTO "DB"."TABLE" (ID, TEMPLATE_NAME)
VALUES (V_ID, V_TEMPLATE_NAME);

注意：V_ID和V_TEMPLATE_NAME必须在存储过程的DECLARE段提前声明，示例：

DECLARE
    V_ID NUMBER;
    V_TEMPLATE_NAME VARCHAR2(256);
BEGIN
    -- 给变量赋值
    V_ID := 1001;
    V_TEMPLATE_NAME := '测试模板';
    -- 执行插入
    INSERT INTO "DB"."TABLE" (ID, TEMPLATE_NAME)
    VALUES (V_ID, V_TEMPLATE_NAME);
    COMMIT;
END;
/

方式2：手动指定IDENTITY列的值 #

如果业务需要手动设置TEMPLATE_ID，需要先开启会话级的IDENTITY_INSERT权限：

DECLARE
    V_ID NUMBER;
    V_TEMPLATE_ID NUMBER;
    V_TEMPLATE_NAME VARCHAR2(256);
BEGIN
    V_ID := 1002;
    V_TEMPLATE_ID := 5001;
    V_TEMPLATE_NAME := '手动指定ID模板';
    
    -- 开启手动插入IDENTITY列的权限
    EXECUTE IMMEDIATE 'SET IDENTITY_INSERT "DB"."TABLE" ON';
    
    INSERT INTO "DB"."TABLE" (ID, TEMPLATE_ID, TEMPLATE_NAME)
    VALUES (V_ID, V_TEMPLATE_ID, V_TEMPLATE_NAME);
    
    -- 关闭权限（建议操作完成后关闭，避免影响后续操作）
    EXECUTE IMMEDIATE 'SET IDENTITY_INSERT "DB"."TABLE" OFF';
    
    COMMIT;
END;
/

额外注意点 #

• 所有用到的变量必须提前声明，类型要和表列匹配；
• 因为表和列创建时用了双引号，后续引用必须严格匹配大小写（比如"DB"."TABLE"不能写成db.table）；
• 操作完成后记得提交事务（COMMIT），避免数据未持久化。

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