drop table my_table ORA-00942: table or view does not existMore Details: https://docs.oracle.com/error-help/db/ora-00942
create table my_table (
val1 number generated as identity primary key
, val2 varchar2(16)
, val3 varchar2(16)
, val4 date) Table created.
declare
id number;
begin
INSERT INTO my_table (val2, val3, val4)
VALUES ('one', 'test', sysdate)
RETURNING val1 INTO id;
dbms_output.put_line('new id = ' || id);
end; new id = 1
declare
lrec my_table%rowtype;
id number;
begin
lrec.val2 := 'two';
lrec.val3 := 'test again';
lrec.val4 := sysdate;
INSERT INTO my_table
VALUES lrec
RETURNING val1 INTO id;
dbms_output.put_line('new id = ' || id);
end; ORA-32795: cannot insert into a generated always identity column ORA-06512: at line 9 ORA-06512: at "SYS.DBMS_SQL", line 1721More Details: https://docs.oracle.com/error-help/db/ora-32795
drop table my_table Table dropped.
create table my_table (
val1 number primary key
, val2 varchar2(16)
, val3 varchar2(16)
, val4 date) Table created.
create sequence my_seq start with 42Sequence created.
create or replace trigger my_trg
before insert on my_table for each row
begin
:new.val1 := my_seq.nextval;
end; Trigger created.
declare
lrec my_table%rowtype;
id number;
begin
lrec.val1 := 1;
lrec.val2 := 'three';
lrec.val3 := 'test again';
lrec.val4 := sysdate;
INSERT INTO my_table
VALUES lrec
RETURNING val1 INTO id;
dbms_output.put_line('new id = ' || id);
end; new id = 42