Execute SQL Alter commands for every session with

Question

I have a requirement to execute some SQL commands at the start of every database session. I am using Entity Framework 5 via DbContext talking to a Oracle 11g database.

I would like to execute:

ALTER SESSION SET NLS_COMP=ANSI;
ALTER SESSION SET NLS_SORT=BINARY_CI;

at the start of an session creation to get case insensitive searching.
How best could I go about this?

I've put the commands in the constructor of dbContext, but have only simple unit test and it does appear to work. But unsure if this is right thing to do

public partial class Entities : DbContext
{
    public Entities()
        : base("name=Entities")
    {
        this.Database.ExecuteSqlCommand("ALTER SESSION SET NLS_COMP=ANSI");
        this.Database.ExecuteSqlCommand("ALTER SESSION SET NLS_SORT=BINARY_CI");
    }
}

Answer 1

you can use Database.Connection.StateChange method

    public AtomContext(string nameOrConnectionString)
        : base(nameOrConnectionString)
    {
        this.Database.Connection.StateChange += Connection_StateChange;
    }

    void Connection_StateChange(object sender, StateChangeEventArgs e)
    {
        if (e.OriginalState == ConnectionState.Open || e.CurrentState != ConnectionState.Open)
            return;

        IDbConnection connection = ((EntityConnection)((IObjectContextAdapter)this).ObjectContext.Connection).StoreConnection;
        using (IDbCommand command = connection.CreateCommand("ALTER SESSION SET NLS_LANGUAGE=TURKISH"))
            command.ExecuteNonQuery();

        using (IDbCommand command = connection.CreateCommand("ALTER SESSION SET NLS_COMP = LINGUISTIC"))
            command.ExecuteNonQuery();

        using (IDbCommand command = connection.CreateCommand("ALTER SESSION SET NLS_SORT=TURKISH_AI"))
            command.ExecuteNonQuery();
    }

entityframeworkef6oracledbcontextsession

Answer 2

The other option would be a "LOGON TRIGGER ON SCHEMA". But it's questionable whether you force DBA's to create it. Logon triggers are considered to be "dangerous".

Answer 3

If anyone is reading this, no, the right way is doing it on connection open triggered down the line of:

public Entities()
    : base("name=Entities")
{        

    ctx.Database.Connection.StateChange += Connection_StateChange;
    ...
}

    private void Connection_StateChange(object sender, StateChangeEventArgs e)
    {
        if (e.OriginalState == ConnectionState.Open || e.CurrentState != ConnectionState.Open)
            return;

       this.Database.ExecuteSqlCommand("ALTER SESSION SET NLS_COMP=ANSI");

    }