Connection strings for Oracle

Providers for Oracle Oracle Data Provider for .NET / ODP.NET (OracleConnection)
.NET Framework Data Provider for Oracle (OracleConnection)
dotConnect for Oracle (OracleConnection)
Microsoft OLE DB Provider for Oracle
Oracle Provider for OLE DB
.NET Framework Data Provider for OLE DB (OleDbConnection)
Oracle in OraHome92
Microsoft ODBC for Oracle
Microsoft ODBC Driver for Oracle
Oracle in XEClient
.NET Framework Data Provider for ODBC (OdbcConnection)
MSDataShapeCommunity ForumsFind solutions and post questions regarding connection string related issues.Forum for Oracle >>

Oracle Data Provider for .NET / ODP.NET

Type:    .NET Framework Class Library
Usage:  Oracle.DataAccess.Client.OracleConnectionManufacturer:  Oracle
More info about this class library ?Customize string
example values ?Using TNSData Source=TORCL;User Id=myUsername;Password=myPassword;  Using integrated securityData Source=TORCL;Integrated Security=SSPI;  Using ODP.NET without tnsnames.oraData Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=MyOracleSID)));User Id=myUsername;Password=myPassword;  Using the Easy Connect Naming Method (aka EZ Connect)The easy connect naming method enables clients to connect to a database without any configuration.Data Source=username/password@//myserver:1521/my.service.com;Port 1521 is used if no port number is specified in the connection string.

Make sure that EZCONNECT is enabled in the sqlnet.ora file. NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

'//' in data source is optional and is there to enable URL style hostname values Easy Connect Naming Method to connect to an InstanceThis one does not specify a service or a port.Data Source=username/password@myserver//instancename;  Easy Connect Naming Method to connect to a dedicated server instanceThis one does not specify a service or a port.Data Source=username/password@myserver/myservice:dedicated/instancename;Other server options: SHARED, POOLED (to use instead of DEDICATED). Dedicated is the default. Specifying Pooling parametersBydefault, connection pooling is enabled. This one controls the poolingmechanisms. The connection pooling service creates connection pools byusing the ConnectionString property to uniquely identify a pool.Data Source=myOracle;User Id=myUsername;Password=myPassword;Min Pool Size=10;Connection Lifetime=120;Connection Timeout=60;Incr Pool Size=5;Decr Pool Size=2;The firstconnection opened creates the connection pool. The service initiallycreates the number of connections defined by the Min Pool Sizeparameter.

The Incr Pool Size attribute defines the number of newconnections to be created by the connection pooling service when moreconnections are needed.

When a connection is closed, theconnection pooling service determines whether the connection lifetimehas exceeded the value of the Connection Lifetime attribute. If so, theconnection is closed; otherwise, the connection goes back to theconnection pool.

The connection pooling service closes unusedconnections every 3 minutes. The Decr Pool Size attribute specifies themaximum number of connections that can be closed every 3 minutes. Restricting Pool sizeUse this one if you want to restrict the size of the pool.Data Source=myOracle;User Id=myUsername;Password=myPassword;Max Pool Size=40;Connection Timeout=60;The Max Pool Sizeattribute sets the maximum number of connections for the connectionpool. If a new connection is requested, but no connections are availableand the limit for Max Pool Size has been reached the connection poolingservice waits for the time defined by the Connection Timeout attribute.If the Connection Timeout time has been reached, and there are still noconnections available in the pool, the connection pooling serviceraises an exception indicating that the request has timed-out. Disable PoolingData Source=myOracle;User Id=myUsername;Password=myPassword;Pooling=False;  Using Windows user authenticationOracle can open a connection using Windows user login credentials to authenticate database users.Data Source=myOracle;User Id=/;If the Password attribute is provided, it is ignored.

Operating System Authentication is not supported in a .NET stored procedure. Privileged ConnectionsOracle allows database administrators to connect to Oracle Database with either SYSDBA or SYSOPER privileges.Data Source=myOracle;User Id=myUsername;Password=myPassword;DBA Privilege=SYSDBA;SYSOPER is also valid for the DBA Privilege attribute. Runtime Connection Load BalancingOptimizes connection pooling for RAC database by balancing work requests across RAC instances.Data Source=myOracle;User Id=myUsername;Password=myPassword;Load Balancing=True;This feature can only be used against a RAC database and only if pooling is enabled (default). 

.NET Framework Data Provider for Oracle

Type:    .NET Framework Class Library
Usage:  System.Data.OracleClient.OracleConnectionManufacturer:  Microsoft
More info about this class library ?Customize string
example values ?StandardData Source=MyOracleDB;Integrated Security=yes;This one works only with Oracle 8i release 3 or later Specifying username and passwordData Source=MyOracleDB;User Id=myUsername;Password=myPassword;Integrated Security=no;This one works only with Oracle 8i release 3 or laterMissing the System.Data.OracleClient namespace? Download .NET Managed Provider for OracleArticle: "Oracle Data Provider for .NET" by Rama Mohan Omiting tnsnames.oraThisis another type of Oracle connection string that doesn't rely on you tohave a DSN for the connection. You create a connection string based onthe format used in the tnsnames.ora file without the need to actuallyhave one of these files on the client pc.SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort))(CONNECT_DATA=(SERVICE_NAME=MyOracleSID)));uid=myUsername;pwd=myPassword;  Some reported problems with the one above and Visual Studio. Use the next one if you've encountered problems.Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort))(CONNECT_DATA=(SERVICE_NAME=MyOracleSID)));User Id=myUsername;Password=myPassword;  Using Connection PoolingTheconnection pooling service will create a new pool if it can't find anyexisting pool that exactly match the new connections connection stringproperties. If there is a matching pool a connection will be recycledfrom that pool.Data Source=myOracleDB;User Id=myUsername;Password=myPassword;Min Pool Size=10;Connection Lifetime=120;Connection Timeout=60;Incr Pool Size=5;Decr Pool Size=2;The firstconnection opened creates the connection pool. The service initiallycreates the number of connections defined by the Min Pool Sizeparameter.

The Incr Pool Size attribute defines the number of newconnections to be created by the connection pooling service when moreconnections are needed.

When a connection is closed, theconnection pooling service determines whether the connection lifetimehas exceeded the value of the Connection Lifetime attribute. If so, theconnection is closed; otherwise, the connection goes back to theconnection pool.

The connection pooling service closes unusedconnections every 3 minutes. The Decr Pool Size attribute specifies themaximum number of connections that can be closed every 3 minutes. Windows AuthenticationData Source=myOracleDB;User Id=/;  Privileged ConnectionWith SYSDBA privilegesData Source=myOracleDB;User Id=SYS;Password=SYS;DBA Privilege=SYSDBA;  Privileged ConnectionWith SYSOPER privilegesData Source=myOracleDB;User Id=SYS;Password=SYS;DBA Privilege=SYSOPER;  Utilizing the Password Expiration functionalityFirstopen a connection with a connection string. When the connection isopened, an error is raised because the password have expired. Catch theerror and execute the OpenWithNewPassword command supplying the newpassword.Data Source=myOracleDB;User Id=myUsername;Password=myPassword;

oConn.OpenWithNewPassword(sTheNewPassword);  Proxy AuthenticationData Source=myOracleDB;User Id=myUsername;Password=myPassword;Proxy User Id=pUserId;Proxy Password=pPassword;  

dotConnect for Oracle

Type:    .NET Framework Class Library
Usage:  Devart.Data.Oracle.OracleConnectionManufacturer:  Devart
More info about this class library ?Customize string
example values ?StandardUser ID=myUsername;Password=myPassword;Host=ora;Pooling=true;Min Pool Size=0;Max Pool Size=100;Connection Lifetime=0;  

Microsoft OLE DB Provider for Oracle

Type:    OLE DB Provider
Usage:  Provider=msdaoraManufacturer:  Microsoft
More info about this provider ?Customize string
example values ?Standard securityThis connection string uses a provider from Microsoft.Provider=msdaora;Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;  Trusted connectionProvider=msdaora;Data Source=MyOracleDB;Persist Security Info=False;Integrated Security=Yes;  

Oracle Provider for OLE DB

Type:    OLE DB Provider
Usage:  Provider=OraOLEDB.OracleManufacturer:  Oracle
More info about this provider ?Customize string
example values ?Standard SecurityProvider=OraOLEDB.Oracle;Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;  Trusted ConnectionThis one specifies OS authentication to be used when connecting to an Oracle database.Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;OSAuthent=1;  Oracle XE, VB6 ADOProvider=OraOLEDB.Oracle;dbq=localhost:1521/XE;Database=myDataBase;User Id=myUsername;Password=myPassword;  Oracle XE, C++ ADOProvider=OraOLEDB.Oracle;Data Source=localhost:1521/XE;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;  TNS-less connection stringProvider=OraOLEDB.Oracle;Data Source=(DESCRIPTION=(CID=GTU_APP)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=myHost)(PORT=myPort)))(CONNECT_DATA=(SID=MyOracleSID)(SERVER=DEDICATED)));User Id=myUsername;Password=myPassword;  Controling rowset cache mechanismSpecifiesthe type of caching used by the provider to store rowset data. OraOLEDBprovides two caching mechanisms; File and Memory.Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;CacheType=File;Memory is thedefault value. All the rowset data is stored in-memory which providesbetter performance at the expense of higher memory utilization.

File= All the rowset data is stored on disk. This caching mechanism limitsthe memory consumption at the expense of performance. Controling the fetchsizeThis one specifies the number of rows the provider will fetch at a time (fetch array).Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;FetchSize=200;The FetchSize valuemust be set appropriately depending on the data size and the responsetime of the network. If the value is set too high, this could result inmore wait time during the execution of the query. If the value is settoo low, this could result in many more round trips to the database.Valid values are 1 to 429,496,296. The default is 100. Controling the chunksizeThis one specifies the size, in bytes, of the data in LONG and LONG RAW columns fetched and stored in the provider cache.Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;ChunkSize=200;Providing a highvalue for this attribute improves performance, but requires more memoryto store the data in the rowset. Valid values are 1 to 65535. Thedefault is 100. Using with Microsofts OLE DB .NET Data ProviderTheMicrosoft OLE DB .NET Data Provider can utilize OraOLEDB as the OLE DBProvider for accessing Oracle. However this must be enabled in theconnection string.Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;OLEDB.NET=True;The OLEDB.NET connection string attribute must not be used in ADO applications. Using OraOLEDB Custom Properties with Microsofts OLE DB .NET Data ProviderTheSPPrmsLOB and NDatatype properties can only be set as connection stringattributes when OraOLEDB is used by OLE DB .NET Data Provider.Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;OLEDB.NET=True; SPPrmsLOB=False;NDatatype=False;SPPrmsLOB=False;Using ADO, theseproperties would have been set as a property on the command. This is notpossible if using the Microsofts OLE DB .NET Data Provider. So theproperties are specified in the connection string instead.

PLSQLRSet: If the stored procedure, provided by the consumer, returns a rowset, PLSQLRSet must be set to TRUE (enabled).

NDatatype:This property allows the consumers to specify whether any of theparameters bound to the command are of Oracle's N datatypes (NCHAR,NVARCHAR or NCLOB). This information is required by OraOLEDB to detectand bind the parameters appropriately. This property should not be setfor commands executing SELECT statements. However, this property must beset for all other SQLs such as INSERT, UPDATE, and DELETE.

SPPrmsLOB:This property allows the consumer to specify whether one or more of theparameters bound to the stored procedures are of Oracle's LOB datatype(CLOB, BLOB, or NCLOB). OraOLEDB requires this property to be set toTRUE, in order to fetch the parameter list of the stored procedure priorto execution. The use of this property limits the processing overheadto stored procedures having one or more LOB datatype parameters. Using distributed transactionsThis one specifies sessions to enlist in distributed transactions. This is the default behaviour.Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;DistribTX=1;Valid values are 0 (disabled) and 1 (enabled). 

.NET Framework Data Provider for OLE DB

Type:    .NET Framework Wrapper Class Library
Usage:  System.Data.OleDb.OleDbConnectionManufacturer:  Microsoft
More info about this wrapper class library ?Customize string
example values ?Bridging to Oracle Provider for OLE DBThisis just one connection string sample for the wrapping OleDbConnectionclass that calls the underlying OLEDB provider. See respective OLE DBprovider for more connection strings to use with this class.Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;OLEDB.NET=True;Note! The keyword"OLEDB.NET" must be set to "True" for the OraOLEDB.Oracle provider tofunction with the .NET Framework Data Provider for OLE DB. 

Oracle in OraHome92

Type:    ODBC Driver
Usage:  Driver={Oracle in OraHome92}Manufacturer:  Oracle
Customize string
example values ?StandardDriver={Oracle in OraHome92};Dbq=myTNSServiceName;Uid=myUsername;Pwd=myPassword;  

Microsoft ODBC for Oracle

Type:    ODBC Driver
Usage:  Driver={Microsoft ODBC for Oracle}Manufacturer:  Microsoft
More info about this driver ?Customize string
example values ?New versionDriver={Microsoft ODBC for Oracle};Server=myServerAddress;Uid=myUsername;Pwd=myPassword;  Connect directlyNo TSN nor DSN required.Driver={Microsoft ODBC for Oracle};Server=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=199.199.199.199)(PORT=1523))(CONNECT_DATA=(SID=dbName)));Uid=myUsername;Pwd=myPassword;  Without TSN.ora alternativeDriver={Microsoft ODBC for Oracle};CONNECTSTRING=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=server)(PORT=7001))(CONNECT_DATA=(SERVICE_NAME=myDb)));Uid=myUsername;Pwd=myPassword;  

Microsoft ODBC Driver for Oracle

Type:    ODBC Driver
Usage:  Driver={Microsoft ODBC Driver for Oracle}Manufacturer:  Microsoft
More info about this driver ?Customize string
example values ?Old versionDriver={Microsoft ODBC Driver for Oracle};ConnectString=OracleServer.world;Uid=myUsername;Pwd=myPassword;  

Oracle in XEClient

Type:    ODBC Driver
Usage:  Driver=(Oracle in XEClient)Manufacturer:  Oracle
Customize string
example values ?StandardOracle XE (or "Oracle Database 10g Express Edition") is a simple version that's free to distribute.Driver=(Oracle in XEClient);dbq=111.21.31.99:1521/XE;Uid=myUsername;Pwd=myPassword;  

.NET Framework Data Provider for ODBC

Type:    .NET Framework Wrapper Class Library
Usage:  System.Data.Odbc.OdbcConnectionManufacturer:  Microsoft
More info about this wrapper class library ?Customize string
example values ?Bridging to Oracle in OraHome92 ODBC DriverThisis just one connection string sample for the wrapping OdbcConnectionclass that calls the underlying ODBC Driver. See respective ODBC driverfor more connection strings to use with this class.Driver={Oracle in OraHome92};Server=myServerAddress;Dbq=myDataBase;Uid=myUsername;Pwd=myPassword;  

MSDataShape

Type:    Data Shaping COM component
Usage:  Provider=MSDataShape;Data Provider=providernameManufacturer:  Microsoft
More info about this wrapper COM component ?Customize string
example values ?MSDataShapeProvider=MSDataShape;Persist Security Info=False;Data Provider=MSDAORA;Data Source=orac;User Id=myUsername;Password=myPassword;Want to learn data shaping? Check out 4GuyfFromRolla's (old but great) article about Data Shaping Sign-in | JoinShareSelected ArticlesWhat is a connection string?Rules for connection stringsAll SQL Server SqlConnection propertiesSQL Server Data Types ReferenceCopyright ? 2011 ConnectionStrings.com   |   AllRights Reserved   |   Powered by CSAS   |   Send feedback, articles,requests and more connection strings here.添加到收藏夾 / 分享XSina WeiboFacebookFriendster收藏夾GoogleDiggTwitterMyspace打印Baidu電子郵件更多...设置AddThis