Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Integration guide to use Oracle with ABP Framework

HOW TO INTEGRATE ABP PROJECT WITH ORACLE?

Step 1: Manage packages

Step 1.1

Open src\Acme.OracleApp.DbMigrator\Acme.OracleApp.DbMigrator.csproj and update Microsoft.EntityFrameworkCore.Tools from v3.1.2 to v3.1.3

Step 1.2

Open src\Acme.OracleApp.EntityFrameworkCore.DbMigrations\Acme.OracleApp.EntityFrameworkCore.DbMigrations.csproj and update Microsoft.EntityFrameworkCore.Tools and Microsoft.EntityFrameworkCore.Design from v3.1.2 to v3.1.3.

Step 1.3

The existing template contains EF Core SQL Server provider. First remove this package then add a new EF Core Oracle provider.

Notice that, there are 2 Oracle providers for EF Core.

  1. The first one is from the Oracle vendor. The Oracle.EntityFrameworkCore package works only with EF Core 2.1. ABP framework uses the latest EF Core (3.1). Therefore this package is not eligible.
  2. The second one is from Devart vendor. The Devart.Data.Oracle.EFCore is a paid package. It works with the latest EF Core (3.1). So we will use this package. To be able to use it, you need to install the application https://www.devart.com/dotconnect/oracle/download.html, even you reference it from NuGet.

Remove the Volo.Abp.EntityFrameworkCore.SqlServer package, by deleting the below line:

<PackageReference Include="Volo.Abp.EntityFrameworkCore.SqlServer" Version="2.7.0"/>

Add the Devart.Data.Oracle.EFCore line, by adding the below line:

<PackageReference Include="Devart.Data.Oracle.EFCore" Version="9.11.980"/>

Step 2: Set Database Provider

Open src\Acme.OracleApp.EntityFrameworkCore.DbMigrations\EntityFrameworkCore\OracleAppMigrationsDbContext.cs

Remove this line builder.ConfigureIdentityServer(); Add the below instead:

builder.ConfigureIdentityServer(options =>
{
	options.DatabaseProvider = EfCoreDatabaseProvider.Oracle;
});    

Step 3: Use Oracle

Open src\Acme.OracleApp.EntityFrameworkCore.DbMigrations\EntityFrameworkCore\OracleAppMigrationsDbContextFactory.cs

remove the below

var builder = new DbContextOptionsBuilder<OracleAppMigrationsDbContext>()
                .UseSqlServer(configuration.GetConnectionString("Default"));

add the below

DbContextOptionsBuilder<OracleAppMigrationsDbContext> builder = (DbContextOptionsBuilder<OracleAppMigrationsDbContext>)
new DbContextOptionsBuilder<OracleAppMigrationsDbContext>().UseOracle(configuration.GetConnectionString("Default"));

Step 4: Manage module dependency

Open src\Acme.OracleApp.EntityFrameworkCore\EntityFrameworkCore\OracleAppEntityFrameworkCoreModule.cs

Remove the below module dependency

typeof(AbpEntityFrameworkCoreSqlServerModule)

Remove the below line from ConfigureServices()

remove options.UseSqlServer(); 

Add the below code into ConfigureServices()

options.Configure(ctx =>
{
    ctx.DbContextOptions.UseOracle(ctx.ConnectionString);  
});

Step 5: Set connection strings

Replace connection strings in all appsettings.json files according to Oracle connection string format:

Data Source=localhost;User Id=myuser;Password=mypassword;

Step delete the folder src\Acme.OracleApp.EntityFrameworkCore.DbMigrations\Migrations

Step 6 (Optional): Create Oracle user

Oracle uses User Id as scheme/database. You need to create a new user (scheme/database) in your Oracle database. To do this, run the below commands in Oracle to create a new schema (user/database)

ALTER SESSION SET "_ORACLE_SCRIPT"= true;
CREATE USER myuser IDENTIFIED BY "mypassword";
GRANT ALL PRIVILEGES TO myuser

If you don't have a SQL client to run the queries, you can use DBeaver. It is a SQL client software application and a database administration tool, supports Oracle.

Step 7: Create migrations

Step 7.1: Delete existing migrations

Existing migrations are created for the SQL database, therefore you need to delete them.

To do this delete the below folder:

src\Acme.OracleApp.EntityFrameworkCore.DbMigrations\Migrations

Step 7.2: Add initial migrations

To create the initial migrations, open Package Manager Console in Visual Studio and select Acme.OracleApp.EntityFrameworkCore.DbMigrations as the default project. Then run the below command:

add-migration

Step 8: Adjust column sizes for Oracle

Oracle has some restrictions and limitations on some data types. Hence, some of the initial migrations need fine tuning to work with Oracle database. (See Oracle's data types documentation and data type limits)

To fix this, open the first migration file:

src\Acme.OracleApp.EntityFrameworkCore.DbMigrations\Migrations\XXXXXXXXX_Initial.cs

Change these oversize column definitions in the generated migration file:

Table Property Old value New value
AbpAuditLogs Exceptions 4000 2000
AbpSettings Value 2048 2000
IdentityServerApiSecrets Value 4000 2000
IdentityServerClientSecrets Value 4000 2000

Also, remove default value of AbpUsers table AccessFailedCount column, to do this;

Find the below line

AccessFailedCount = table.Column<int>(nullable: false, defaultValue: 0);

change to

AccessFailedCount = table.Column<int>(nullable: false);

Step 9: Comment out ExistingConnection

Comment out the below section because Devart Oracle provider throws an exception when using context.ExistingConnection

Screenshot

Note that, this issue will be fixed in the next versions, and you will not need to apply the step-8 anymore.

Step 10 (Optional): Start your Oracle Database:

When you restart Windows, or don't use the Oracle server for a long time, it suspends database. To start it up you can run the following in command line

sqlplus sys as sysdba
startup

or you can run this

sqlplus myuser/mypassword@localhost

That's all! Now you can try out your project with Oracle database.

@hikalkan

This comment has been minimized.

Copy link

hikalkan commented May 28, 2020

Step 2: Set Database Provider is not needed. It is only needed for MySQL. For other providers leave it as default.

@hikalkan

This comment has been minimized.

Copy link

hikalkan commented May 29, 2020

You can use the new Oracle integration package beginning from v2.9: https://github.com/abpframework/abp/blob/dev/docs/en/Entity-Framework-Core-Oracle.md

@dicksonkimeu

This comment has been minimized.

Copy link

dicksonkimeu commented May 31, 2020

This package will work with the Oracle.EntityFrameworkCore too ?

@hikalkan

This comment has been minimized.

Copy link

hikalkan commented May 31, 2020

It is currently in beta (https://www.nuget.org/packages/Oracle.EntityFrameworkCore/3.19.0-beta1), just released 2 days ago.
This GIST is not for this package, but I created an issue to integrate to Oracle.EntityFrameworkCore in the next versions (follow abpframework/abp#4160).

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.