Alter Session Problems

[Oktane]

Hi,

I am trying to set the schema name by using:
ALTER SESSION SET CURRENT_SCHEMA = ""
When my application is run (btw - I have modified the OnContextCreated on my EF DataContext class)

This gets executed before any of my commands are executed when my context is created, but I am getting no results returned. If I manually run the commands for SQL Developer -

ALTER SESSION SET CURRENT_SCHEMA = Schema
SELECT * FROM TABLE

and I get results from the DB with no issues.

When I run these operations in my code I get no results. I have monitored the activity using dbMonitor and I see the following

-Connection Open
-Alter Statement Prepared/Executed
-Close Connection
-Open Connection
-Connection is taken from Pool
-Select Statement Prepared/Executed
-Close Connection

All under the same OracleConnection object.

Any ideas?

[Shalex]

Please use one of the following OnContextCreated methods to use the same StoreConnection during context lifetime:

Code: Select all

partial void OnContextCreated()
{
            DbConnection conn = (Connection as EntityConnection).StoreConnection;
            conn.Open();
            ExecuteStoreCommand("ALTER SESSION SET CURRENT_SCHEMA = YourSchemaName");
}

OR

Code: Select all

partial void OnContextCreated()
{
            DbConnection conn = (Connection as EntityConnection).StoreConnection;
            conn.Open();
            conn.ChangeDatabase("YourSchemaName");
}

Notify us about the results.

[HCRoman]

Better is to track the StateChange event
This works for each connection open!

(
The code inside of the event handler can be one of the examples from Shalex too...
)

Code: Select all

partial void OnContextCreated() 
{ 
    Connection.StateChange += this.EntityConnection_StateChange;
} 

private void EntityConnection_StateChange(object sender, StateChangeEventArgs e)
{
   if (e.CurrentState == ConnectionState.Open)
   {
      DbConnection connection = ((EntityConnection)sender).StoreConnection;
      using (DbCommand command = connection.CreateCommand())
      {
         this.SetRoles(command);
         this.SetCurrentSchema(command);
      }
   }
}

Roman