Oracle provider

[jpiquot]

Hi Sebastien,

As we need an Oracle provider for YesSql (to be used with OrchardCore). We can write the Oracle provider so you can add it to the YesSql project, if you agree.

[hishamco]

Is there an Oracle implementation that support .NET Core?

[jpiquot]

Hi, No, nothing official from oracle yet. You have to target net461 or use the .Net Core Provider for Oracle from DevArt

[hishamco]

Sometimes net461 isn't the right option for all the projects, and regarding DevArt Oracle provider i'm not sure if it's free & open source or not

[jpiquot]

I don't understand why Oracle does not support .Net Core. They did not even take the time to update the roadmap since march 2017.

[alexbocharov]

Need an official provider from Oracle (free & open source)

[jpiquot]

Is there an issue to build YesSql with target net461? EF Core team are working on Oracle EF Core support but only target .NET until the .NET Core driver will be available. Can we do the same?

[alexbocharov]

Yes, using Oracle.Data.Access (free). But is it worth it... Who considers it necessary?

[alexbocharov]

I was waiting for the official support of .NET Core from Oracle

[beppler]

Hi.

Oracle launched the .NET Core provider.

It can be downloaded on nuget.

[sebastienros]

Is there a docker image running on Windows that can be used to test it? I would not commit to it if there is no way to test it works.

[beppler]

Oracle has an registry with standard and enterprise editions that can be used for testing environments.

Enterprise edition is available on docker store.

[mguzzardi]

Hi,

I started an Oracle Provider (v12c only) https://github.com/mguzzardi/yessql

I got 82 tests Passed and 21 Failed.

I'm struggling with "IN" Operator and boolean datatype. I need to look how dapper work with Oracle.

For information, a non exhaustive list of differences for Oracle:

• empty string is equivalent to NULL : empty string is replaced by "null"
• parameters/bind variables : must be prefixed by ":" instead of "@"
• DBCommand must be configured to BindByName=true (quick and dirty fix in my code)
• end of sql statement : semicolon not allowed
• "returning into" : I needed to write PL/SQL code to do that
• Oracle Database does not support the creation of indexes for primary keys, since an index is implicitly created for all primary keys (same for unique key) : not fixed

[pbros]

Hello,

I'd be interested in using Oracle with YesSQL (through OrchardCore). Has any progress been made?

Thanks

[jpiquot]

Hi @pbros,

Using Oracle with .NET Core is a real risk today as Oracle does not invest much on .NET Core. As an example, see the EF Core driver that has not been released. If you want to use Oracle, it may be better to consider Java.

YesSQL, like many libraries in the .NET world that need persistence, support many databases, but not Oracle. Or if they support it, say that it's not tested or like Dapper tested but with many bugs remaining.

[pbros]

Hi @jpiquot ,

While I can't speak to how much Oracle invests in .NET Core, they do seem committed to supporting it (Twitter: https://twitter.com/oracledotnet, Github: https://github.com/oracle/dotnet-db-samples). I've been following what they are doing for about a year, and while they might not be moving very quickly, they are delivering their products in a logical way. They first first released the .NET Core Managed Client, which allows us to easily use Dapper (using https://www.nuget.org/packages/Dapper.Oracle/ https://github.com/DIPSAS/Dapper.Oracle). Once that was released and stable, they started working on their EF implementation, which is based on their Managed Client, and is currently in their 3rd and final beta. Production release is for later this year. Some of the challenges they are working on is more in relation to some of the object creations related to CodeFirst and DatabaseFirst automations, whereas the querying seems to be mostly worked out (I didn't find any bugs in the latest beta..)

You can see more information about their announcements here: https://www.oracle.com/technetwork/topics/dotnet/latest-news/index.html

From my understanding, since YesSql is using Dapper as its ORM, the onus of supporting Oracle should be more on https://github.com/DIPSAS/Dapper.Oracle than YesSql. It seems like one of the objectives of YesSql to be database agnostic, which supporting Oracle will continue to expand. It also brings support for more Enterprise level database engines.

@alexkeh is one of the .NET managers at Oracle, and from what I can tell, they seem very open to received feedback in regards to bugs or issues.

[jpiquot]

Hi @pbros,

My feedback was not really based on commercial announcements, but on all the issues we had in our project. The customer wanted to keep Oracle as the database as they are using it for many years. Now in the staging process we see that the performance gap between SQL and Oracle is a blocker. Oracle is loosing a big customer that is considering moving to SQL due to the lack of support of Oracle in the .NET world.

The Oracle EF Core driver is far from being in a final release. We tried to replace the DevArt driver with it, but there are too many missing parts. I don't think that being 3 years late is very "logical". How many years we will need to wait for the next version of .NET in a few month's?

YesSql is relational database agnostic, but not the underlying providers. We tried to write the Oracle support for YesSql but there are issues in Dapper's Oracle provider that can't be solved easily.

If Oracle is open to feedback, why a private beta program, why not an open source version of the driver?

[alexkeh]

@jpiquot Oracle's target is to ship the production Oracle EF Core version this quarter, though there's a chance it could slip into early Q3. It really depends on what we hear back from customer beta testing. As anxious as customers are for a production version, we're trying to let product quality dictate the release schedule more than a fixed date.

To explain the wait for the Oracle .NET Core and EF Core providers, we evaluated what we needed to do and what the functionality of the .NET and EF Core platforms themselves. We provided feedback to Microsoft on our feature requirements. Microsoft delivered that functionality promptly in Core 2.0 and 2.1, which allowed Oracle to first deliver a .NET Core provider and the current beta EF Core provider. You'll notice Oracle minimum provider requirements is 2.1 and higher.

You asked why Oracle didn't deliver something sooner, like what DevArt did. Well, your experience with DevArt is exactly why: too many missing parts and a poor experience. In fairness to DevArt, some of that experience may be due to missing features in the platform that Microsoft has been adding with every new release. And in fairness to Microsoft, I'm sure they were inundated (and continue to be) with feature requests as any new framework must try to catch up with functionality of existing mature frameworks.

Oracle began with private betas for both Core releases in order to work closely with a small set of customers for a couple of reasons. These were completely new providers being delivered. We tested the providers thoroughly, but you never truly know with a new product until you release it to customers for the first time. Working with a small group let us work closely with these customers. The second major reason is that we didn't deliver a complete EF Core tracing capability until the public beta.

Oracle doesn't open source ODP.NET because it is bundled with Oracle DB commercial products. Oracle would have to be very, very careful about the open source contributions it accepted from the community. For example, accepting GPL code, code subject to someone else's patent, or code that the contributor did not have the rights to, has significant legal and financial implications for the commercial product. Oracle is responsible for catching these issues, not the contributor.

Sorry to hijack the thread! I just wanted to address the concerns you had.

[jpiquot]

@alexkeh , thanks for hijacking the thread. I am happy to have more informations on the driver progress.

I did not say that DevArt had too many missing parts. I was saying that on the Oracle beta. I agree that what I wrote is ambiguous.

One of our options was to use PostgreSQL and I was really surprised by the performance tests and the quality of the EF driver. So how can an open source database can do better on his EF driver that a very expensive commercial product?

The customer will make his decision this week, to move or not from Oracle to Microsoft SQL Server. I think that Q3 may be too late for them. Will the driver be maintained and migrated for each new version of .Net? What is the roadmap for EF 3.0?

Regards, Jérôme

[alexkeh]

@jpiquot Let me know what is missing from the current Oracle beta. We want to hear your feedback.

I'm now curious why you didn't end up using DevArt if it had all the functionality you needed in EF Core. What made you abandon DevArt?

With respect to performance tests, there are large volumes of books written on DB app performance tuning. Out of the box experience can be misleading as DBs and providers are configured for varying default experiences. Everything from sizing hardware, network, DB, application, storage, etc. could be a potential bottleneck. For example, I've solved performance issues for customers getting them to increase their Oracle network packet size. You may ask why doesn't Oracle just make the packet size as large as possible then. Well, doing so would lead to large memory consumption with much of it wasted if end users were querying small amounts of data. Then, people would be happy that Oracle was faster, then complain why Oracle was consuming more memory. :-)

I can't explain your specific performance experience without more details about the performance tests and configuration. A good tool to run is Oracle Performance Analyzer, which is part of the free Oracle Developer Tools for Visual Studio in ODAC. It does a great job of identifying bottlenecks as you run your app and making specific recommendations to fix them.

However, some of the bottlenecks may be due to inefficient SQL that the tools may ask you to modify. Unfortunately, there's not a lot you can do about optimizing LINQ, though you can replace LINQ-generated SQL with your own performance tuned SQL.

In recent years, Oracle has focused more on self-tuning innovations and architecture optimizations so that you don't have spend hours tuning, such as Exadata, automatic memory management, automatic SQL tuning, etc. All these technologies have culminated in the Oracle Autonomous Database in which the DB itself will simply auto-tune, auto-patch, and auto-secure itself. The auto-tuning works by sampling your workload, then performing optimizations without any human intervention in real-time.

For ODP.NET EF Core, we plan to support EF Core 3.0 in likely the second production release. The first production release will focus purely on support EF Core 2.x.

[jpiquot]

@alexkeh

We use DateTimeOffset types and HasIndex(), that are not supported by the Oracle beta.

The main issue with DevArt was bulk writes that makes 1000 seperate inserts instead of a single merge of all the rows like Sql Server driver. There is also a little bug on migrations where we need to modify alternate keys before applying the script.

Development and Integration environment are on Sql server and Staging on Oracle. The user interface is more responsive on Sql Server instances than the Oracle staging environment.

We use Orleans Actor Model Framework that makes parallel calls. With Sql Server making 10.000 concurrent read + writes was less than 1 min. We could not do the test on Oracle as the DBA said that it would crash the Oracle instance.

[alexkeh]

@jpiquot DateTimeOffset support will be included in the next release. We've already implemented it, just waiting for the next release vehicle.

HasIndex() cannot be invoked on a primary key because Oracle already implicitly produces an index for primary keys. As such, you don't need to use HasIndex() with primary keys.

Oracle DB can definitely handle 10K+/min. concurrent operations. The DB, OS, and hardware still need to be tuned and sized correctly for high throughput.

[pbros]

Hi @jpiquot ,

I was wondering if any progress had been made regarding Oracle support for Yessql following the comments from @alexkeh .

Thanks

[mazuryv]

Our team has finished Oracle provider support for YesSql. @mguzzardi Thank you for your ideas. @pbros @jpiquot Link https://github.com/mazuryv/yessql Pull request https://github.com/sebastienros/yessql/pull/224

[DotCat1985]

Our team has finished Oracle provider support for YesSql. @mguzzardi Thank you for your ideas. @pbros @jpiquot Link https://github.com/mazuryv/yessql Pull request #224

Hi @mazuryv, which Oracle versions are supported by your fork? How can I include in an OrchardCore application?

[mazuryv]

Our team has finished Oracle provider support for YesSql. @mguzzardi Thank you for your ideas. @pbros @jpiquot Link https://github.com/mazuryv/yessql Pull request #224

Hi @mazuryv, which Oracle versions are supported by your fork? How can I include in an OrchardCore application?

Hello. I included this code as part of Orchard.Core CMS, replaced nuget reference to project reference. Published me packages of Orchard.Core add new provider: /features/Portal/Orchard.Core /src/OrchardCore/OrchardCore.Data/OrchardCoreBuilderExtensions.cs services.TryAddDataProvider(name: "Oracle", value: "Oracle", hasConnectionString: true, hasTablePrefix: true, isDefault: false); //add new dataProvider to list ... case "Oracle": storeConfiguration .UseOracle(shellSettings["ConnectionString"], IsolationLevel.ReadCommitted) .UseBlockIdGenerator(); break;

[DotCat1985]

Our team has finished Oracle provider support for YesSql. @mguzzardi Thank you for your ideas. @pbros @jpiquot Link https://github.com/mazuryv/yessql Pull request #224

Hi @mazuryv, which Oracle versions are supported by your fork? How can I include in an OrchardCore application?

Hello. I included this code as part of Orchard.Core CMS, replaced nuget reference to project reference. Published me packages of Orchard.Core add new provider: /features/Portal/Orchard.Core /src/OrchardCore/OrchardCore.Data/OrchardCoreBuilderExtensions.cs services.TryAddDataProvider(name: "Oracle", value: "Oracle", hasConnectionString: true, hasTablePrefix: true, isDefault: false); //add new dataProvider to list ... case "Oracle": storeConfiguration .UseOracle(shellSettings["ConnectionString"], IsolationLevel.ReadCommitted) .UseBlockIdGenerator(); break;

Did you have any issue about performance and stability? Which Oracle releases are supported?

[mazuryv]

Our team has finished Oracle provider support for YesSql. @mguzzardi Thank you for your ideas. @pbros @jpiquot Link https://github.com/mazuryv/yessql Pull request #224

Hi @mazuryv, which Oracle versions are supported by your fork? How can I include in an OrchardCore application?

Hello. I included this code as part of Orchard.Core CMS, replaced nuget reference to project reference. Published me packages of Orchard.Core add new provider: /features/Portal/Orchard.Core /src/OrchardCore/OrchardCore.Data/OrchardCoreBuilderExtensions.cs services.TryAddDataProvider(name: "Oracle", value: "Oracle", hasConnectionString: true, hasTablePrefix: true, isDefault: false); //add new dataProvider to list ... case "Oracle": storeConfiguration .UseOracle(shellSettings["ConnectionString"], IsolationLevel.ReadCommitted) .UseBlockIdGenerator(); break;

Did you have any issue about performance and stability? Which Oracle releases are supported?

All Orchard feature is supported. Tested at commercial product during year! Oracle 12.2

[mazuryv]

New version Oracle provider mazuryv:master https://github.com/mazuryv/yessql/tree/master

[hishamco]

FYI https://medium.com/@alex.keh/announcing-oracle-entity-framework-core-8-19a084d0e20b

[hishamco]

@mazuryv did you test Oracle against Docker image or local instance?