Database specific features for Oracle

This section illustrates the database specific features for Oracle which are available to you through configuration, either through the .config file of your application or through code.

See the Application configuration through .config files section for the details about the configuration settings defined through .config files.

See the RuntimeConfiguration system section for details about configuring the settings through code.

Ansi joins

By default, the Oracle DQEs will use Ansi joins, i.e. LEFT/INNER/RIGHT OUTER JOIN syntax. To switch this off,  and use join syntaxis like SELECT .. FROM A, B, C, you can switch this off by using one of the following:

1. A setting in the application's .config file. Please see: Generated code - Application configuration through .config files for the details.

2. Use the DQE Configuration of the RuntimeConfiguration system. Please see Runtime Configuration - Oracle - Disabling Ansi joins

Trigger based sequence values

It can be that your project's Oracle database schema is used by multiple applications, among them your LLBLGen Pro based software. This can give the situation that you've to deal with the situation that the schema is configured to use triggers to insert sequence values on row insert. To tell the Oracle DQE DQE of choice that this is the case, and thus that it shouldn't ask for a new sequence value when a new entity is inserted, you've to add a setting to the application's .config file. Please see: Generated code - Application configuration through .config files for the details.

Oracle compatibility mode

The Oracle DQEs (both ODP.NET and MS Oracle) support a wide range of Oracle versions, from 9i to 12c and higher. To enable your application to use specific features of the used Oracle version, the Oracle compatibility mode.

You can set the Oracle DQE's compatibility mode in two different ways: using the application's .config file or use a code statement. For the application's .config file method, please see Generated code - Application configuration through .config files. For using the code method, please see for SelfServicing: Generated code - CommonDaoBase functionality and for Adapter: Generated code - DataAccessAdapter functionality. For the RuntimeConfiguration method, please see Runtime Configuration - Oracle - Set default compatibility level.

By default the compatibility mode is set to Oracle 9i, 10g, 11g.

The differences per compatibility mode

• Oracle 9i, 10g, 11g. Value 0, or OracleCompatibilityLevel.Oracle9i10g11g. Default, generates paging queries with rownum filters. Generates separate sequence retrieval queries for obtaining sequence values during inserts.
• Oracle 12c. Value 1, or OracleCompatibilityLevel.Oracle12c. Generates OFFSET FETCH ONLY paging queries which are more efficient than rownum queries and don't run into the problem with nesting through too much wrapping brackets. Also supports identity fields with default sequences in this mode by omiting the sequence retrieval query for identity fields. This compatibility mode is recommended for Oracle 12c or higher.

Sequence value query batching

In insert queries, sequence value queries are batched together with the insert statement into one command, using wrapping BEGIN, END statements.

ODP.NET Managed provider support (ODP.NET v12+)

By default, the first factory the ODP.NET DQE will look for will be Oracle.ManagedDataAccess.Client, which is the managed provider shipped in the 12c ODP.NET version. If this factory isn't found, the Oracle.DataAccess.Client factory is tried. If that one isn't found either, the DQE/driver will give up.

.NET Standard 2.0+/.NET Core support

Oracle is a supported database for the LLBLGen Pro Runtime Framework on .NET Standard 2.0. To use Oracle with LLBLGen Pro on .NET standard 2.0, generate code for .NET Standard 2.0 from your ODP.NET using LLBLGen Pro project and add a reference to the Oracle.ManagedDataAccess.Core package from NuGet (with version v2.18.0 or higher) to your main project, which use the generated code project(s).

As the .NET Standard variant of ODP.NET is the managed provider, The RuntimeConfiguration settings' method SetAlwaysChooseUnmanagedProviderwhich allow you to select the unmanaged provider isn't available for the .NET Standard 2.0 builds of the ODP.NET DQE.

When registering the ADO.NET Factory using RuntimeConfiguration, you should choose for invariant name "Oracle.ManagedDataAccess.Client". When using TSN names, it's key to define these again using the OracleConfiguration class for .NET Core, as there's no CLI/tsnnames.ora file. For an example how to do that, please see the dotnet examples for ODP.NET.

Setting to always pick unmanaged propvider

In some situations it might be necessary to choose the unmanaged provider, as XmlType isn't supported on the managed provider, and as the managed provider is chosen by default, this could lead to a catch-22, because ODP.NET installs both the unmanaged and the managed provider.

To make sure the unmanaged provider is chosen at runtime, use one of the following:

1. Use the DQE Configuration of the RuntimeConfiguration system. Please see Runtime Configuration - Oracle - Always use the unmanaged DbProviderFactory

2. Add ODPNETAlwaysChooseUnmanagedProvider as setting in the application's appSettings section in the application's .config file with the value true as shown below.

<add key="ODPNETAlwaysChooseUnmanagedProvider" value="true"/>

By default this setting is false. If it's ok to use the managed provider, it's not required to specify the setting.