How to: Change the Database Schema

When you want to change the database schema to a schema different than the schema for the currently connected user, you need to execute an alter session statement on the connection - ALTER SESSION SET CURRENT_SCHEMA="scott";. You can do this by setting the InitSQL property backend configuration object. The InitSQL property specifies an SQL statement to be executed on each newly created connection. This can be used for application role restrictions. This property can be used in a generic way to provide a connection with a customer specific setup.

1. Open the file that holds the definition of your context class.
2. Locate the GetBackendConfiguration method that provides the context constructor with a BackendConfiguration instance.
3. In it set the specific SQL statement:

public static BackendConfiguration GetBackendConfiguration()
{
   BackendConfiguration backend = new BackendConfiguration();

    // Additional backend configuration settings.

   backend.ConnectionPool.InitSQL.Add( "ALTER SESSION SET CURRENT_SCHEMA=scott" );
   return backend;
}

Public Shared Function GetBackendConfiguration() As BackendConfiguration
 Dim backend As New BackendConfiguration()

 ' Additional backend configuration settings.

 backend.ConnectionPool.InitSQL.Add("ALTER SESSION SET CURRENT_SCHEMA=scott")
 Return backend
End Function