Migration from MS SQL to Oracle, timestamp problem
Posted: Mon 19 Apr 2010 14:03
Hello,
I try to migrate database from MS SQL 2005 to Oracle 10. There is one problem related to MS SQL timestamp data type. In MS SQL I have a table with timestamp data type:
In Oracle the same table looks like this:
And I also create triggers to set ConcurrencyStamp field:
The content of Oracle SSDL file .The CSDL and MSL are the same for MS
SQL and Oracle.
On the conceptual level the ConcurrencyStamp field is still binary type (Byte[] in .NET).
OK. Then I try to change IpAddress and State fields in the program.
and got an exception:
{"ORA-06550: line 6, column 50:\nPL/SQL: ORA-00932: inconsistent datatypes: expected - got BLOB\nORA-06550: line 4, column 1:\nPL/SQL: SQL Statement ignored"}
And dbMonitor prints at that moment:
Why EF tries to convert Byte[] to BLOB? Devart documentation says that RAW data type is mapped into Byte[].
I try to migrate database from MS SQL 2005 to Oracle 10. There is one problem related to MS SQL timestamp data type. In MS SQL I have a table with timestamp data type:
Code: Select all
CREATE TABLE [dbo].[Users](
[Id] [int] IDENTITY(1,1) NOT NULL,
[LastName] [nvarchar](128) NOT NULL,
[FirstName] [nvarchar](128) NULL,
[MiddleName] [nvarchar](128) NULL,
[State] [smallint] NULL,
[Rating] [decimal](6, 2) NULL,
[IpAddress] [nvarchar](64) NULL,
[Phone] [nvarchar](32) NULL,
[EMail] [nvarchar](128) NULL,
[Contacts] [nvarchar](256) NULL,
[Description] [nvarchar](512) NULL,
[Avatar] [image] NULL,
[ConcurrencyStamp] [timestamp] NOT NULL,
[Tag] [int] NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Code: Select all
CREATE TABLE "Users"
(
"Id" NUMBER(9) NOT NULL ,
"LastName" NVARCHAR2(128) NOT NULL ,
"FirstName" NVARCHAR2(128) NULL ,
"MiddleName" NVARCHAR2(128) NULL ,
"State" NUMBER(9) NULL ,
"Rating" NUMBER(6,2) NULL ,
"IpAddress" NVARCHAR2(64) NULL ,
"Phone" NVARCHAR2(32) NULL ,
"EMail" NVARCHAR2(128) NULL ,
"Contacts" NVARCHAR2(256) NULL ,
"Description" NVARCHAR2(512) NULL ,
"Avatar" BLOB NULL ,
"ConcurrencyStamp" RAW(8) NOT NULL ,
"Tag" NUMBER(9) NULL
);
Code: Select all
CREATE TRIGGER "Users_Id_Trigger"
BEFORE INSERT ON "Users"
FOR EACH ROW
BEGIN
IF (:new."Id" IS NULL) THEN
SELECT "Users_Seq".nextval INTO :new."Id" from dual;
END IF;
:new."ConcurrencyStamp" := utl_raw.cast_to_raw(dbms_utility.get_time);
END;
/
CREATE TRIGGER "Users_Update_Trigger"
BEFORE UPDATE ON "Users"
FOR EACH ROW
BEGIN
:new."ConcurrencyStamp" := utl_raw.cast_to_raw(dbms_utility.get_time);
END;
/
SQL and Oracle.
Code: Select all
OK. Then I try to change IpAddress and State fields in the program.
Code: Select all
user.IpAddress = Util.GetIPAddress();
user.State = (Int16)Constants.UserState.Connected;
dbContext.SaveChanges();
{"ORA-06550: line 6, column 50:\nPL/SQL: ORA-00932: inconsistent datatypes: expected - got BLOB\nORA-06550: line 4, column 1:\nPL/SQL: SQL Statement ignored"}
And dbMonitor prints at that moment:
Code: Select all
declare
updatedRowid ROWID;
begin
update RETAILSOLUTIONS2."Users"
set "State" = :p0, "IpAddress" = :p1
where (("Id" = :p2) and ("ConcurrencyStamp" = :p3))
returning ROWID into updatedRowid;
open :outParameter for select "ConcurrencyStamp" from RETAILSOLUTIONS2."Users" where ROWID = updatedRowid;
end;