We had to port our giant-_ish_, Entity Framework based application to work on Oracle RDBMS (11g, later 12c).
We did it.
And we learned a lot.
Boss comes around and…
One day boss comes around and says:
Boss: “We need our application to work on Oracle”
Dev: “Oracle? But we use MS SQL…”
Boss: Can you do it?
Dev: [thinking at loud] We use Entity Framework, which is somewhat abstraction on top of our database. I’ve heared that there are some providers enabling work with Oracle, but never used them. I’ve seen this Microsoft conference where leading programmer was swapping SQL Server provider with Oracle one by a single line and it worked like a charm, but it was template-basic application and…
Boss: Great! We need this in 6 weeks.
Dev: But…
Boss: Inform me constantly. It’s very important project and fail is not an option. Don’t forget we need some time for tests. 2 weeks should be enough I guess. I gotta go now, I have another meeting. Thanks !
* Sound of the closing door. *
Dev: …what just happened?
Accept and advance
This is fromatted versions of all information gathered by 3 folks working on Oracle port for couple weeks.
I believe we wouldn’t do it if would not cooperate and solve each problem together.
All problems we’ve encountered were put on our slack channel with short solution and decryption.
It was the best decision we made in whole project.
Most common problems
Note: If you have troubles that are not covered in this article, here’s some notes from Oracle. Some of the issues are described there as well as are here. THOSE ARE THE MOST VALUABLE LINKS IN WHOLE ARTICLE.
- Release 12.1.0.2.1 for ODAC 12c Release 3 Production
- Oracle.ManagedDataAccess NuGet Package 12.1.24160419 README
If you google more you will find other resources on Oracle servers. Unfortunately, I discovered them when I was near finishing writing whole document.
Those files can be found inside different ODP.NET nuget packages (those readme files you never read).
This is what happen when you don’t read
readmefiles.
Which provider should I use?
We recommend using Official Oracle ODP.NET. All cases presented here use this and only this provider. Version is 12.1.2400.
Dependencies
EntityFramework (>= 6.0.0 && < 7.0.0)
Oracle.ManagedDataAccess (>= 12.1.2400 && < 12.2.0)
Schema == User
This is the first thing you should learn about Oracle. In Oracle, there are no databases. There are schemas and schemas are users.
Do you want to create new database on your localhost like you did on MS SQL? Create new user. Its name is the name of your datab… I mean schema.
You can connect to your new schema with new connection in Oracle SQL Developer. Remember to assign to the created user Connect role (and maybe some others, I used to grant it with all roles, since I didn’t have to deal with security issues).
Note: User
systemhasOther users. Those are all users on your Oracle server. You can access their database objects (dbo) fromsystemuser level, without connecting to the user itself. It might come handy in scenarios when you often repeat drop/create users.
DBO names.Length <= 30
All table names in your schemas must contain less or equal than 30 characters.
To configure your entity to map to table with specified name use ToTable(:string) in your EntityTypeConfiguration<> derived class.
public class FoobarEntityConfiguration : EntityTypeConfiguration<Foobar>
{
public FoobarEntityConfiguration()
{
ToTable("Foobar");
// More configuration here ...
}
}
http://stackoverflow.com/a/756569/864968
I’d suggest creating convention test that checks every oracle table configuration defines table name no greater then 30 characters.
EF logger
This is extremely helpful when a) you don’t have Oracle license (which contains profiler and developer edition not) and b) you want to peek what’s going on under the hood after your C# is magically transformed to some kind of SQL.
EF allows to log executed queries.
private void EnableDebugLogs(DbContext context)
{
context.Database.Log = s => Debug.WriteLine(s); // SQL writer
}
Logged queries can be found in Output window in Visual Studio. You can provide any action particularly.
ORA-01918: user ‘dbo’ does not exist
You’ve created EF migration, run Update-Database and you get this error.
Remember what I was talking about schemas and users? They’re the same thing!
In DbContext derived class specify schema name. It should be the same as the user you’re connecting with database.
public class FooDbContext : DbMigrationsConfiguration<FooDbContext>
{
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.HasDefaultSchema(" < Your connection string user here > ".ToUpper()); // Make sure it's upper case !
}
}
ORA-01005: null password given; logon denied
This might happen while you’re running Update-Database and your ConnectionString does not contain Persist Security Info.
http://stackoverflow.com/questions/14810868/ora-01005-null-password-given-logon-denied
Migration directory
If you, just like us, have to support both MS SQL and Oracle, you should consider seperate migrations set for each RDBMS. To do so, configure your migration directory in DbMigrationConfiguration derived class.
public class FooMigrationConfiguration : DbMigrationsConfiguration<FooContext>
{
public FooMigrationConfiguration()
{
DatabaseHelper.SetMigrationDirectory(this, " i.e. ContextsFooMigrations ");
}
}
public static void SetMigrationDirectory<TContext>(DbMigrationsConfiguration<TContext> migration, string migrationsPath) where TContext : DbContext
{
if (string.IsNullOrEmpty(migrationsPath))
{
throw new ArgumentException($"{nameof(migrationsPath)} cannot be null or empty.");
}
if (migrationsPath.Contains("/"))
throw new ArgumentException($"Invalid {nameof(migrationsPath)}. Path should be valid Windows path. Use backslashes instead of slashes.");
migration.MigrationsDirectory = migrationsPath;
}
Note: Speaking aside, support for both RDBMS is like maintaining two similar but different applications. They just differ. It is very unlikely that you will end up with application working on both MS SQL and Oracle without any changes.
EF is great tool, but don’t expect it to do miracles.
NLog + Oracle
If you used to log exception errors directly to Database with help of NLog then you should also modify your database target in your nlog.config.
<!--NLog configuration-->
<!--For more info visit: https://github.com/NLog/NLog website -->
<nlog xmlns="http://www.nlog-project.org/schemas/NLog.xsd"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.nlog-project.org/schemas/NLog.xsd NLog.xsd"
autoReload="true"
throwExceptions="true"
internalLogLevel="Off"
internalLogFile="App_Data
log-internal.log" >
<targets>
<!--Puts logs into database-->
<target name="db" xsi:type="Database" commandType="Text" connectionStringName="FoobarDbContext">
<commandText>
insert into FOOBARSCHEMA."FOOBARTABLE"("Id", "Level", "Logger", "Message", "StackTrace", "Date", "TenantId", "UserId")
values(sys_guid(), :LogLevel, :Logger, :Message, :StackTrace, systimestamp, :TenantId, :UserId)
</commandText>
<!--Reserved words by Oracle (don't use them as variable names: Level, Date-->
<parameter name="LogLevel" layout="${level}"/>
<parameter name="Logger" layout="${logger}"/>
<parameter name="Message" layout="${message}"/>
<parameter name="StackTrace" layout="${exception:format=Message,Type,Method,StackTrace,Data:separator=
:maxInnerExceptionLevel=10:innerFormat=Message,Type,Method,StackTrace,Data:innerExceptionSeparator=
}"/>
<parameter name="TenantId" layout="${event-properties:item=TenantId}"/>
<parameter name="UserId" layout="${event-properties:item=UserId}"/>
</target>
</targets>
<rules>
<logger name="*" minlevel="Warn" writeTo="trace,db" />
</rules>
</nlog>
Web.config
It’s quite clear that we should maintain at least two versions of configuration file.
We decided to name them accordingly Web.Oracle.config and Web.MSSQL.config. It’s clear and simple. During deploy the correct file is being selected and included into artifact.
You can also write watcher that detects Web.config change and accordingly replaces rest of web.configs in whole solution. You can do that like we did, with help of Cake Watch.
External app.settings section
It is a good practice to have separate config file with common settings for both MS SQL and Oracle version. We can exclude them by adding file attribute.
<appSettings file="AppSettings.config" />
Connection strings
Connections string differ so much that should be kept separately.
<connectionStrings>
<add name="ApplicationDbContext"
providerName="Oracle.ManagedDataAccess.Client"
connectionString="User Id=ApplicationDbContext;Password=P4S5W0RD;Data Source=OracleDataSource;Persist Security Info=true" />
</connectionStrings>
<entityFramework>
<defaultConnectionFactory type="Oracle.ManagedDataAccess.EntityFramework.OracleConnectionFactory, Oracle.ManagedDataAccess.EntityFramework" />
<providers>
<provider invariantName="Oracle.ManagedDataAccess.Client"
type="Oracle.ManagedDataAccess.EntityFramework.EFOracleProviderServices, Oracle.ManagedDataAccess.EntityFramework, Version=6.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />
</providers>
</entityFramework>
<system.data>
<DbProviderFactories>
<remove invariant="Oracle.ManagedDataAccess.Client" />
<add name="ODP.NET, Managed Driver"
invariant="Oracle.ManagedDataAccess.Client"
description="Oracle Data Provider for .NET, Managed Driver"
type="Oracle.ManagedDataAccess.Client.OracleClientFactory, Oracle.ManagedDataAccess, Version=4.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />
</DbProviderFactories>
</system.data>
<oracle.manageddataaccess.client>
<version number="*">
<dataSources>
<dataSource alias="OracleDataSource"
descriptor="(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORC)))" />
</dataSources>
</version>
</oracle.manageddataaccess.client>
ODP.NET GAC (Global Assembly Cache)
Uninstall
Sometime you might get following error
Could not load type 'OracleInternal.Common.ConfigBaseClass' from assembly 'Oracle.ManagedDataAccess, Version=4.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342'.
This is not clear to me. Sometime I have to uninstall it, sometime I have to install it (due the error suggests something opposite).
I presume you have gacutil in your PATH. To check out, simply type gacutil /? to see available parameters. If you don’t, google for gacutil location and add it to your system variable PATH, or use gacutil with absolute path. Remember to open command window in Administrator mode.
gacutil /u Oracle.ManagedDataAccess
C:...packagesOracle.ManagedDataAccess.12.1.2400inx64
λ gacutil /u Oracle.ManagedDataAccess
Microsoft (R) .NET Global Assembly Cache Utility. Version 4.0.30319.33440
Copyright (c) Microsoft Corporation. All rights reserved.
Assembly: Oracle.ManagedDataAccess, Version=4.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342, processorArchitec
ture=MSIL
Uninstalled: Oracle.ManagedDataAccess, Version=4.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342, processorArchi
tecture=MSIL
Number of assemblies uninstalled = 1
Number of failures = 0
After that, reset IIS with iisreset to reload loaded assemblies and reload the page.
Install
Type is not resolved for member 'Oracle.ManagedDataAccess.Client.OracleException,Oracle.ManagedDataAccess, Version=4.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342'.
Generally speaking all you got to do is to install Oracle.ManagedDataAccess.dll to your GAC (Global Assembly Cache).
gacutil /i <dll_path>
Oracle.ManagedDataAccess.dll can be found in your nuget package lib directory (mine was .packagesOracle.ManagedDataAccess.12.1.2400lib
et40).
C:...packagesOracle.ManagedDataAccess.12.1.2400lib
et40> gacutil /i .Oracle.ManagedDataAccess.dll
Microsoft (R) .NET Global Assembly Cache Utility. Version 4.0.30319.33440
Copyright (c) Microsoft Corporation. All rights reserved.
Assembly successfully added to the cache
ref: Entity Framework Seed method exception
Bounty
Install and uninstall enable further work, but what really would do the trick is a proper configuration that resolves constant installing and uninstalling assemblies from cache. So if come up with any better solution, feel free to share.
Identity
In both 11g and 12c, GUID Identity columns in migration files must be replaced from
Id = c.Guid(nullable: false, identity: true),
to
Id = c.Guid(nullable: false, identity: false, defaultValueSql: "SYS_GUID()"),
This is for both 11g and 12c
Identity in 11g
11g does not offer autoincrement + uniquness feature (commonly known as Identity in MS SQL). EF handles generating next Identity values by incrementing sequence by a trigger. Sequences are good for numeric column types, but they don’t work well with GUIDs.
Change generated triggers to insert SYS_GUID() (which is NEWID() equivalent in MS SQL) or change C# migration.
Apply
"Oracle 11.2.0.2.0 does not support APPLY" exception
Error says everything. More robust EF queries are not supported on 11g. You can rewrite your query, but it’s just workaround, not a solution for this particular problem. Apply are supported from version 12c. This is the main reason we moved from 11g to 12c. Fortunately, client was moving to 12c as well.
ref: ODAC 11.2 Release 4 (11.2.0.3.0) throwing “Oracle 11.2.0.2.0 does not support APPLY” exception
Bulk inserts
Bulk inserts are possible with ODP.NET Unmanaged Driver.
Isolation levels
This one is quite important. In EF we can create transaction with specified Isolation level. Those differ in MS SQL and Oracle.
MS SQL 2014 isolation levels
READ UNCOMMITTEDREAD COMMITTEDREPEATABLE READSNAPSHOTSERIALIZABLE
Oracle 11g/12c isolation levels
READ UNCOMMITTEDREAD COMMITTEDREPEATABLE READSERIALIZABLE
I’m no Oracle expert, so if you want gain more knowledge about those, check out this awesome blog post. It helped me a lot in understanding differences in isolation levels between Oracle and MS SQL.
High Oracle Memory usage
High RAM Memory Consumed by Oracle 11g for Windows Server 2008
PL/SQL tips
clear screen;
SET FEEDBACK OFF;
/ and ; When do I need to use a semicolon vs a slash in Oracle SQL?
commit; commits changes on database. You have to put this after every inserting section in your script.
SID vs SERVICE_NAME
How SID is different from Service name in Oracle tnsnames.ora
Quartz.NET + Oracle
Some configuration need to be done before your scheduler will run on Oracle.
jobStore.driverDelegateType
properties["quartz.jobStore.driverDelegateType"] = "Quartz.Impl.AdoJobStore.OracleDelegate, Quartz";
http://stackoverflow.com/a/20343752/864968
dataSource.default.provider
["quartz.dataSource.default.provider"] = "OracleODPManaged-1211-40",
http://www.quartz-scheduler.net/documentation/quartz-2.x/tutorial/job-stores.html
Migrations
It is highly possible that you’re gonna mess around with migrations, DbContexts and entities. You might end up with changes in your model that will need new migration.
We had one case when after adding a migration (for MSSQL) we got a lot of table creates like this migration was the very first, but it wasn’t.
To avoid such issues with your model changes (when you’re absolutly sure there were none, like any table definition changes), add ContextKey and MigrationsNamespace to your MigrationConfiguration.
ContextKey is stored in __MigrationHistory table, created by EF migrator during first successful migration.
MigrationsNamespace is a bit different. It will appear in C# migrations. During migration on database those migrations transformed to xml (edmx), gzipped, compressed with “base64” and finally stored in __MigrationHistory table. This will cause differences in model.
Also, correct namespace is crucial while looking for proper migrations during EF migrations scan.
Compressed model can be easly decompressed. Check this link for more.
Can I get decode an EntityFramework Model from a specified migration?,
Entity Framework Migrations Rebuild Target Hash
More here:
Changing the Namespace With Entity Framework 6.0 Code First Databases,
Namespace changes with Entity Framework 6 migrations
Create table if does not exist
The easiest way is to handle it with try/catch section.
begin
execute immediate
'
create table "SCHEMA"."__MigrationHistory"
(
"MigrationId" nvarchar2(150) not null,
"ContextKey" nvarchar2(300) not null,
"Model" blob not null,
"ProductVersion" nvarchar2(32) not null,
constraint "PK___MigrationHistory" primary key("MigrationId", "ContextKey")
)
';
exception
when others then
if sqlcode <> -955 then
raise;
end if;
end;
/
source: http://stackoverflow.com/questions/15630771/check-table-exist-or-not-before-create-it-in-oracle
Drop all tables in schema
BEGIN
FOR cur_rec IN (SELECT object_name, object_type
FROM user_objects
WHERE object_type IN
('TABLE',
'VIEW',
'PACKAGE',
'PROCEDURE',
'FUNCTION',
'SEQUENCE'
))
LOOP
BEGIN
IF cur_rec.object_type = 'TABLE'
THEN
EXECUTE IMMEDIATE 'DROP '
|| cur_rec.object_type
|| ' "'
|| cur_rec.object_name
|| '" CASCADE CONSTRAINTS';
ELSE
EXECUTE IMMEDIATE 'DROP '
|| cur_rec.object_type
|| ' "'
|| cur_rec.object_name
|| '"';
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ( 'FAILED: DROP '
|| cur_rec.object_type
|| ' "'
|| cur_rec.object_name
|| '"'
);
END;
END LOOP;
END;
/
source: http://stackoverflow.com/a/1690419/864968
Inserting IDs to Identity columns
Sometime you have to insert row with specific ID. If your ID column is was defined with generated always as identity not null you will get the following exception: ORA-32795: cannot insert into a generated always identity column. This is the default way of generting scripts by EF btw.
You can replace this definition with genereted by default as identity on null. After doing this, you will be able to insert values to ID columns and nulls will be replaced with generated values.
Types mapping
Sometime we need to configure column types explicitly.
Extensions
We can use extensions.
String properties
using System;
using System.Data.Entity.ModelConfiguration.Configuration;
/// <summary>
/// String property configuration helpers.
/// More info about Oracle Data Provider mapping can be found here https://docs.oracle.com/cd/E63277_01/win.121/e63268/entityCodeFirst.htm
/// </summary>
public static class OracleStringPropertyConfigurationExtensions
{
/// <summary>
/// Configures string property for valid NVarChar2 column.
/// Sets max length and encoding.
/// </summary>
/// <param name="property">Property to be configured.</param>
/// <param name="maxLength">Column value max size. Size cannot be greater than 2000 bytes.</param>
/// <returns>Property configuration</returns>
public static StringPropertyConfiguration IsNVarChar2(this StringPropertyConfiguration property, int maxLength = NVARCHAR2_MAX)
{
if (property == null) throw new ArgumentNullException(nameof(property));
if (maxLength > NVARCHAR2_MAX) throw new ArgumentException($"Oracle nvarchar2 column accepts strings that are not greater than {NVARCHAR2_MAX}.");
property
.HasMaxLength(maxLength)
.IsUnicode(true);
return property;
}
/// <summary>
/// Configures string property for valid NVarChar2 column.
/// Sets max length and encoding.
/// </summary>
/// <param name="property">Property to be configured.</param>
/// <param name="maxLength">Column value max size. Size cannot be greater than 4000 bytes.</param>
/// <returns>Property configuration</returns>
public static StringPropertyConfiguration IsVarChar2(this StringPropertyConfiguration property, int maxLength = VARCHAR2_MAX)
{
if (property == null) throw new ArgumentNullException(nameof(property));
if (maxLength > VARCHAR2_MAX) throw new ArgumentException($"Oracle varchar2 column accepts strings that are not greater than {VARCHAR2_MAX}.");
property
.HasMaxLength(maxLength)
.IsUnicode(false);
return property;
}
/// <summary>
/// Configures string property for valid NVarChar2 column.
/// Sets max length and encoding.
/// </summary>
/// <param name="property">Property to be configured.</param>
/// <param name="maxLength">Column value max size. Size must be greater than 4000 bytes.</param>
/// <returns>Property configuration</returns>
public static StringPropertyConfiguration IsClob(this StringPropertyConfiguration property, int? maxLength = null)
{
if (property == null) throw new ArgumentNullException(nameof(property));
if (maxLength < CLOB_MIN) throw new ArgumentException($"To configure column as CLOB type set its max length to be greater or equal than {CLOB_MIN}.");
property.IsUnicode(false);
// String Length
if (maxLength.HasValue) property.HasMaxLength(maxLength);
else property.IsMaxLength();
return property;
}
/// <summary>
/// Configures string property for valid NVarChar2 column.
/// Sets max length and encoding.
/// </summary>
/// <param name="property">Property to be configured.</param>
/// <param name="maxLength">Column value max size. Size must be greater than 4000 bytes.</param>
/// <returns>Property configuration</returns>
public static StringPropertyConfiguration IsNClob(this StringPropertyConfiguration property, int? maxLength = null)
{
if (property