How can an Oracle stored procedure INSERT into a table with an identity column?-CodePudding

Currently facing the following error on what I feel should be an easy statement.

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

The table I am trying to insert into is as easy as this:

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;

I then have a stored procedure that is trying to INSERT into this table:

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

Why would this not be allowed? How else can you possible use a stored procedure to insert into a table with a identity column?

I have also tried updating my stored procedure removing the identity column from the insert (shown below) and it results in the same error:

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

CodePudding user response：

You will get that error if either V_ID or V_TEMPLATE_NAME is not declared or in scope. With an anonymous block, and with the version that skips the generated column:

BEGIN
  INSERT INTO "TABLE" (ID, TEMPLATE_NAME)
  VALUES (V_ID, V_TEMPLATE_NAME);
END;
/

ORA-06550: line 3, column 17:
PL/SQL: ORA-00984: column not allowed here
ORA-06550: line 2, column 3:
PL/SQL: SQL Statement ignored

But it works if the variables are declared:

DECLARE
  V_ID NUMBER;
  V_TEMPLATE_NAME VARCHAR2(30);
BEGIN
  INSERT INTO "TABLE" (ID, TEMPLATE_NAME)
  VALUES (V_ID, V_TEMPLATE_NAME);
END;
/

1 rows affected

It also works with your first statement, since your generated column has then on null clause:

DECLARE
  V_ID NUMBER;
  V_TEMPLATE_NAME VARCHAR2(30);
BEGIN
  INSERT INTO "TABLE" 
  VALUES (V_ID, NULL, V_TEMPLATE_NAME);
END;
/

1 rows affected

though it would still be better practice to list the column names.

fiddle

Presumably in your procedure those will be formal parameter names, but the principle is the same; possibly you just have a typo somewhere.

In the first statement you posted, you actually have:

VALUES (ID, NULL, V_TEMPLATE_NAME);

with ID not V_ID. If that is what you are really running then that will fail with the same error even if you have a V_ID variable. As your second statement has V_ID it's unclear if one or the other has been changed during posting.

Either way... you need to have the variables declared (as local variables or procedure arguments) and refer to them with the correct names.

(See also the documentation on static SQL name resolution; with missing variables or a name mismatch it's failing on step 5.)