oracle/dotnet-db-samples

.NET code samples for Oracle database developers #OracleDotNet

Oracle .NET Code Sample Repository

About Oracle .NET

This repository provides .NET code samples for Oracle developers, specifically for Oracle Data Provider for .NET (ODP.NET) and other Oracle .NET components. .NET products for Oracle Database are free of charge. They consist of:

• Oracle Data Provider for .NET (ODP.NET) - an optimized ADO.NET data access provider to the Oracle database. ODP.NET allows developers to take advantage of advanced Oracle database functionality, including Real Application Clusters, XML DB, and self-updating client caches. ODP.NET offers three provider types: Core for .NET (Core) runtimes, managed for .NET Framework (100% managed code and easy deployment), and unmanaged for .NET Framework.
• Oracle Developer Tools for Visual Studio - a tightly integrated design-time "Add-in" for Microsoft Visual Studio that provides full Oracle .NET application lifecycle management, including PL/SQL debugging and schema comparison tools.
• Oracle Developer Tools for VS Code - enables Visual Studio Code developers to connect to Oracle Database and Oracle Autonomous Database, edit SQL and PL/SQL with autocomplete, Intellisense, breadcrumbs, and syntax coloring, execute SQL and PL/SQL and view and save the results in formats such as .CSV and JSON. The Oracle Database Explorer tree control allows you to quickly explore your database schema, view table data, and edit, execute and save PL/SQL.
• Oracle Providers for ASP.NET - a collection of ASP.NET providers that use Oracle Database as the data source. ASP.NET applications can store various types of website state in an Oracle database, which can then be shared across the application.

Getting Started

Oracle .NET components are available individually on NuGet Gallery or bundled together as part of Oracle Data Access Components (ODAC). Provider downloads are available from NuGet Gallery and Oracle .NET download page. Oracle Developer Tools for VS Code or Visual Studio can be downloaded from the VS Code Marketplace or Visual Studio Marketplace, respectively.

Tutorials

For beginning Oracle .NET developers, these on-premises .NET database tutorials and Autonomous Database .NET tutorials will assist you in getting started with .NET application development with Oracle Database.

Oracle .NET video tutorials are available from the Oracle .NET YouTube site.

For those that want to build an end-to-end basic .NET web app for Oracle Database in the cloud, try the .NET Development with Oracle Autonomous Database Quick Start. The tutorial takes about an hour to complete and is free. By the end, you will have a complete running .NET website and database.

More Information

• Documentation
• Contibutions to code samples should review CONTRIBUTING for more details.
• You may use files in this repository only if in compliance with the LICENSE.
• More Oracle .NET development information can be found at the Oracle .NET Developer Center.

Contributing

This project welcomes contributions from the community. Before submitting a pull request, please review our contribution guide

Security

Please consult the security guide for our responsible security vulnerability disclosure process

License

Copyright (c) 2015, 2023 Oracle and/or its affiliates.

Released under the MIT License

dotnet-db-samples's Issues

How to use in asp.net core

Can you tell me how to use in asp.net core 2?

How to Associate Sequences with Tables？

I used codefrist to generate tables and primary keys, and automatically generated new sequences for each primary key.
Like this：

But I added the code as follows:
using (BloggingContext db = new BloggingContext()) { db.Blogs.Add(new Blog { Url = "aaaaa1" }); db.SaveChanges(); }
I will get an error message:
Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index

Because I didn't copy the primary key。
I found the sequence of ForOracleUseSequenceHiLo method settings
like this
entity.Property(o => o.Id).ForOracleUseSequenceHiLo("aa_sq");

But I can't determine the automatically generated sequence name. Or do you have a better way to connect directly?

Oracle.EntityFrameworkCore support for EF Core 3.x

Does Oracle.EntityFrameworkCore support net core 3.0 ?

Scaffold-DbContext Error

EF Core support

If the EF Core support is not there yet, please mention it in the sample. It has been long since the support was supposed to be there. The readme conveys that EF core is there which is misleading to audience.

dotnet core 3 support

dotnet core version: 3.0.0-preview5-19227-01
ef core version: 3.0.0-preview5.19227.1
Oracle.EntityFrameworkCore version: 2.19.0-beta4

in Startup.cs

services.AddDbContext<DbContext>(opt => opt.UseOracle("User Id=user1;Password=123456;Data Source=127.0.0.1:1521/ORCLPDB1"));

got an error when doing a simple query

An unhandled exception has occurred while executing the request.
System.TypeLoadException: Method 'PopulateDebugInfo' in type 'Oracle.EntityFrameworkCore.Infrastructure.Internal.OracleOptionsExtension' from assembly 'Oracle.EntityFrameworkCore, Version=2.0.19.0, Culture=neutral, PublicKeyToken=null' does not have an implementation.

Is this a Oracle.EntityFrameworkCore but? when will it support dotnet core 3.

I found a similar discussion about Devart.Data.Oracle at https://forums.devart.com/viewtopic.php?t=36091

Failure to update model from oracle database using EF 5 and VS 2017

I have a C# application using Oracle database and Entity Framework 5. Oracle client is version 12c. My application uses database first approach. I'm trying to run the app using Visual Studio Professional 2017. When I access the edmx file and I try to update the model from the database, it gives me the following error

exception of type 'System.ArgumentException' occurred while attempting to update from the database. The exception message is: 'Unable to convert runtime connection string to its design-time equivalent. The libraries required to enable Visual Studio to communicate with the database for design purposes (DDEX provider) are not installed for provider 'Oracle.DataAccess.Client'. Connection string: XXXXX.

i have already installed oracle developer tool for visual studio 2017
Please help me to solve this issue

OracleParameter object is already contained in a collection

I was doing some testing, and I found that little problem.
When using "Parameters.Add".

The "Parameters.AddRange" works perfectly!

NOTE: The Inserir_Parametro_Um_Por_Um test fails!

using Oracle.ManagedDataAccess.Client;
using System;
using System.Data;
using Xunit;

namespace OracleTestesNetCore
{
    public class Testes
    {
        private static OracleConnection _oracleConnection = null;
        private static readonly OracleParameter[] _parametros = new[]
        {
            new OracleParameter
            {
                    OracleDbType = OracleDbType.Varchar2,
                    ParameterName="DESCRICAO",
                    Value = "DESCRICAO TESTE"
            },
            new OracleParameter
            {
                    OracleDbType = OracleDbType.Decimal,
                    ParameterName="VALOR",
                    Value = 25.99m
            },
            new OracleParameter
            {
                    OracleDbType = OracleDbType.Date,
                    ParameterName="DATA",
                    Value = DateTime.Now
            }
        };

        public Testes()
        {
            // Efetuar conexão!
            _oracleConnection = new OracleConnection
            {
                ConnectionString = new OracleConnectionStringBuilder
                {
                    UserID = "ralms_pdb_admin",
                    Password = "ralms_pdb_admin",
                    DataSource = "127.0.0.1:1521/ralms"
                }.ConnectionString
            };

            _oracleConnection.Open();

            // Inicializar Banco
            InicializarBanco();
        }

        [Fact]
        public void Inserir_Parametro_Um_Por_Um()
        {
            ExecutarComando("DELETE FROM \"TESTE\"");

            for (int i = 0; i < 3; i++)
            {
               // First time, run normally, second failure!
               // OracleParameter object is already contained in a collection

                ExecutarComando(
                    $"INSERT INTO \"TESTE\" (DESCRICAO,VALOR,DATA) VALUES (:DESCRICAO,:VALOR,:DATA)",
                    _parametros);
            }

            var dados = GetDados("SELECT * FROM \"TESTE\"");

            Assert.True(dados.Rows.Count == 3);
        }

        [Fact]
        public void Inserir_Parametro_Range()
        {
            ExecutarComando("DELETE FROM \"TESTE\"");

            for (int i = 0; i < 3; i++)
            {
                ExecutarComando(
                    $"INSERT INTO \"TESTE\" (DESCRICAO,VALOR,DATA) VALUES (:DESCRICAO,:VALOR,:DATA)",
                    _parametros,
                    true);
            }

            var dados = GetDados("SELECT * FROM \"TESTE\"");

            Assert.True(dados.Rows.Count == 3);
        }

        [Fact]
        public void GetTabelas()
        {
            var dados = GetDados("SELECT * FROM ALL_TABLES");

            Assert.True(dados.Rows.Count > 0);
        }

        private static void InicializarBanco()
        {
            ExecutarComando(
@"BEGIN
  EXECUTE IMMEDIATE 'CREATE TABLE ""TESTE""(
    ""ID"" NUMBER(38, 0) GENERATED BY DEFAULT AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  NOT NULL ENABLE,
    ""DESCRICAO"" NVARCHAR2(100),
    ""DATA"" DATE,
    ""VALOR"" NUMBER(18, 2)
)';
EXCEPTION
WHEN OTHERS THEN
  IF(SQLCODE != -942)THEN
      EXECUTE IMMEDIATE 'DELETE FROM ""TESTE""';
  END IF;
END;");

        }

        private static void ExecutarComando(
            string comando,
            OracleParameter[] parametros = null,
            bool usarRange = false)
        {
            using (var cmd = _oracleConnection.CreateCommand())
            {
                cmd.CommandText = comando;

                if (parametros != null)
                {
                    if (usarRange)
                    {
                        cmd.Parameters.AddRange(parametros);
                    }
                    else
                    {
                        for (int i = 0; i < parametros.Length; i++)
                        {
                            cmd.Parameters.Add(parametros[i]);
                        }
                    }
                }
                cmd.ExecuteNonQuery();
            }
        }

        private static DataTable GetDados(string comando)
        {
            var dados = new DataTable();
            using (var cmd = _oracleConnection.CreateCommand())
            {
                cmd.CommandText = comando;
                dados.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection));
                return dados;
            }
        }
    }
}

Scaffold-DbContext generate type problem

DDL:

CREATE TABLE "TABLE1" (
    "KEY1"    VARCHAR2(20 CHAR),
    -- sbyte, -128 to 127
    "NUM2"    NUMBER(2, 0),
    -- short, -32,768 to 32,767
    "NUM3"    NUMBER(3, 0),
    -- short, -32,768 to 32,767
    "NUM4"    NUMBER(4, 0),
    -- int, -2,147,483,648 to 2,147,483,647
    "NUM5"    NUMBER(5, 0),
    -- int, -2,147,483,648 to 2,147,483,647
    "NUM9"    NUMBER(9, 0),
    -- long, -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
    "NUM10"   NUMBER(10, 0)
);

Integral numeric types

Scaffold-DbContext

public partial class TABLE1
{
    public string KEY1 { get; set; }
    public byte? NUM2 { get; set; }
    public byte? NUM3 { get; set; }
    public byte? NUM4 { get; set; }
    public short? NUM5 { get; set; }
    public int? NUM9 { get; set; }
    public int? NUM10 { get; set; }
}

Added reference to Oracle.ManagedDataAccess and project throws file not found

File description: Oracle.ManagedDataAccess.dll
File version: 2.0.12.0
Product name: Oracle Data Provider for .NET Core
SIze: 4.16MB

This Oracle DLL was added to a .NET Standard class library. And a WebAPI has a reference to this .NET Standard class library.

When you run the project, it fires:

System.IO.FileNotFoundException: Could not load file or assembly 'Oracle.ManagedDataAccess, Version=2.0.12.0, Culture=neutral, PublicKeyToken=89b483f429c47342'. The system cannot find the file specified.
File name: 'Oracle.ManagedDataAccess, Version=2.0.12.0, Culture=neutral, PublicKeyToken=89b483f429c47342'
   at MyProject.Repository.BaseRepository..ctor()
   at MyProject.Repository.UserRepository..ctor()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitCreateInstance(CreateInstanceCallSite createInstanceCallSite, ServiceProvider provider)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(IServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitTransient(TransientCallSite transientCallSite, ServiceProvider provider)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(IServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitConstructor(ConstructorCallSite constructorCallSite, ServiceProvider provider)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(IServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitTransient(TransientCallSite transientCallSite, ServiceProvider provider)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(IServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceProvider.<>c__DisplayClass22_0.<RealizeService>b__0(ServiceProvider provider)
   at Microsoft.Extensions.DependencyInjection.ServiceProvider.GetService(Type serviceType)
   at Microsoft.Extensions.Internal.ActivatorUtilities.GetService(IServiceProvider sp, Type type, Type requiredBy, Boolean isDefaultParameterRequired)
   at lambda_method(Closure , IServiceProvider , Object[] )
   at Microsoft.AspNetCore.Mvc.Controllers.ControllerActivatorProvider.<>c__DisplayClass4_0.<CreateActivator>b__0(ControllerContext controllerContext)
   at Microsoft.AspNetCore.Mvc.Controllers.ControllerFactoryProvider.<>c__DisplayClass5_0.<CreateControllerFactory>g__CreateController|0(ControllerContext controllerContext)
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeInnerFilterAsync>d__14.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.<InvokeNextResourceFilter>d__22.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.Rethrow(ResourceExecutedContext context)
   at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.<InvokeFilterPipelineAsync>d__17.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.<InvokeAsync>d__15.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.AspNetCore.Builder.RouterMiddleware.<Invoke>d__4.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.<Invoke>d__7.MoveNext()

I also have configured the DLL to be copied, changed its the Specific version to "no" but the project won't run.

Can Oracle create a stable nuget package for this DLL?

Scaffold-DbContext number(x,y)

Column tyoe: number(x,y)
Scaffold-DbContext Result: type int not decimal
Example:
Column tyoe: number(10,4)
Db value: 1234.5678
int value: 1234

Scaffold whole shema - Schema not found

Hey guys,
if I try to scaffold a whole schema (not the schema of the given user), I always get the message:

Unable to find a schema in the database matching the selected schema.

Here is how I try to scaffold the context:

dotnet ef dbcontext scaffold "User Id=myUser;Password=myPassword;Data Source=SomeTnsName;" Oracle.EntityFrameworkCore -o Models --schema SCHEMA

When will Oracle.EntityFrameworkCore release a beta version?

When will Oracle.EntityFrameworkCore release a beta version? thank you！

Oracle.ManagedDataAccess.dll not loading with core 2

I am using Core 2 with the beta 2 version of the DLL and it's not loading for me (even with Copy Local = Yes). Is there a away to get this to work?

System.IO.FileNotFoundException: 'Could not load file or assembly 'Oracle.ManagedDataAccess, Version=2.0.12.0, Culture=neutral, PublicKeyToken=89b483f429c47342'. The system cannot find the file specified.'

How to make Depency Injection with AddDbContext in .NET Core

I've checked the "source_files" sample but it was to simple for my understanding...

The MySQL provider and Npgsql providers suggests create the database connection inside the Context class, something like:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) 
=> optionsBuilder.UseNpgsql("Host=my_host;Database=my_db;Username=my_user;Password=my_pw");

And in the ConfigureService method of the Startup class, some code like this:

public void ConfigureServices(IServiceCollection services)
{
    services.AddDbContext<OracleContext>().BuildServiceProvider();
}

So, I'd like to know if is possible to make the dependency injection like that, does exist some method like the "UseNpgsql()" in Oracle.ManagedDataAccess.Core? In fact, does this driver support Entity Framework Core Object Mapping? Or is it only for database access purposes with native queries?

Thanks in advance!

Issue connecting to Oracle DB while running on Kubernetes

Hi,

I am having an issue when trying to connect to an Oracle DB from Kubernetes. However, the same application works perfectly when connecting to the db from my local machine.

I've added some trace logging and you can see the error I get below:

2019-10-16 12:42:31.839710 TID:16 (CFG) (ENV) Machine Name : baseapp-helm-dev-56b96fd86b-jmr7s
2019-10-16 12:42:31.843577 TID:16 (CFG) (ENV) User Name : baseappuser
2019-10-16 12:42:31.844338 TID:16 (CFG) (ENV) OS Version : Unix 4.19.50.1
2019-10-16 12:42:31.844402 TID:16 (CFG) (ENV) 64-bit OS : True
2019-10-16 12:42:31.844433 TID:16 (CFG) (ENV) 64-bit Process : True
2019-10-16 12:42:31.844495 TID:16 (CFG) (ENV) .NET Core Runtime Version : 3.0.0
2019-10-16 12:42:31.844537 TID:16 (CFG) (ENV) Application Directory : /src/app
2019-10-16 12:42:31.844561 TID:16 (CFG) (VER) Oracle Data Provider for .NET Core Driver Version : 2.0.19.1
2019-10-16 12:42:32.135277 TID:16 (CFG) (VER) Oracle Data Provider for .NET Core Driver Informational Version : 2.0.19.1:20190703
2019-10-16 12:42:32.141105 TID:16 (CFG) (SQLNET) FilePath : (null)
2019-10-16 12:42:32.141228 TID:16 (CFG) (TNSNAMES) FilePath : (null)
2019-10-16 12:42:32.154095 TID:16 (PUB) (OCFG) OracleConfiguration.TraceFileLocation() : /src/app
2019-10-16 12:42:32.154383 TID:16 (PUB) (OCFG) OracleConfiguration.TraceLevel() : 7
2019-10-16 12:42:31.827256 TID:16 (PUB) (ENT) OracleConnection.Open() (conid=30223109) (state=Closed) (sessid=0) (implid=0) (pooling=T) (txnid=n/a)
2019-10-16 12:42:32.211122 TID:16 (PRI) (ENT) (CP) OracleConnectionDispenser..cctor()
2019-10-16 12:42:32.212485 TID:16 (PRI) (EXT) (CP) OracleConnectionDispenser..cctor()
2019-10-16 12:42:32.212607 TID:16 (PRI) (ENT) (CP) OracleConnectionDispenser.Get()
2019-10-16 12:42:32.215404 TID:16 (PRI) (ENT) (CP) PoolManager.ctor()
2019-10-16 12:42:32.217209 TID:16 (PRI) (EXT) (CP) PoolManager.ctor()
2019-10-16 12:42:32.219060 TID:16 (PRI) (ENT) (CP) PoolManager.Initialize() (constr=User Id=PROJECTMOON_DEV;Data Source=invmapplorac3b1:1521/fareeagle;)
2019-10-16 12:42:32.221648 TID:16 (PRI) (EXT) (CP) PoolManager.Initialize() (pmid=10818129) (constr=User Id=PROJECTMOON_DEV;Data Source=invmapplorac3b1:1521/fareeagle;)
2019-10-16 12:42:32.229329 TID:16 (PRI) (BUF) (OBP.CTOR) (poolid:8253412) (OracleConnectionDispenser`3.GetPM)
2019-10-16 12:42:32.234281 TID:16 (PRI) (ENT) (CP) OraclePoolManager.Get()
2019-10-16 12:42:32.340199 TID:16 (PRI) (ENT) (CP) PoolManager.Get() (txnid=n/a) (bForceMatch=F)
2019-10-16 12:42:32.340819 TID:16 (PRI) (ENT) (CP) PoolManager.Get() MultiTenant : Searching for a idle connection, retryCountWithoutAffinity: 0
2019-10-16 12:42:32.345775 TID:16 (PRI) (ENT) PoolManager.ProcessCriteriaCtx_NonEnlistedConnection()
2019-10-16 12:42:32.345832 TID:16 (PRI) (EXT) PoolManager.ProcessCriteriaCtx_NonEnlistedConnection()
2019-10-16 12:42:32.354709 TID:16 (PRI) (ENT) (CP) PoolManager.CreateNewPR() (txnid=n/a)
2019-10-16 12:42:33.533596 TID:16 (PRI) (ENT) TimeStamp.GetLocalTZOffset()
2019-10-16 12:42:33.535150 TID:16 (PRI) (EXT) TimeStamp.GetLocalTZOffset()
2019-10-16 12:42:33.539004 TID:15 (PRI) (ENT) (CP) PoolManager.CreateNewPRThreadFunc()
2019-10-16 12:42:33.539290 TID:15 (PRI) (ENT) (CP) PoolManager.CreateNewPRThreadFunc() (initiated by TID:16)
2019-10-16 12:42:33.548479 TID:15 (PRI) (SVC) (ENT) OracleConnectionImpl.Connect() (oper=open) (aff=n/a) (inst=) (affmatch=n/a) (pr.service=) (pr.pdb=) (pr.edition=) (sessid=-1:-1) (F;F;F;;N) (pmid=10818129)
2019-10-16 12:42:33.549371 TID:15 (PRI) (BUF) (COBP.CTOR) (poolid:1) (parentpoolid:8253412) (OracleConnectionImpl.Connect)
2019-10-16 12:42:33.613320 TID:15 (CFG) (SQLNET) FilePath : (null)
2019-10-16 12:42:33.613410 TID:15 (CFG) (TNSNAMES) FilePath : (null)
2019-10-16 12:42:33.651675 TID:15 (NET) (ENT) EZConnect.ResolveSimple()
2019-10-16 12:42:33.651838 TID:15 (NET) Resolving EZConnect+ string: invmapplorac3b1:1521/fareeagle
2019-10-16 12:42:33.711440 TID:15 (NET) (EXT) EZConnect.ResolveSimple()
2019-10-16 12:42:33.738085 TID:15 (NET) (ENT) TcpTransportAdapter.Connect()
2019-10-16 12:42:33.741708 TID:15 (NET) Trying (host=invmapplorac3b1) (port=1521)
2019-10-16 12:42:33.741754 TID:15 (NET) (ENT) TcpTransportAdapter.Connect()
2019-10-16 12:42:33.743947 TID:15 (NET) (ENT) TcpTransportAdapter.ConnectIterate()
2019-10-16 12:42:33.744742 TID:15 (NET) Trying (address=10.0.116.106) (port=1521)

And a bit further down, here is more logging where the error seems to happen (I have removed some of the generated stuff so that it could be more readable)

AUTH_TERMINAL.!!baseapp-helm-dev-56b96fd86b-jmr7s AUTH_PROGRAM_NM...NetCoreBaseApp.dll AUTH_MACHINE.CCbaseapp-helm-dev-56b96fd86b-jmr7s\baseapp-helm-dev-56b96fd86b-jmr7s AUTH_PID...1:1 AUTH_SID...baseappuser
AUTH_CONNECT_STRING...(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=invmapplorac3b1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=fareeagle)(CID=(PROGRAM=/src/app/NetCoreBaseApp.dll)(HOST=baseapp-helm-dev-56b96fd86b-jmr7s)(USER=baseappuser))))..
..SESSION_CLIENT_CHARSET...178 SESSION_CLIENT_LIB_TYPE...0 SESSION_CLIENT_DRIVER_NAME...ODPC.NET : 19.1.0.0.0 SESSION_CLIENT_VERSION...318832640 SESSION_CLIENT_LOBATTR...1

AUTH_ALTER_SESSION.YYALTER SESSION SET NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' TIME_ZONE='Etc/GMT'...

(PRI) (TTC) (ENT) TTCAuthenticate.ReceiveOAuthResponse()

AMERICAN AMERICA $ AMERICA, WE8MSWIN1252 GREGORIAN DD-MON-RR AMERICAN BINARY HH.MI.SSXFF AM.9 DD-MON-RR HH.MI.SSXFF AM.:HH.MI.SSXFF AM TZR.;.DD-MON-RR HH.MI.SSXFF AM TZR.<$.4 BINARY.2 BYTE.= FALSE.> .<< .
ORA-00604: error occurred at recursive SQL level 1.ORA-06502: PL/SQL: numeric or value error: character string buffer too small.ORA-06512: at line 18.

SaveChanges() return 0 for id

When using SaveChanges() to add an item to a table, I get a 0 back for the unique id. When I check in the Oracle database, it shows a number for the id and was inserted fine. I am just not getting the id back after calling SaveChanges() in my code.

Scaffold-DbContext GetTriggerColumns Exception. An item with the same key has already been added.

Table has multiple triggers, using the same column_name.
select column_name from USER_TRIGGER_COLS.
System.ArgumentException: An item with the same key has already been added.

`Scaffold-DbContext` results ORA-00904: "C"."IDENTITY_COLUMN"

Scaffold-DbContext results ORA-00904: "C"."IDENTITY_COLUMN"
The DB which i connected to is "R11.2" which does not have IDENTITY_COLUMN.
I think "That is reason".

Is there any way to specify target DB version which is not support IDENTITY_COLUMN.

ODP.NET-EF-Core-Beta-2-Documentation.pdf says UseOracleSQLCompatibility() extention method
can specify database version.
But ...

Thank you.

nuget not available :- "Oracle.EntityFrameworkCore"

Please let me know if package source target is different.

http://packagesource

Fulltext support

Are you planning to add fulltext support to Oracle.EntityFrameworkCore? In SQL Server it can be done as follows

dbContext.ExampleTable.Where(x => EF.Functions.Contains(x.ftProperty, "text")).ToList();

I can not find Oracle.EntityFrameworkCore in nuget

following the example with ef core (https://github.com/oracle/dotnet-db-samples/tree/master/samples/dotnet-core/ef-core) I must install the package Oracle.EntityFrameworkCore in nuget, but I can not find it. Today I use .NET Identity + EF 6 + Oracle and I will need to migrate to .NET Core and I am not making progress.

Tks.

DbUpdateConcurrencyException with EntityFramework Core for workflow-core

Hello,

I'm trying to implement Oracle as a Persistence mechanism for workflow-core. I based myself on the existing MySQL, MSSQL and Postgre implementations (which all work 100%)

However, when I run my integration tests, some of them fail (EventScenario,RetrySageScenario,WhileScenario) with the following errors

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.InvalidOperationException: The instance of entity type 'PersistedExecutionPointer' cannot be tracked because another instance with the key value '{PersistenceId: 1}' is already being tracked. When attaching existing entities, ensure that only one entity instance with a given key value is attached.

Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException: Database operation expected to affect 1 row(s) but actually affected 2282 row(s). Data may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions.

The EF query that gives theses errors is here:

        public async Task PersistWorkflow(WorkflowInstance workflow)
        {
            using (var db = ConstructDbContext())
            {
                var uid = new Guid(workflow.Id);
                var existingEntity = await db.Set<PersistedWorkflow>()
                    .Where(x => x.InstanceId == uid)
                    .Include(wf => wf.ExecutionPointers)
                    .ThenInclude(ep => ep.ExtensionAttributes)
                    .Include(wf => wf.ExecutionPointers)
                    .AsTracking()
                    .FirstAsync();

                var persistable = workflow.ToPersistable(existingEntity);
                await db.SaveChangesAsync();
            }
        }

Is this a known issue?

This was done using Oracle.EntityFrameworkCore Version 2.19.30, on a 12.1.0.2.0 Database

See danielgerlag/workflow-core#422 for reference

Timeout doesn't work

I tried what's here but it fetched data beyond the timeout I set in seconds.

      using (OracleConnection connection = new OracleConnection(connectionString))
      {
        OracleCommand command = new OracleCommand(selectStatement, connection);
        command.Connection.Open();

        using (OracleDataAdapter da = new OracleDataAdapter(command))
        {
          da.SelectCommand.CommandTimeout = 30;
          da.Fill(dataTable);
          command.Connection.Close();
          da.Dispose();
        }
      }

.NET Standard 2.0 support?

oracle dataprovider timeout after oracle restart

https://stackoverflow.com/questions/49030744/oracle-dataprovider-timeout-after-oracle-restart#49030744

Official version

Hello!!

I would like to know when it's published the official version ?

tks,

.NET Core support

Project Oracle.ManagedDataAccess is not compatible with netcoreapp1.0 (.NETCoreApp,Version=v1.0).

When a compatible version will be released?

.First or .FirstOrDefault does not work

This code is giving the following exception:
_context.User.FirstOrDefault(p => p.Id == 3)

exception:
"ORA-00933: SQL command not properly ended"

When I use .ToList() work normaly

Unable to find provider assembly with name Oracle.EntityFrameworkCore

I added below two references on asp.net core console application.

Install-Package Microsoft.EntityFrameworkCore.Tools -Version 2.1.4
Install-Package Oracle.ManagedDataAccess.Core -Version 2.18.3

I am getting below exception when I am using scaffolding command for nuget command.

Unable to find provider assembly with name Oracle.EntityFrameworkCore

My scaffolding command is like below :

Scaffold-DbContext "Data Source=oracle_host;Persist Security Info=True;User Id=myuser;Password=mypassword;" Oracle.EntityFrameworkCore -OutputDir Models

NUMBER(4,0) is converted to byte

We are using 12c database with db first methodology , When the db is scaffolded number4(4,0) is coverted to byte , instead of int.

Scaffold-DbContext MAP System.ArgumentNullException

Example:

--------------------------------------------------------
--  DDL for Table TableName
--------------------------------------------------------

  CREATE TABLE "SchemaName"."TableName" 
   ("COL1" VARCHAR2(10 CHAR), 
    "COL2" VARCHAR2(8 CHAR));
--------------------------------------------------------
--  DDL for Index IDX_TableName
--------------------------------------------------------

  CREATE INDEX "SchemaName"."IDX_TableName" ON "SchemaName"."TableName" (SUBSTR("COL2",1,6));

--------------------------------------------------------
--  DDL for Index PK_TableName
--------------------------------------------------------

  CREATE UNIQUE INDEX "SchemaName"."PK_TableName" ON "SchemaName"."TableName" ("COL1");

--------------------------------------------------------
--  Constraints for Table TableName
--------------------------------------------------------
  ALTER TABLE "SchemaName"."TableName" MODIFY ("COL1" NOT NULL ENABLE);
  ALTER TABLE "SchemaName"."TableName" ADD CONSTRAINT "PK_TableName" PRIMARY KEY ("COL1");

CoreFirst EFMigrationsHistory Error

I copied this code to execute corefirst and told me that the table did not exist.

I am a brand new database

Error:
Failed executing DbCommand (113ms) [Parameters=[], CommandType='Text', CommandTimeout='0']
SELECT "MigrationId", "ProductVersion"
FROM "__EFMigrationsHistory"
ORDER BY "MigrationId"

What am I missing?

PublishTrimmed with Oracle.ManagedDataAccess.Core causes MSB6006 error

1. dotnet new console
2. dotnet add package Oracle.ManagedDataAccess.Core --version 2.19.50
3. dotnet publish --runtime win-x64 /p:PublishTrimmed=true

Error

dotnet publish --runtime win-x64 /p:PublishTrimmed=true
.NET Core 向け Microsoft (R) Build Engine バージョン 16.3.0+0f4c62fea
Copyright (C) Microsoft Corporation.All rights reserved.

  C:\work\Trimmed\Trimmed.csproj の復元が 20 ms で完了しました。
  Trimmed -> C:\work\Trimmed\bin\Debug\netcoreapp3.0\win-x64\Trimmed.dll
C:\Program Files\dotnet\sdk\3.0.100\Sdks\Microsoft.NET.Sdk\targets\Microsoft.NET.ILLink.targets(87,5): error MSB6006: "dotnet.exe" はコード -532462766 を伴って終了しました。 [C:\work\Trimmed\Trimmed.csproj]

Configuration Api and tnsnames.ora

Hi, tnsnames.ora is still supported scenario or db related details need to be provided via configuration api as given in the

https://github.com/oracle/dotnet-db-samples/blob/master/samples/dotnet-core/configuration-api/config-api.cs#L32

Thanks

Bugs in EF-Core Script Migrtions

It seems that there are some bugs in Script-Migration - EF Core tool for SQL script generation.
I have DbContext with default schema MY_SCHEMA, SQL compatibility set to 11 and one entity:

public class Document
{
    public int Id { get; set; }
    public string Code { get; set; }
    public string Value { get; set; }
}

I create migration:

dotnet ef migrations add InitialCreate

and migration script:

dotnet ef migrations script

The output looks as follows:

BEGIN
                      EXECUTE IMMEDIATE 'BEGIN 

execute immediate 'CREATE TABLE "__EFMigrationsHistory" (
    "MigrationId" NVARCHAR2(150) NOT NULL,
    "ProductVersion" NVARCHAR2(32) NOT NULL,
    CONSTRAINT "PK___EFMigrationsHistory" PRIMARY KEY ("MigrationId")
)';
END;';
                    EXCEPTION
                    WHEN OTHERS THEN
                      IF(SQLCODE != -942)THEN
                          RAISE;
                      END IF;
                    END;
/

select count(*) from all_users where username='MY_SCHEMA'
/

BEGIN 

execute immediate 'CREATE SEQUENCE "MY_SCHEMA"."Document_Id_sq1" start with 1';

execute immediate 'CREATE TABLE "MY_SCHEMA"."Document" (
    "Id" NUMBER(10) NOT NULL,
    "Code" NVARCHAR2(2000),
    "Value" NVARCHAR2(2000),
    CONSTRAINT "PK_Document" PRIMARY KEY ("Id")
)';
execute immediate 'create or replace trigger "Document_Id_i2"
before insert on "Document" for each row 
begin 
  if :new."Id" is NULL then 
    select "Document_Id_sq1".nextval into :new."Id" from dual;  
  end if; 
end;';
END;
/

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES (N'20190509194625_init', N'2.2.3-servicing-35854')
/

There are 2 problems:

• EXECUTE IMMEDIATE BEGIN followed by EXECUTE IMMEDIATE BEGIN at first line. It appears on every script generated by this tool - and cannot be compiled by oracle
• I set default schema MY_SCHEMA, however trigger is not generated under this schema and moreover it references to Document table and newly generated sequence without schema specification.

Server Returns ORA-01017 When Connecting to Server with EUS Enabled Using Managed Driver

Are there any known issues within the managed driver that prevent client applications from authenticating with an Oracle server whenever Enterprise User Security is enable server-side?

We are able to connect to the database behind EUS just fine as long as we use the unmanaged driver. However; using the managed driver results in the server returning an ORA-01017 error code. Additionally, we can connect to other Oracle servers using the managed driver when EUS is not in the picture.

Can we get an LDAP connection sample for ODP.NET Core?

Can we get an LDAP connection sample for ODP.NET Core? I read that it is supported but have failed to find any documentation related to this claim.

InitialLOBFetchSize

Hello,
is there any way to set InitialLOBFetchSize property globally?

Geography / spatial data type

How can I access an Oracle table that has a SHAPE / geographic column type?

For these tables I am getting "One or more errors occurred. (Unsupported column datatype)".

.NET Core 3 Support

Hello,

What is the expected timeline for .NET Core 3 support now that it has been officially released?

Thank you

Error getting output parameter from stored procedure

I'm trying to call a stored procedure with an output parameter. The in parameters are sent with success to procedure however I can't get the output parameter

using (var db = new BloggingContext()) {
                OracleParameter[] parameters = {
                    new OracleParameter("i_var", "variable"),
                    new OracleParameter("i_num", 1),
                    new OracleParameter("o_clob", OracleDbType.Clob, ParameterDirection.Output)
                };

                await db.Database.ExecuteSqlCommandAsync("BEGIN pck_test.test_proc (:i_var, :i_num, :o_clob); END;", parameters);


                OracleClob result = (OracleClob) parameters[2].Value;
            }

And the procedure code is

PACKAGE BODY pck_test
IS
    PROCEDURE test_proc (i_var varchar2, i_num number, o_clob out varchar2)
    IS
    BEGIN
          o_clob:= 'aaa';
    END;
END;

How can I get the value from o_clob parameter?

how to remove specific user sessions from oracle v$session table on dot net project logout

In our dot net project user wise connection string is there. If 100 dedicated DB users (ie different connection string) login on system then 100 sessions get created.

I have used ClearPool and ClearAllPools method in dot net but again 1 session get created because of pooling.

Currently pooling is true in connection string and i want to keep it true only, because of performance.

On User logout i want to clear all session from V$session table.

Here I am using Visual Studio 2013 with Asp.Net C#.

Database - Oracle Database 11g Enterprise Edition

Database Connection Using ODP.NET Driver(Oracle.DataAccess.Client).

Please suggest.

Xamarin.Android | System.InvalidCastException: Unable to convert an object of type 'System.Byte' to 'System.String'.

Overview

One error occours when i try to compile a project, xamarin.android, with linker option SDK assemblies only in release mode. Removing the Oracle.ManagedDataAccess.Core linker runs perfectly.

Error Details

Check here the stacktrace ⬇

System.InvalidCastException: Unable to convert an object of type 'System.Byte' to 'System.String'.
   em Mono.Cecil.MetadataBuilder.GetConstantSignature(ElementType type, Object value)
   em Mono.Cecil.MetadataBuilder.AddConstant(IConstantProvider owner, TypeReference type)
   em Mono.Cecil.MetadataBuilder.AddField(FieldDefinition field)
   em Mono.Cecil.MetadataBuilder.AddFields(TypeDefinition type)
   em Mono.Cecil.MetadataBuilder.AddType(TypeDefinition type)
   em Mono.Cecil.MetadataBuilder.AddTypes()
   em Mono.Cecil.MetadataBuilder.BuildTypes()
   em Mono.Cecil.MetadataBuilder.BuildModule()
   em Mono.Cecil.MetadataBuilder.BuildMetadata()
   em Mono.Cecil.ModuleWriter.<>c.<BuildMetadata>b__2_0(MetadataBuilder builder, MetadataReader _)
   em Mono.Cecil.ModuleDefinition.Read[TItem,TRet](TItem item, Func`3 read)
   em Mono.Cecil.ModuleWriter.BuildMetadata(ModuleDefinition module, MetadataBuilder metadata)
   em Mono.Cecil.ModuleWriter.Write(ModuleDefinition module, Disposable`1 stream, WriterParameters parameters)
   em Mono.Cecil.ModuleWriter.WriteModule(ModuleDefinition module, Disposable`1 stream, WriterParameters parameters)
   em Mono.Cecil.ModuleDefinition.Write(String fileName, WriterParameters parameters)
   em Mono.Cecil.AssemblyDefinition.Write(String fileName, WriterParameters parameters)
   em Mono.Linker.Steps.OutputStep.WriteAssembly(AssemblyDefinition assembly, String directory, WriterParameters writerParameters)
   em Mono.Linker.Steps.OutputStep.OutputAssembly(AssemblyDefinition assembly)
   em Mono.Linker.Steps.OutputStep.ProcessAssembly(AssemblyDefinition assembly)
   em Mono.Linker.Steps.BaseStep.Process(LinkContext context)
   em Mono.Linker.Pipeline.Process(LinkContext context)
   em MonoDroid.Tuner.Linker.Process(LinkerOptions options, ILogger logger, LinkContext& context)
   em Xamarin.Android.Tasks.LinkAssemblies.Execute(DirectoryAssemblyResolver res)
   em Xamarin.Android.Tasks.LinkAssemblies.Execute()
   em Microsoft.Build.BackEnd.TaskExecutionHost.Microsoft.Build.BackEnd.ITaskExecutionHost.Execute()
   em Microsoft.Build.BackEnd.TaskBuilder.<ExecuteInstantiatedTask>d__26.MoveNext()	simix.supermidia	C:\Program Files (x86)\Microsoft Visual Studio\2017\Community\MSBuild\Xamarin\Android\Xamarin.Android.Common.targets	2129

What did i tried

• When use Dont link option project is compiled, but my Apk size is duplicated
• When use Linker all option seems like runs ok, but i have some references to resolve.. so i cant run a complete test right now.
• I tested with these versions: 2.18.3, 2.18.5, 2.18.6, 2.19.3 and 2.19.31

Explain plan?

Is there a way to perform an explain plan on a query where the results are returned as a managed-code object, rather than a string to be parsed?

Scaffold-DbContext To Tables from another schema

I try Scaffold tables that is stored in another schema, using the next command:

Scaffold-DbContext "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=X.X.X.X)(PORT=1521)))(CONNECT_DATA=(SID=MYSID)));User Id=MYUSER;Password=MYPASSWORD;" Oracle.EntityFrameworkCore -Tables "AnotherScheme.TheTable"

and i am getting:

Unable to find a table in the database matching the selected table AnotherScheme.TheTable.

I can make queries to the table.

ManagedDataAcces Execute reader performance

Hello. I have .Net framework API with odp.net.x64 package to access Oracle DB. I'm trying to move my application to .Net core and get Oracle.ManagedDataAccess.Core package for this, but my request to DB became 10 times slower. I have a simple select to DB and dbCommand.ExecuteReader() takes more then 2 sec for executing. In the previous version, it was 300 ms. I can't find any information why it so slow. Any ideas?

ExecuteNonQuery alway return -1 when update data

When i using .net core
//finally, execute the command.
int retval = cmd.ExecuteNonQuery();

retval alway received -1 when update successfully or false

Still issue with Migration table in different schema (user) that currently is logged in

I found here that the issue with different schema of migration table and logged user is solved but with latest version of NuGet package i still get following error:

 Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-00904: "T"."OWNER": invalid identifier

How to handle ExecuteNonQuery properly?

I am calling a stored procedure which has in and out parameters; I am confused a bit of how to check whether returned out is null or not using .net core2; Here is an example:

        String strErr = "";
        var prm = new OracleParameter();
        using (OracleConnection con = new OracleConnection(conString))
        {
            using (OracleCommand cmd = con.CreateCommand())
            {
                try
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    con.Open();
                    cmd.CommandText = "PKG_WEB.CALL_US";
                    cmd.BindByName = true;

                    prm = new OracleParameter("P_ID", OracleDbType.NVarchar2);
                    prm.Direction = ParameterDirection.Input;
                    prm.Value = 1;
                    cmd.Parameters.Add(prm);

                    prm = new OracleParameter("P_MOBILE", OracleDbType.NVarchar2);
                    prm.Direction = ParameterDirection.Input;
                    prm.Value = 123;
                    cmd.Parameters.Add(prm);

                    prm = new OracleParameter("P_ERROR", OracleDbType.NVarchar2, 100);
                    prm.Direction = ParameterDirection.Output;
                    cmd.Parameters.Add(prm);

                    cmd.ExecuteNonQuery();

                   // here how can we check if returned P_ERROR param is null?
                   if (  //  P_ERROR  not null  ){
                        strErr = cmd.Parameters["P_ERROR"].Value.ToString();
                       // if condition was not added strErr wil be filled as "null"
                    }                        
                }
                catch (Exception ex)
                {
                    //Console.WriteLine(ex.Message);
                }
                //Console.ReadLine();
            }
        }

I appreciate if you can post an example of ExecuteNonQuery on github same as ExecuteReader;