HOW TO INTEGRATE ABP PROJECT WITH ORACLE?

There are 2 packages for ABP Oracle integration

1- Oracle (ODP.NET) Official integration

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.

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.
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

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.

ebicoglu/abp-oracle-integration.md