Page 1 of 1
Retrieve PK on inserts
Posted: Tue 05 Jan 2016 14:18
by 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
Re: Retrieve PK on inserts
Posted: Tue 05 Jan 2016 15:11
by Shalex
Please enable
dbMonitor to trace the SQL statements sent to the server. Specify the exact INSERT statement which fails to execute.
Re: Retrieve PK on inserts
Posted: Tue 05 Jan 2016 20:56
by 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;
Re: Retrieve PK on inserts
Posted: Wed 06 Jan 2016 11:13
by 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.
Re: Retrieve PK on inserts
Posted: Wed 06 Jan 2016 16:40
by 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;
/
Re: Retrieve PK on inserts
Posted: Tue 12 Jan 2016 15:44
by Shalex
We have reproduced the error. We will investigate the issue and notify you about the result.
Re: Retrieve PK on inserts
Posted: Mon 18 Jan 2016 10:12
by 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.