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?

@MatthewMcPeak Someone could insert their own `id`, that is not part of the sequence. I do not want to allow that. — Anders, Nov 12 '15 at 16:55

score 6 · Answer 1 · answered Nov 12 '15 at 17:51

The only thing I can think of, since you're on 12c is to make the identity column INVISIBLE, like the code below.

The problem is that it makes getting a %ROWTYPE with the id a little more difficult, but it's doable.

Of course, it may also confuse other people using your table to not see a primary key!

I don't think I'd do this, but it is an answer to your question, for what that's worth.

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;

I agree that this might cause bigger problems further on than it solves. But I appreciate the idea anyway, for completeness. Always good to have all the options on the table. — Anders, Nov 12 '15 at 18:56

score 5 · Accepted Answer · answered Nov 12 '15 at 15:58

5

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;

answered Nov 12 '15 at 15:58

Wernfried Domscheit

54,457
9
76
110

Thank you for the answer. Smart workaround! Would prefer to not need any extra objects, so I will wait a while before accepting and see what other ideas people have. – Anders Nov 12 '15 at 16:54
Well, if you don't like to create the view you could loop over `ALL_TAB_COLS` and do everything dynamically with Package [DBMS_SQL](http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_sql.htm#BABEDAHF). However, you would have to write a lot of code and it would definitely be an overkill. – Wernfried Domscheit Nov 12 '15 at 17:23
Good point Wernfried, not to mention how inefficient the resulting code would be. – Brian Leach Mar 10 '16 at 00:57

0xdb · Answer 3 · 2018-12-23T10:27:31.500

I think that a mix of former answers - using view with invisible identity column - is a optimal way to accomplish this 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;
/

inserted 1 row(s) id=1

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

3 Answers3

Linked