How to use% ROWTYPE when pasting into an Oracle table with an 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 a 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, this gives me this error:

ORA-32795: cannot be inserted into the generated identity column

ORA-06512: in row 5

Since it reads the code very well and supports it, I do not want to stop using it %ROWTYPE. Since under no circumstances do I want to allow anything other than an automatically generated identifier, I do not want to raise the restriction GENERATED ALWAYS.

, %ROWTYPE GENERATED BY DEFAULT ON NULL. ?

Answer 1

:

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;

Answer 2

, , 12c - INVISIBLE, .

, %ROWTYPE , .

, , , !

, , , .

DROP TABLE t;

CREATE TABLE t ( id number invisible generated always as identity, 
                 val varchar2(30));

insert into t (val) values ('A');                 

DECLARE

  record_without_id t%rowtype;
  CURSOR c_with_id IS SELECT t.id, t.* FROM t;
  record_with_id c_with_id%rowtype;

BEGIN
  record_without_id.val := 'C';
  INSERT INTO t VALUES record_without_id;

  -- If you want ID, you must select it explicitly
  SELECT id, t.* INTO record_with_id FROM t WHERE rownum = 1;

  DBMS_OUTPUT.PUT_LINE(record_with_id.id || ', ' || record_with_id.val);
END;
/

SELECT id, val FROM t;    

Answer 3

I think that combining past answers - using a view with an invisible identity column - is the best way to accomplish the task:

create table foo (id number generated always as identity primary key, memo varchar2 (32))
;
create or replace view fooview (id invisible, memo) as select * from foo
; 
<<my>> declare 
    r fooview%rowtype;   
    id number;
begin
    r.memo := 'first row';
    insert into fooview values r
    returning id into my.id
    ;
    dbms_output.put_line ('inserted '||sql%rowcount||' row(s) id='||id);  
end;
/

1 row id = 1 inserted