-
Title
Post stopped with error ORA-32795: cannot insert into a generated always identity column -
Description
Post stopped with ORA-32795: cannot insert into a generated always identity column.
-
Cause
With Oracle 12c, there is a new feature of defining a column as identity column so that Oracle will auto fill in unique incremental number for key column without using Oracle sequence. With identity column feature on, the insert dml cannot reference the column.As of Version 8.6.5, Shareplex does not support the identity column. -
Resolution
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.