ebicoglu/abp-oracle-integration.md

## abp-oracle-integration.md

      
    Raw
  

              abp-oracle-integration.md
            
          
    HOW TO INTEGRATE ABP PROJECT WITH ORACLE?

There are 2 packages for ABP Oracle integration
1- Oracle (ODP.NET) Official integration
2- Oracle Devart 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.
Table	Property	Old value	New value
AbpAuditLogs	Exceptions	4000	2000
AbpSettings	Value	2048	2000
IdentityServerApiSecrets	Value	4000	2000
IdentityServerClientSecrets	Value	4000	2000