Retrieve PK on inserts

[StefanN]

Hello,

how can I retrieve the PK of an inserted entity object? The ID is set in a database trigger (before_insert). When I set property StoreGeneratedPatern = Identity for the PK property, I get ORA-22816 (Invalid returning clause exception).

Thanks in advance.

Stefan

[Shalex]

Please enable dbMonitor to trace the SQL statements sent to the server. Specify the exact INSERT statement which fails to execute.

[StefanN]

Code: Select all

DECLARE
  updatedRowid ROWID;
BEGIN
INSERT INTO MZDIG.PDV_DRAFT(NAME, DESCRIPTION, CREATED, MODIFIED)
VALUES (:p0, :p1, :p2, :p3)
RETURNING ROWID INTO updatedRowid;
OPEN :outParameter FOR SELECT ID_DRAFT FROM MZDIG.PDV_DRAFT WHERE ROWID = updatedRowid;
END;

[Shalex]

PDV_DRAFT is a table (not a view), isn't it?

Please send us a small test project with the corresponding DDL/DML script for reproducing.

[StefanN]

No, it is a view. We use instead_of triggers to handle inserts and updates. This is necessary due to our large datamodel and for security reasons. In this case, the view is pretty much the same as the underlying table. Here is the code of the complete view:

Code: Select all

CREATE OR REPLACE FORCE VIEW MZDIG.PDV_DRAFT
(
   ID_DRAFT,
   NAME,
   DESCRIPTION,
   CREATED,
   CREATED_BY,
   MODIFIED,
   MODIFIED_BY
)
AS
   SELECT "ID_DRAFT",
          "NAME",
          "DESCRIPTION",
          "CREATED",
          "CREATED_BY",
          "MODIFIED",
          "MODIFIED_BY"
     FROM PD_DRAFT;

CREATE OR REPLACE TRIGGER MZDIG.I_PDV_DRAFT
   INSTEAD OF INSERT
   ON MZDIG.PDV_DRAFT
   REFERENCING NEW AS New OLD AS Old
   FOR EACH ROW
DECLARE
   n_check   BOOLEAN;
   d_date   DATE;
   s_user   VARCHAR2 (5);
BEGIN
   d_date := SYSDATE;
   s_user := SYS_CONTEXT ('BD_USER_CTX','USER_CWID');

   INSERT INTO PD_DRAFT (ID_DRAFT,
               NAME,
               DESCRIPTION,
               CREATED,
               CREATED_BY,
               MODIFIED,
               MODIFIED_BY)
        VALUES (:new.ID_DRAFT,
                :new.NAME,
                :new.DESCRIPTION,
                null,
                null,
                null,
                null);
EXCEPTION
   WHEN OTHERS
   THEN
      RAISE;
END I_PDV_DRAFT;
/

[Shalex]

We have reproduced the error. We will investigate the issue and notify you about the result.

[Shalex]

You have encountered a limitation of Oracle view (the same error will be generated if you execute the SQL from dbMonitor via some database management tool).

Possible workarounds:
1. Map your entity to table+trigger (instead of view+trigger).
2. Set StoreGeneratedPattern of your PK column in SSDL to None and assing pseudo values for PK in your code (they will be ignored when inserting records in the database). After SaveChanges(), you should dispose current context (not to use entities in cache with fake IDs), create new DbContext/ObjectContext and retrieve records from database with actual IDs.
3. Create insert/update/dalete stored procedures with necessary logic and returning autogenerated PK via cursor parameter (or imlicit result set in Oracle 12g). Map these stored procedures to entities in EF model.