Retrieve PK on inserts

Retrieve PK on inserts

Postby StefanN » Tue 05 Jan 2016 14:18

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
StefanN
 
Posts: 3
Joined: Tue 05 Jan 2016 14:07

Re: Retrieve PK on inserts

Postby Shalex » Tue 05 Jan 2016 15:11

Please enable dbMonitor to trace the SQL statements sent to the server. Specify the exact INSERT statement which fails to execute.
Shalex
Devart Team
 
Posts: 7460
Joined: Thu 14 Aug 2008 12:44

Re: Retrieve PK on inserts

Postby StefanN » Tue 05 Jan 2016 20:56

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;
StefanN
 
Posts: 3
Joined: Tue 05 Jan 2016 14:07

Re: Retrieve PK on inserts

Postby Shalex » Wed 06 Jan 2016 11:13

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.
Shalex
Devart Team
 
Posts: 7460
Joined: Thu 14 Aug 2008 12:44

Re: Retrieve PK on inserts

Postby StefanN » Wed 06 Jan 2016 16:40

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;
/
StefanN
 
Posts: 3
Joined: Tue 05 Jan 2016 14:07

Re: Retrieve PK on inserts

Postby Shalex » Tue 12 Jan 2016 15:44

We have reproduced the error. We will investigate the issue and notify you about the result.
Shalex
Devart Team
 
Posts: 7460
Joined: Thu 14 Aug 2008 12:44

Re: Retrieve PK on inserts

Postby Shalex » Mon 18 Jan 2016 10:12

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.
Shalex
Devart Team
 
Posts: 7460
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle