ODP.NET

Hello,

I am trying to query tables in other schema than what my user has as default. For example, I am using user boy, but the tables that I want to query is in the schema man. I can query using reference such as "{color:#3366ff}select * from man.table1{color}", but to do that I have to change a lot of codes. I would like to query only "{color:#3366ff}select * from table1{color}" using the user boy.

I was trying to use query "alter session set current_schema = schema_name" after opening the connection to change the schema before using the same connection for the query I want to execute, but it seems that the schema was not changed.

Below is my code as reference:

internal static void
OpenOracleConnectionByAppSetting(
OracleConnection oracleConnectionObject, string appSettingKey)
{
string defaultSchema = "" + System.Configuration.ConfigurationManager.AppSettings\["ZZZ.Oracle.DefaultSchema"\];
try
{
oracleConnectionObject.ConnectionString =
System.Configuration.ConfigurationManager.AppSettings\[appSettingKey\];
oracleConnectionObject.Open();

if (defaultSchema != "")
{
string sql = "alter session set current_schema = " + defaultSchema;

oracleConnectionObject.GetSessionInfo();

OracleDA.Client.OracleCommand cmd = new OracleCommand(sql, oracleConnectionObject);
cmd.ExecuteNonQuery();

if (cmd != null)
{
cmd.Dispose();
}

}

}
catch(Exception ex)
{
string defaultSchemaNote = defaultSchema == "" ? "" : " Using defaultSchema=[" + defaultSchema + "].";
// note: for security reasons, do NOT inform the connection string itself!
throw new Exception("Failed to connect to database." + defaultSchemaNote
+ " (check setting [" + appSettingKey + "] in Web.config configuration file).", ex);
}
}


The code is opening database connection and try to change the schema. Other query will use the same connection.


Does anybody can help me on this?

Thank you very much in advance.