Primary key missing from create_row when identity column is used as PK #30
Description
Good evening Ottmar,
it seems I found another bug in the generated table API where an identity column is used as PK of the table.
The issue seems to be present both in version 0.5.0 and 0.5.1, but it's not present in version 0.4.3.
Again our database is version 12c (12.2.0.1.0).
Here is a quick test case:
CREATE TABLE xxtest
(
table_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY CONSTRAINT table_id_pk PRIMARY KEY
, description VARCHAR2(40)
);
BEGIN
om_tapigen.compile_api(p_table_name => 'XXTEST', p_enable_dml_view => TRUE);
END;
/
-- verify that the DML trigger was created and it's invalid
SELECT *
FROM user_objects
WHERE object_type = 'TRIGGER'
AND status = 'INVALID';
By looking at the generated code, we can see that the create_row procedure call in XXTEST_IOIUD trigger makes reference to p_table_id as PK:
create or replace TRIGGER "INTERF"."XXTEST_IOIUD"
INSTEAD OF INSERT OR UPDATE OR DELETE
ON "XXTEST_DML_V"
FOR EACH ROW
BEGIN
IF INSERTING THEN
"XXTEST_API".create_row (
p_table_id => :new."TABLE_ID" /*PK*/,
p_description => :new."DESCRIPTION" );
ELSIF UPDATING THEN
"XXTEST_API".update_row (
p_table_id => :new."TABLE_ID" /*PK*/,
p_description => :new."DESCRIPTION" );
ELSIF DELETING THEN
raise_application_error (-20000, 'Deletion of a row is not allowed.');
END IF;
END "XXTEST_IOIUD";
but the PK identity column is not present in the create_row() calls in the generated table API package:
create or replace PACKAGE "XXTEST_API" IS
/*
This is the API for the table "XXTEST".
...
FUNCTION create_row (
p_description IN "XXTEST"."DESCRIPTION"%TYPE )
RETURN "XXTEST"."TABLE_ID"%TYPE;
PROCEDURE create_row (
p_description IN "XXTEST"."DESCRIPTION"%TYPE );
...
The mismatch will thus render the trigger invalid.