Devart Forums

Simon C

What are the performance benefits of using connection pooling (in terms of time to actually set up a connection), and what are the differences between devart connection pooling and oci connection pooling? Are there situations where you should one or the other, or disable pooling completely?

Simon C

The code I'm using is (in a standalone console app):

Code: Select all

const string scripttext = @"
create or replace procedure proc1 as
begin
  null;
end;
/";

using (var conn = new OracleConnection("Data Source=SC_11GDB1;user=xxxx;Password=xxxx")) {
    OracleScript script = new OracleScript(scripttext, conn);

    conn.Open();
    script.Execute();
}

It may not parse it, but I would expect it to have the same behaviour as sql developer...

Simon C

When I run the following script in SQL developer:

Code: Select all

create or replace procedure proc1 as
begin
  null;
end;
/

I get the following stored in user_source (with line endings shown):

Code: Select all

create or replace procedure proc1 as\n
begin\n
  null;\n
end;

But if I run the same script using OracleScript (devart dll version 5.35.57.0), I get this stored in the database:

Code: Select all

create or replace procedure proc1 as\n
begin\n
  null;\n
end;\n

How do I get the same behaviour with regards to line endings between sql developer and oraclescript?

Simon C

I determine the homes each oracle connection is using via the Homes property on OracleConnection in the VS debug view (which is set to what it should be)

Simon C

With ORACLE_HOME unset, I am able to connect to DB1 with the sqlplus exe in home1, and to DB2 with the sqlplus exe in home2. Vice versa produces a ORA-12154 error. No special settings were required.

I am unable to do the same using devart OracleConnection and specifying the Home property to the home I wish to use - it always uses the default home (in my case, home2)

Unfortunately, I cannot use direct connection within my application, as users need to specify the TNS identifier they wish to connect to.

Simon C

I'm referring to the ORACLE_HOME environment variable - does setting this for the app affect what OracleConnection does? Does this need to be set as well as specifying the OracleConnection.Home property?

- Both oracle clients are 10.2.0.1.0, both server instances are 10.2.0.3.0
- I do not use a single connection string, I specify connection details using properties on the OracleConnection class, as in the code examples above
- The error is ORA-12154: TNS:could not resolve the connect identifier specified when it tries to open the db2Conn connection

Simon C

Ah, apologies. sqlplus on home2 wasn't set up correctly. Setting ORACLE_HOME to each home allows me to connect to the corresponding database using each tns identifier. Do I need to set the ORACLE_HOME environment variable to do the same with devart?

Are you able to reproduce this issue on your systems?

Simon C

Still get the same error (ORA-12154) with the following code:

Code: Select all

using (OracleConnection db1Conn = new OracleConnection { Home = "OraClient10g_home1", Server = "DB1", UserId = "xxxx", Password = "xxxx" })
{
    db1Conn.Open();
}

using (OracleConnection db2Conn = new OracleConnection { Home = "OraClient10g_home2", Server = "DB2", UserId = "xxxx", Password = "xxxx" })
{
    db2Conn.Open();
}

This is the contents of tnsnames.ora in OraClient10g_home1:

Code: Select all

DB1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = simonctest.testnet)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = 10gR2db1)
    )
  )

and in OraClient10g_home2:

Code: Select all

DB2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = simonctest.testnet)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = 10gR2db2)
    )
  )

However, I do get the same error when I try to connect to DB2 using sqlplus in home2. Is this a limitation of the oracle client itself?

Simon C

Even though it's called on a particular OracleHome instance? Is there any way of getting tns aliases of non-default homes?

Simon C

Both oracle homes have a tnsnames.ora file, but they have different entries in them. For the second connection, I believe it's trying to use the first oracle home tns entry in the second connection, which doesn't exist (hence the error)

Simon C

If you run the following code on a computer with two or more oracle homes installed, the second connection fails as it cannot resolve the connection identifier - it seems it's getting confused between the two oracle homes. This is using dotConnect 5.35.57.0. What am I doing wrong here?

Code: Select all

IList homes = OracleConnection.Homes;

OracleHome home1 = (OracleHome)homes[0];
OracleHome home2 = (OracleHome)homes[1];

string db1 = home1.GetServerList()[0];
string db2 = home2.GetServerList()[0];

using (OracleConnection db1Conn = new OracleConnection {Home = home1.Name, Server = db1, UserId = "xxxx", Password = "xxxx"}) {
    db1Conn.Open();
}

using (OracleConnection db2Conn = new OracleConnection {Home = home2.Name, Server = db2, UserId = "xxxx", Password = "xxxx"}) {
    db2Conn.Open();
}

Devart Forums

Search found 11 matches

Benefits of using connection pooling

OracleScript inserting extra line endings

dotConnect getting confused between multiple oracle homes