Hi,
We are trying to use dotConnect for Oracle to connect to an oracle cluster. The jdbc:oracle:thin connection string looks like the following:
jdbc:oracle:thin:@(DESCRIPTION =
(FAILOVER = on)
(ENABLE = broken)
(ADDRESS =
(PROTOCOL = TCP)
(HOST = s1-company.com)
(PORT = 1521)
)
(ADDRESS =
(PROTOCOL = TCP)
(HOST = s2-company.com)
(PORT = 1521)
)
(ADDRESS =
(PROTOCOL = TCP)
(HOST = s3-company.com)
(PORT = 1521)
)
(ADDRESS =
(PROTOCOL = TCP)
(HOST = s1company.com)
(PORT = 1521)
)
(ADDRESS =
(PROTOCOL = TCP)
(HOST = s2company.com)
(PORT = 1521)
)
(ADDRESS =
(PROTOCOL = TCP)
(HOST = s3company.com)
(PORT = 1521)
)
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = service1)
(FAILOVER_MODE =
(TYPE = select)
(METHOD = basic)
(RETRIES = 20)
(DELAY = 5)
)
)
)
We have tried to use the following as connection string:
"UserID=xxx;Password=xxx;Host=s1-company.com;SID=service1;Direct=true"
"Host=s1-company.com;Port=1521;User ID=xxx;Password=xxx;Service Name=service1;Alternate Servers=s2-company.com;Port=1521;Service Name=service1;Load Balancing=true"
But we got the following error:
Connection failed.
Unknown connection string parameter Alternate Servers
Please advise what connection string should we use to make it happen.
Thanks,
Sean
connection string for 3-node oracle cluster
RAC is supported in the OCI mode only (the Direct=false; parameter in the connection string). All Oracle client settings should be specified in the tnsnames.ora file of your Oracle client (http://download.oracle.com/docs/cd/B283 ... snames.htm).
In direct mode (the Direct=true; parameter in the connection string) the RAC option of Oracle server is not available (you can connect only to each database instance separately).
In direct mode (the Direct=true; parameter in the connection string) the RAC option of Oracle server is not available (you can connect only to each database instance separately).
Will the following connection string works in this case?
UserID=xxx;Password=xxx;Host=s1-company.com;SID=service1;Direct=false;
Also from documentation, in order for OCI mode to work, end user will have to install Oracle Client software, is this correct? What specific software does the end user need to install in order for this RCA oracle to work?
UserID=xxx;Password=xxx;Host=s1-company.com;SID=service1;Direct=false;
Also from documentation, in order for OCI mode to work, end user will have to install Oracle Client software, is this correct? What specific software does the end user need to install in order for this RCA oracle to work?
1. The SID parameter is used only if Direct is set to true (default). So, please remove SID=service1; from your connection string. If the tnsnames.ora file of your Oracle Client contains the s1-company.com alias, the rest of your connection string is ok.
2. You are right. Oracle Client is a middleware between our provider and Oracle Server when the OCI mode is used.
3. Please refer to Oracle documentation to get information about Oracle RAC.
2. You are right. Oracle Client is a middleware between our provider and Oracle Server when the OCI mode is used.
3. Please refer to Oracle documentation to get information about Oracle RAC.
I have part of tnsnames.ora as below
company_server =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db-edw-prod1)(PORT = 1531))
(ADDRESS = (PROTOCOL = TCP)(HOST = db-edw-prod2)(PORT = 1531))
(LOAD_BALANCE = yes)
(FAILOVER = yes)
(CONNECT_DATA =
(FAILOVER_MODE=(TYPE=select)(METHOD=basic))
(SERVICE_NAME = company_server)
)
)
So should the connection string be like
User ID=userid;Password=XXXXXXXX;Host= company_server; Min Pool Size=0;Max Pool Size=100;Connection Lifetime=0;Direct=false;
company_server =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db-edw-prod1)(PORT = 1531))
(ADDRESS = (PROTOCOL = TCP)(HOST = db-edw-prod2)(PORT = 1531))
(LOAD_BALANCE = yes)
(FAILOVER = yes)
(CONNECT_DATA =
(FAILOVER_MODE=(TYPE=select)(METHOD=basic))
(SERVICE_NAME = company_server)
)
)
So should the connection string be like
User ID=userid;Password=XXXXXXXX;Host= company_server; Min Pool Size=0;Max Pool Size=100;Connection Lifetime=0;Direct=false;
We are successful when testing the connection string by creating a OracleConnection with Open command. However when try to call to the Oracle database stored procedure, we received the following error:
The following error occurred: An error occured while connecting to database: Error = Arithmetic operation resulted in an overflow.; Command Type = StoredProcedure; Command= MyStoredProcedure;
Please note we have the same database in a single node environment. Everything is almost identical in this test environment comparing to the 3 node RAC production environment. All stored procedures, tables should be identical and return the same data. But the same stored procedure call worked in single node environment not in our clustered environment.
We also cannot get a list of stored procedure available from Oracle database, which we could when connecting to a single node Oracle. The following shows the code snippet for getting the list of stored procs.
public static List GetStoredProcedureNames(string connectionString, List schemaNames)
{
List spNames = new List();
string schemaName = (schemaNames.Count > 0 ? schemaNames[0] : string.Empty);
// now remove passed in schema name (invalid or valid) and repopulate it
schemaNames.Clear();
using (OracleConnection connection = new OracleConnection(connectionString))
{
try
{
CommandType commandType = CommandType.Text;
OracleDataReader reader = OracleHelper.ExecuteReader(connection,
CommandType.Text,
"select owner, object_name, procedure_name from all_procedures",
null);
Hashtable hash = new Hashtable();
while (reader.Read())
{
string owner = reader.GetString(0);
string objectName = reader.GetString(1);
string procName = reader.GetString(2);
// oracle 9i, 10g and 11g treats object_name the top level function, package or procedure name
// where procedure_name contains procedure name if there is object_name defined as package
string pName = (String.IsNullOrEmpty(procName) ? objectName :
(String.IsNullOrEmpty(objectName) ? procName : objectName + "." + procName));
if (!schemaNames.Contains(owner))
{
schemaNames.Add(owner);
}
// keep all schemas and its stored proc in hash
if (!hash.ContainsKey(owner))
{
List names = new List();
names.Add(pName);
hash.Add(owner, names);
}
else
{
List names = hash[owner] as List;
if (!names.Contains(pName))
{
names.Add(pName);
}
}
}
schemaNames.Sort();
// the passed in or selected schema is invalid
if (!schemaNames.Contains(schemaName))
{
// default to the first schema name in the list
if (schemaNames.Count > 0)
schemaName = schemaNames[0];
}
List procs = hash[schemaName] as List;
foreach (String proc in procs)
{
spNames.Add(proc);
}
spNames.Sort();
}
catch (Exception ex)
{
Console.Write(ex.Message);
}
finally
{
connection.Close();
connection.Dispose();
}
}
return spNames;
}
Could you advise as what may have been wrong?
Thanks a lot.
The following error occurred: An error occured while connecting to database: Error = Arithmetic operation resulted in an overflow.; Command Type = StoredProcedure; Command= MyStoredProcedure;
Please note we have the same database in a single node environment. Everything is almost identical in this test environment comparing to the 3 node RAC production environment. All stored procedures, tables should be identical and return the same data. But the same stored procedure call worked in single node environment not in our clustered environment.
We also cannot get a list of stored procedure available from Oracle database, which we could when connecting to a single node Oracle. The following shows the code snippet for getting the list of stored procs.
public static List GetStoredProcedureNames(string connectionString, List schemaNames)
{
List spNames = new List();
string schemaName = (schemaNames.Count > 0 ? schemaNames[0] : string.Empty);
// now remove passed in schema name (invalid or valid) and repopulate it
schemaNames.Clear();
using (OracleConnection connection = new OracleConnection(connectionString))
{
try
{
CommandType commandType = CommandType.Text;
OracleDataReader reader = OracleHelper.ExecuteReader(connection,
CommandType.Text,
"select owner, object_name, procedure_name from all_procedures",
null);
Hashtable hash = new Hashtable();
while (reader.Read())
{
string owner = reader.GetString(0);
string objectName = reader.GetString(1);
string procName = reader.GetString(2);
// oracle 9i, 10g and 11g treats object_name the top level function, package or procedure name
// where procedure_name contains procedure name if there is object_name defined as package
string pName = (String.IsNullOrEmpty(procName) ? objectName :
(String.IsNullOrEmpty(objectName) ? procName : objectName + "." + procName));
if (!schemaNames.Contains(owner))
{
schemaNames.Add(owner);
}
// keep all schemas and its stored proc in hash
if (!hash.ContainsKey(owner))
{
List names = new List();
names.Add(pName);
hash.Add(owner, names);
}
else
{
List names = hash[owner] as List;
if (!names.Contains(pName))
{
names.Add(pName);
}
}
}
schemaNames.Sort();
// the passed in or selected schema is invalid
if (!schemaNames.Contains(schemaName))
{
// default to the first schema name in the list
if (schemaNames.Count > 0)
schemaName = schemaNames[0];
}
List procs = hash[schemaName] as List;
foreach (String proc in procs)
{
spNames.Add(proc);
}
spNames.Sort();
}
catch (Exception ex)
{
Console.Write(ex.Message);
}
finally
{
connection.Close();
connection.Dispose();
}
}
return spNames;
}
Could you advise as what may have been wrong?
Thanks a lot.
This may indeed be a problem with oracle client. The customer was running on an old machine that has installed oracle client long time ago and was having issue with the connection. However the same connection string used in another server works. The customer will update their oracle client on the particular machine and verify.
We were able to further narrow the issue. We have a windows application written in c#.net that uses same CoreLab.Oracle library (version 4.60.33.0) as our SharePoint custom web part. When we set both up with a connection string to the RAC oracle nodes, our windows version worked just fine but the SharePoint custom web parts do not work and give out error as indicated before:
An error occured while connecting to database: Error = Arithmetic operation resulted in an overflow.; Command Type = StoredProcedure; Command= MyStoredProcedure;
This leads us to believe that there is difference running CoreLab.Oracle library (version 4.60.33.0) in a windows process than running in a w3wp asp.net process. Please note the customer have all the servers running on x64 bit platform on windows 2008.
Please advise. Thanks.
We were able to further narrow the issue. We have a windows application written in c#.net that uses same CoreLab.Oracle library (version 4.60.33.0) as our SharePoint custom web part. When we set both up with a connection string to the RAC oracle nodes, our windows version worked just fine but the SharePoint custom web parts do not work and give out error as indicated before:
An error occured while connecting to database: Error = Arithmetic operation resulted in an overflow.; Command Type = StoredProcedure; Command= MyStoredProcedure;
This leads us to believe that there is difference running CoreLab.Oracle library (version 4.60.33.0) in a windows process than running in a w3wp asp.net process. Please note the customer have all the servers running on x64 bit platform on windows 2008.
Please advise. Thanks.
Please post here your call stack to identify the object that throws this exception. If there is no call stack via standard interface, you can obtain it by connecting from Visual Studio (Debug | Attach to Proccess) to your w3wp asp.net process. Before attaching don't forget to make these settings in your Visual Studio: a) in the (Debug | Exceptions) window check Common Language Runtime Exceptions; b) in the (Tools | Options) window, Debugging | General - uncheck Enable Just My Code (Managed Only).
Here is the stack trace:
The following error occurred: An error occured while connecting to database: Error = Arithmetic operation resulted in an overflow.; Command Type = StoredProcedure; Command= MyStoredProc;
at WA.Core.Data.OracleDatabaseDAO.Execute(ServiceInfo serviceInfo, OperationInfo operation)
Arithmetic operation resulted in an overflow.
at System.IntPtr.ToInt32()
at CoreLab.Oracle.a4.a(br[] A_0, Byte[] A_1)
at CoreLab.Oracle.OracleCommand.a(CommandBehavior A_0, IDisposable A_1, Int32 A_2, Int32 A_3)
at CoreLab.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at CoreLab.Oracle.OracleDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at WA.Core.Data.OracleHelper.ExecuteDataset(OracleConnection connection, CommandType commandType, String commandText, OracleParameter[] commandParameters)
at WA.Core.Data.OracleDatabaseDAO.Execute(ServiceInfo serviceInfo, OperationInfo operation)
The following error occurred: An error occured while connecting to database: Error = Arithmetic operation resulted in an overflow.; Command Type = StoredProcedure; Command= MyStoredProc;
at WA.Core.Data.OracleDatabaseDAO.Execute(ServiceInfo serviceInfo, OperationInfo operation)
Arithmetic operation resulted in an overflow.
at System.IntPtr.ToInt32()
at CoreLab.Oracle.a4.a(br[] A_0, Byte[] A_1)
at CoreLab.Oracle.OracleCommand.a(CommandBehavior A_0, IDisposable A_1, Int32 A_2, Int32 A_3)
at CoreLab.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at CoreLab.Oracle.OracleDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at WA.Core.Data.OracleHelper.ExecuteDataset(OracleConnection connection, CommandType commandType, String commandText, OracleParameter[] commandParameters)
at WA.Core.Data.OracleDatabaseDAO.Execute(ServiceInfo serviceInfo, OperationInfo operation)
The above mentioned stack trace is obtained from our web parts running in SharePoint environment. However running identical code inside a windows application, we are able to connect to the RAC oracle server. Note also that both windows application and web part running inside SharePoint returns successfully the result when connecting to a single node oracle server.
The following code shows where the error occurred when running inside a web part:
//create a command and prepare it for execution
using (OracleCommand cmd = new OracleCommand())
{
try
{
PrepareCommand(cmd, connection, null, commandType, commandText, commandParameters);
//create the DataAdapter & DataSet
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
//fill the DataSet using default values for DataTable names, etc.
da.Fill(ds);
cmd.Transaction.Commit();
//return the dataset
return ds;
}
catch
{
if (cmd.Transaction != null)
cmd.Transaction.Rollback();
throw;
}
finally
{
cmd.Dispose();
}
}
Below is the result when I run the same code successfully inside a windows application and got the DataSet back. I then output some info on the DataSet.
DataTable Count: 1
DataTable Name: Table
DataTable Row Count: 1
DataTable Column Count: 5
STIP_YEAR | SEQ_NUM | PROJ_EXT_PROJ_ID | MPO | MPO_PCT |
2011 | 61 | 720125- | Atlanta TMA | 11 |
2011
61
720125-
Atlanta TMA
11
As you can see the OracleDataAdapter can fill the DataSet in windows application connecting to the same RAC Oracle but not in web part running inside SharePoint. I am really curious what is the difference and what can be done to fix this problem.
Thanks in advance.
The following code shows where the error occurred when running inside a web part:
//create a command and prepare it for execution
using (OracleCommand cmd = new OracleCommand())
{
try
{
PrepareCommand(cmd, connection, null, commandType, commandText, commandParameters);
//create the DataAdapter & DataSet
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
//fill the DataSet using default values for DataTable names, etc.
da.Fill(ds);
cmd.Transaction.Commit();
//return the dataset
return ds;
}
catch
{
if (cmd.Transaction != null)
cmd.Transaction.Rollback();
throw;
}
finally
{
cmd.Dispose();
}
}
Below is the result when I run the same code successfully inside a windows application and got the DataSet back. I then output some info on the DataSet.
DataTable Count: 1
DataTable Name: Table
DataTable Row Count: 1
DataTable Column Count: 5
STIP_YEAR | SEQ_NUM | PROJ_EXT_PROJ_ID | MPO | MPO_PCT |
2011 | 61 | 720125- | Atlanta TMA | 11 |
2011
61
720125-
Atlanta TMA
11
As you can see the OracleDataAdapter can fill the DataSet in windows application connecting to the same RAC Oracle but not in web part running inside SharePoint. I am really curious what is the difference and what can be done to fix this problem.
Thanks in advance.
It seems like a bug with OCI calls on the x64 platform that was fixed in the 4.75.42 version. If your subscription has expired, we recommend you to try the latest dotConnect for Oracle Trial that can be downloaded from here. Please notify us if the problem is resolved with the latest version.
I have tried and got the same error stack. Please note the same program runs as a windows console app (written in c#.net) worked fine with the same connection string and command but failed when running inside SharePoint page. I simply copied and pasted the same code that is used in the console app in a aspx page with proper imports and assembly directives. So this leads me to believe that the library for connecting to clustered oracle servers has some problems when running inside a SharePoint or a web process. Note that the console app and the web page both run on x64 machine with MOSS 2007 installed so platform issue should not be the case.
Please advise.
Please advise.