Post stopped with ORA-32795: cannot insert into a generated always identity column.
Resolution:
Workaround:
On target database:
Alter the table for "By Default" clause of the identity column in the target DB from the default one (the ALWAYS clause) to "BY DEFAULT” clause so that insert dml will works independently of the Identity mechanism.
SQL>ALTER TABLE PRODUCT MODIFY (ID NUMBER GENERATED BY DEFAULT AS IDENTITY);
Following is an example of Identity column:
SQL>CREATE TABLE PRODUCT
2 (ID NUMBER GENERATED AS IDENTITY,
3 NAME VARCHAR2(128));
SQL>SELECT TABLE_NAME, COLUMN_NAME, GENERATION_TYPE
2 FROM USER_TAB_IDENTITY_COLS
3 ORDER BY TABLE_NAME;
TABLE_NAME COLUMN_NAME GENERATION
---------- ----------- ----------
PRODUCT ID ALWAYS
Note: as you can see from GENERATION column above, by default it is "ALWAYS". With that default setting, the "insert" DML cannot reference identity column, e. g.” Insert into PRODUCT values (1, 'TEST'); " will not work. Valid "insert" DML for this case is “Insert into PRODUCT values ('TEST');”
After you altering the column using "BY DEFAULT" for the identity column as below, regular insert DML (insert into PRODUCT values (1, 'TEST'); will work.
SQL>ALTER TABLE PRODUCT MODIFY (ID NUMBER GENERATED BY DEFAULT AS IDENTITY);
SQL>SELECT TABLE_NAME, COLUMN_NAME, GENERATION_TYPE
2 FROM USER_TAB_IDENTITY_COLS
3 ORDER BY TABLE_NAME;
TABLE_NAME COLUMN_NAME GENERATION
---------- ----------- ----------
PRODUCT ID BY DEFAULT
Note: for more information for identify column, please refer to Oracle docs.
© 2022 Quest Software Inc. ALL RIGHTS RESERVED. Feedback Terms of Use Privacy