ORA-22816 when using StoreGeneratedPattern in EF

ORA-22816 when using StoreGeneratedPattern in EF

Postby Tobsel » Wed 09 Mar 2011 09:56

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
Tobsel
 
Posts: 11
Joined: Fri 28 May 2010 12:31

Postby AndreyR » Thu 10 Mar 2011 16:47

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.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Re: ORA-22816 when using StoreGeneratedPattern in EF

Postby Rolf » Wed 02 Jan 2013 16:58

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

Regards, Rolf
Rolf
 
Posts: 6
Joined: Wed 11 Jan 2012 15:46

Re: ORA-22816 when using StoreGeneratedPattern in EF

Postby Shalex » Thu 03 Jan 2013 13:33

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


Return to dotConnect for Oracle