Changing the default schema in Oracle
Posted by merill | Filed under Tips
The application I was working on assumed that the user would always be in the default schema. I ran into a snag when I had to connect to the staging environment where the read only user that I was provided with didn’t have all the objects in his schema.
The solution was to make a call to change the default schema using the ALTER SESSION call with this code.
/// <summary>
/// Sets the schema to use if one is configured.
/// </summary>
public static void SetSchema()
{
var schema = ConfigurationManager.AppSettings["SchemaName"];
if (string.IsNullOrEmpty(schema)) return;
using (var connection = GetConnection())
{
using (var command = new OracleCommand("alter session set current_schema=" + schema))
{
connection.Open();
command.Connection = connection;
command.ExecuteNonQuery();
}
}
}
- WPF Checked ListBox
- Viewing the disk space usage (size) of all the tables in a SQL Server 2005/2008 database
- Deleting millions of records from a table without blowing the transaction log
- Microsoft .NET Framework 3.5 SP1 breaks Microsoft CCF (Customer Care Framework)
- Making your portable hard disk work with the XBox 360
August 13th, 2009 at 7:46 pm
Shame you can’t set this through the connection string, but this is a very nice workaround.
September 17th, 2009 at 6:41 pm
@Steven Algieri
Could you tell me how to set schema in oracle connection string?