ORA-01036 with concurrency mode 'Fixed'

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
ejeet
Posts: 1
Joined: Thu 21 Aug 2008 03:59

ORA-01036 with concurrency mode 'Fixed'

Post by ejeet » Thu 21 Aug 2008 04:28

If you have a self-referencing table that includes a column with the concurrency mode set to Fixed in the entity model, then when you add a new entity and save changes, you get an ORA-01036 error because the generated PL/SQL is invalid.

Here's a small example that illustrates the problem.

Create a table TEST with the following columns:
ID NUMBER(18) - primary key
PARENT_ID NUMBER(18) nullable, Foreign key to ID
NAME VARCHAR2(50)
UPDATED_UTC TIMESTAMP(6) Default SYSTIMESTAMP

Create an entity model from this table and set the Concurrency Mode property of UPDATED_UTC to Fixed. Add a row, e.g. values
1,null,'Parent','8/21/2008 3:08:47 AM'

Then try and add a child row, e.g. values
2,1,'Child','8/21/2008 3:08:47 AM'.

in C#, something like the following:
using (OraEntities ctx = new OraEntities())
{

var query = from test in ctx.TEST
where test.ID == 1
select test;
TEST parent = query.First();

TEST child = new TEST();
child.ID = 2;
child.NAME = "Child";
child.parent = parent;
child.UPDATED_UTC = DateTime.UtcNow;

ctx.AddToTEST(child);

ctx.SaveChanges();
}
It will give an ORA-01036 error on the SaveChanges.

If you dump out the ObjectContext changes just before the SaveChanges you get the following (where the first section is the DbCommand command text and subsequent section is the DbParameter parameter values):

=============== BEGIN COMMAND ===============

update NH_OP_USER.TEST
set p0 = 0
where ((ID = :p1) and (UPDATED_UTC = :p2))

p0 = 0
p1 = 1
p2 = 8/21/2008 3:08:47 AM

=============== END COMMAND ===============
=============== BEGIN COMMAND ===============

insert into NH_OP_USER.TEST(ID, PARENT_ID, NAME, UPDATED_UTC)
values (:p0, :p1, :p2, :p3)

p0 = 2
p1 = 1
p2 = Child
p3 = 8/21/2008 3:47:37 AM

=============== END COMMAND ===============

I don't know why it's generating the UPDATE stuff (it does a similar thing in T-SQL for a SQL Server database) - it looks meaningless to me - but the important thing to note is the syntax error in the SET, there should be a colon before the p0.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Fri 22 Aug 2008 13:10

Thanks for the detailed error description. When the Concurrency Mode is 'None', everything works fine. The update bug with Concurrency Mode 'Fixed' is corrected. The next build will be without this problem and will be available next week.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Wed 27 Aug 2008 16:28

New build of OraDirect .NET version 4.75.40 is available!
More: http://devart.com/forums/viewtopic.php?t=12756

Post Reply