How to use %ROWTYPE when inserting into Oracle table with identity column?

Question

I have an Oracle 12c database with a table containing an identity column:

CREATE TABLE foo (
  id   NUMBER  GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  bar  NUMBER
)

Now I want to insert into the table using PL/SQL. Since in practice the table has many columns, I use %ROWTYPE:

DECLARE
  x foo%ROWTYPE;
BEGIN
  x.bar := 3;
  INSERT INTO foo VALUES x;
END;

However, it give me this error:

ORA-32795: cannot insert into a generated always identity column
ORA-06512: at line 5

Since it is very good for code readability and maintainability, I do not want to stop using %ROWTYPE. Since I under no circumstances want to allow anything but the automatically generated ID's I do not want to lift the GENERATED ALWAYS restriction.

This article suggests that the only way to be able to use %ROWTYPE is to switch to GENERATED BY DEFAULT ON NULL. Is there no other way to fix this?

Answer 1

You can create a view and insert there:

CREATE OR REPLACE VIEW V_FOO AS
SELECT BAR -- all columns apart from virtual columns
FROM foo;

DECLARE
   x V_FOO%ROWTYPE;
BEGIN
   x.bar := 3;
   INSERT INTO V_FOO VALUES x;
END;