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?
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;
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With