ORA-22816 when using StoreGeneratedPattern in EF

[Tobsel]

Hello,

we are using Entity Framework and the StoreGeneratedPattern for ROWID and other fields (no primary key fields)

we have the following problem. We are often using public synonyms that point to a table on an other database over a database link. The table structure is the same as defined in Entity Framework. We just want to read/write the data into an other database with this technique.

When using StoreGeneratedPattern Devart generates a returning clause for getting this values. This is not working with database links. Is there a possiblity to force devart to create a select statement (over the key fields) after update/insert to get the fields back? Sure this is not as fast as the returning clause, but would help is such a case. Maybe over a proeprty.

Thank you very much,

Tobias

[AndreyR]

Thank you for the inquiry.
We have just added Entity Framework Support at our UserVoice.
Please provide your feedback - vote for the existing suggestions and add your own (like this one).
As for this suggestion, could you please provide an example of PL|SQL:
1. DDL script of the table:
CREATE TABLE...
2. StoreGeneratedPattern settings for the columns:
ID Identity,
MyField Computed...
You can provide a .edmx piece instead of these two steps.
3. PL/SQL block for retrieving data from the inserted or updated record.
We will investigate the possibility of implementing this behaviour as optional.

[Rolf]

I am currently stumbling over the same problem.
Is there a solution inbetween?!

Regards, Rolf

[Shalex]

Here is a workaround for a non-primary key column:
1. Create the database objects:

Code: Select all

CREATE TABLE DEPT (
  DEPTNO NUMBER(4),
  DNAME VARCHAR2(14 BYTE),
  LOC VARCHAR2(13 BYTE),
  CONSTRAINT PK_DEPT PRIMARY KEY (DEPTNO));
/
CREATE TRIGGER dname_trigger
BEFORE INSERT or update ON dept FOR EACH ROW
BEGIN
  SELECT 'changed' INTO :NEW.dname FROM DUAL;
END;
/
CREATE OR REPLACE PROCEDURE insert_dept (deptno_p number, loc_p VARCHAR2, dname_p OUT SYS_REFCURSOR)
AS
BEGIN
Insert into dept(deptno, loc) values (deptno_p, loc_p);
open dname_p for Select dname from dept where deptno = deptno_p;
END; 
/

2. Create the Devart Entity Model with the DEPT table. Drag&drop the insert_dept procedure from Database Explorer to the Stored Procedures node of Store part of the model in Model Explorer.
3. Right click on the DEPT class on the diagram > Stored Procedure mapping > select Insert in the Commands list > select INSERT_DEPT in the Stored Procedure drop-down > add one Result Column Binding: Column=DNAME, Property=DNAME:String. Press OK and save the model.
4. Select the DNAME column of the DEPT table in Store part of the model in Model Explorer and set its Store Generated Pattern property to Identity.
5. Run the code to make sure the workaround works:

Code: Select all

            using (MyEntities context = new MyEntities()) {
                DEPT d = new DEPT() { DEPTNO = 1, LOC = "New York" };
                context.AddToDEPTs(d);
                context.SaveChanges();
                string out_value = d.DNAME; // d.NAME is initialized with the value from database
            }