Oracle Connection strings - Connectionstring Examples

Connection strings Examples for Oracle

Oracle Data Provider for .NET / ODP.NET

This is a .NET Class Library from Oracle. The namespace for this library is Oracle.DataAccess.Client.OracleConnection.

Using TNS:

Data Source=TORCL;User Id=yourUsername;Password=yourPassword;

Using integrated security:

Data Source=TORCL;Integrated Security=SSPI;

Using ODP.NET without tnsnames.ora:

Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=yourHost)(PORT=yourPort)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=yourOracleSID)));User Id=yourUsername;Password=yourPassword;

Using the Easy Connect Naming Method (aka EZ Connect):

Data Source=username/password@//yourserver:1521/your.service.com;

EZCONNECT must be enabled in the sqlnet.ora file. NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

Easy Connect Naming Method to connect to an Instance:

Data Source=username/password@yourserver//instancename;

Easy Connect Naming Method to connect to a dedicated server instance:

Data Source=username/password@yourserver/yourservice:dedicated/instancename;

Specifying Pooling parameters:

Data Source=yourOracle;User Id=yourUsername;Password=yourPassword;Min Pool Size=10;Connection Lifetime=120;Connection Timeout=60;Incr Pool Size=5;Decr Pool Size=2;

Restricting Pool size:

Data Source=yourOracle;User Id=yourUsername;Password=yourPassword;Max Pool Size=40;Connection Timeout=60;

Disable Pooling:

Data Source=yourOracle;User Id=yourUsername;Password=yourPassword;Pooling=False;

Using Windows user authentication:

Data Source=yourOracle;User Id=/;

Privileged Connections:

Data Source=yourOracle;User Id=yourUsername;Password=yourPassword;DBA Privilege=SYSDBA;

Runtime Connection Load Balancing:

Data Source=yourOracle;User Id=yourUsername;Password=yourPassword;Load Balancing=True;

.NET Data Provider for Oracle

This is a .NET library from Microsoft. The namespace for this provider is System.Data.OracleClient.OracleConnection.

Standard Security:

Data Source=yourOracleDB;Integrated Security=yes;

Specifying username and password:

Data Source=yourOracleDB;User Id=yourUsername;Password=yourPassword;Integrated Security=no;

Omiting tnsnames.ora:

SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=yourHost)(PORT=yourPort))(CONNECT_DATA=(SERVICE_NAME=yourOracleSID)));uid=yourUsername;pwd=yourPassword;

Using Connection Pooling:

Data Source=yourOracleDB;User Id=yourUsername;Password=yourPassword;Min Pool Size=10;Connection Lifetime=120;Connection Timeout=60;Incr Pool Size=5;Decr Pool Size=2;

Windows Authentication:

Data Source=yourOracleDB;User Id=/;

Privileged Connection:

Data Source=yourOracleDB;User Id=SYS;Password=SYS;DBA Privilege=SYSDBA;

DBA Privilege can be SYSDBA or SYSOPER

Utilizing the Password Expiration functionality:

Data Source=yourOracleDB;User Id=yourUsername;Password=yourPassword;

oConn.OpenWithNewPassword(sTheNewPassword);

Proxy Authentication:

Data Source=yourOracleDB;User Id=yourUsername;Password=yourPassword;Proxy User Id=pUserId;Proxy Password=pPassword;

Oracle in OraHome92

This is an ODBC driver from Oracle. The usage for this driver is Driver={Oracle in OraHome92}.

Standard Security:

Driver={Oracle in OraHome92};Server=yourServerAddress;Dbq=yourDataBase;Uid=yourUsername;Pwd=yourPassword;

Oracle in XEClient

This is an ODBC driver from Oracle. The usage for this driver is Driver=(Oracle in XEClient).

Standard Security:

Driver=(Oracle in XEClient);dbq=111.21.31.99:1521/XE;Uid=yourUsername;Pwd=yourPassword;

Microsoft OLE DB Provider for Oracle

This is an OLE DB provider from Microsoft. The usage for this provider is Provider=msdaora.

Standard Security:

Provider=msdaora;Data Source=yourOracleDB;User Id=yourUsername;Password=yourPassword;

Trusted connection:

Provider=msdaora;Data Source=yourOracleDB;Persist Security Info=False;Integrated Security=Yes;

Oracle Provider for OLE DB

This is an OLE DB provider from Oracle. The usage for this provider is Provider=OraOLEDB.Oracle.

Standard Security:

Provider=OraOLEDB.Oracle;Data Source=yourOracleDB;User Id=yourUsername;Password=yourPassword;

Trusted connection:

Provider=OraOLEDB.Oracle;Data Source=yourOracleDB;OSAuthent=1;

TNS-less connection string:

Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION=(CID=GTU_APP)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=yourHost)(PORT=yourPort)))(CONNECT_DATA=(SID=yourOracleSID)(SERVER=DEDICATED)));User Id=yourUsername;Password=yourPassword;

.NET Data Provider for OLE DB

This is an .NET class library from Microsoft. The namespace for this provider is System.Data.OleDb.OleDbConnection.

Standard Security:

Provider=OraOLEDB.Oracle;Data Source=yourOracleDB;User Id=yourUsername;Password=yourPassword;

Note: This is a wrapper class

Microsoft ODBC for Oracle

This is an ODBC driver from Microsoft. The usage for this provider is Driver={Microsoft ODBC for Oracle}.

New Version:

Driver={Microsoft ODBC for Oracle};Server=yourServerAddress;Uid=yourUsername;Pwd=yourPassword;

Microsoft ODBC Driver for Oracle

This is an ODBC driver from Microsoft. The usage for this provider is Driver={Microsoft ODBC Driver for Oracle}.

Old version:

Driver={Microsoft ODBC Driver for Oracle};ConnectString=OracleServer.world;Uid=yourUsername;Pwd=yourPassword;

dotConnect for Oracle

This is .NET class library from Devart . The namespace for this provider is Devart.Data.Oracle.OracleConnection.

Standard Security:

User ID=yourUsername;Password=yourPassword;Host=ora;Pooling=true;Min Pool Size=0;Max Pool Size=100;Connection Lifetime=0;

MSDataShape

This is a Data Shaping COM component from Microsoft. The usage for this provider is Provider=MSDataShape and Data Provider=providername.

MSDataShape:

Provider=MSDataShape;Persist Security Info=False;Data Provider=MSDAORA;Data Source=orac;User Id=yourUsername;Password=yourPassword;