Unable to generate mock data for Oracle table with auto-generated Identity

[sanelson2000]

Given a table created with the following DDL

create table PEOPLE
(
  ID         NUMBER generated as identity
    constraint PEOPLE_PK
    primary key,
  FIRST_NAME VARCHAR2(100) not null,
  LAST_NAME  VARCHAR2(100) not null,
  CREATED_AT TIMESTAMP(6)  not null,
  UPDATED_AT TIMESTAMP(6)  not null
);

When I try to use the Mock Data generator, I see the following screen:

When trying to run this, I get

Error inserting mock data: SQL Error [32795] [99999]: ORA-32795: cannot insert into a generated always identity column

If I set the Identity column to NULL, instead of Numeric Sequence, I get:

Error generating mock data:
Can't generate appropriate unique value for the 'ID' attribute.
Try to change the generator or its parameters.

In this case, the Identity column should not be set, so we need to exclude it completely.

[sanelson2000]

Similarly, when generating an Insert Statement, it generates:

INSERT INTO PEOPLE
(ID, FIRST_NAME, LAST_NAME, CREATED_AT, UPDATED_AT)
VALUES("SP_PLUGINS"."ISEQ$$_123968".nextval, '', '', '', '');

The ID column, and associated sequence, should not be included in the generated SQL.

[serge-rider]

Please check it in 5.2.4.
Now you can set any attribute generator to <Skip>. All auto-generated columns automatically skipped by default (although there are no auto-generated columns in Oracle so you will have to set it manually).

[p91paul]

What do you mean by "although there are no auto-generated columns in Oracle"? Here is how to detect virtual (=generated) columns on Oracle: https://stackoverflow.com/questions/8074963/how-to-check-column-is-virtual-in-oracle

[serge-rider]

Virtual column is a different thing. As well as column with default value set to some sequence increment function.
I mean there is no such "flag" on a column marking it as auto-generated. In opposite to, say, MySQL. Potentially it is possible to determine that in fact column is auto-incremented by using a sequence but I'm not sure we should do this.
Because this is not formal. You can also have a trigger which will auto-increment a column or something even more sophisticated.
In any case it is not a problem for mock data generators.

[sanelson2000]

@serge-rider Oracle does indeed have auto-generated columns, as of 12c.
See: https://oracle-base.com/articles/12c/identity-columns-in-oracle-12cr1

Also, see my DDL above. Notice the ID column is NUMBER generated as identity.
When I run that DDL, and then use DBeaver to try to re-generate the DDL, it generates the following:

I believe DBeaver should always skip columns that are marked as GENERATED ALWAYS... Or maybe just GENERATED.

CREATE TABLE "IDM"."PEOPLE" 
   (	"ID" NUMBER GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  NOT NULL ENABLE, 
	"FIRST_NAME" VARCHAR2(100) NOT NULL ENABLE, 
	"LAST_NAME" VARCHAR2(100) NOT NULL ENABLE, 
	"CREATED_AT" TIMESTAMP (6) NOT NULL ENABLE, 
	"UPDATED_AT" TIMESTAMP (6) NOT NULL ENABLE, 
	 CONSTRAINT "PEOPLE_PK" PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS";

[p91paul]

I agree you cannot detect columns generated with trigger: digging into trigger sources to detect it is something nobody should dare trying. Nevertheless, virtual columns can be detected will all_tab_cols, and they are very different from columns with a default attribute: they don't have a stored value, they are calculated based on the function you give them in the generated clause, and putting them in the insert clause will generate an error. That's why I advocate detecting them for this feature (and also for data export where there is a checkbox to skip generated columns). As for detection of 12c identity columns, I'm not sure if the same method would work but surely one of the all_* views is going to contain the information.

[sanelson2000]

So, I just took another look at the page that I referenced above. I have only ever used the GENERATED ALWAYS option (which is the default) for IDENTITY columns. In this case, it looks like the column should always be ignored. Referencing it in any way on insert creates an error. The other options are GENERATED BY DEFAULT and GENERATED BY DEFAULT ON NULL. Using BY DEFAULT allows you to specify the value if you want, or to ignore it, in which case, it will auto-generate the ID. Attempting to specify NULL results in an error. While BY DEFAULT ON NULL appears to work the same way as BY DEFAULT, except you can also specify NULL values on the inserts, in which case, it will use the auto-generated identity.

In any case, I believe the default behavior for generation of mock data, as well as generating the INSERT statements, should be to ignore the column if it is GENERATED, as it will always default to the auto-generated ID if it is not specified.