Oracle odbc строка подключения
This is another type of Oracle connection string that doesn't rely on you to have a DSN for the connection. You create a connection string based on the format used in the tnsnames.ora file without the need to actually have 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;
Omiting tnsnames.ora Alternative
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;
Trusted connection
Provider = msdaora; Data Source = MyOracleDB; Persist Security Info = False; Integrated Security = Yes;
В этом разделе показано, как подключаться к источникам данных Oracle со страницы Выбор источника данных или Выбор назначения в мастере импорта и экспорта SQL Server. Для подключения к Oracle можно использовать ряд поставщиков данных.
Подробные требования и необходимые условия для подключения к базе данных Oracle выходят за рамки этой статьи Майкрософт. В ней предполагается, что у вас уже установлено клиентское программное обеспечение Oracle и вы можете успешно подключиться к целевой базе данных Oracle. Для получения дополнительных сведений обратитесь к администратору базы данных Oracle или к документации по Oracle.
Параметры подключения для этого поставщика данных одинаковы независимо от того, является ли Oracle источником или назначением. Таким образом, на страницах Выбор источника данных и Выбор назначения мастера отображаются одинаковые параметры.
Вам не нужно вводить строку подключения в поле ConnectionString списка. После ввода отдельных значений для имени сервера Oracle (источника данных) и информации для входа мастер собирает строку подключения из отдельных свойств и их значений.
Using the Easy Connect Naming Method (aka EZ Connect)
The easy connect naming method enables clients to connect to a database without any configuration.
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
Standard
TNS-less connection string
Provider = 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;
Both Server and Dbq
Some feedback on using both Dbq and Server keywords to make this work.
Microsoft OLE DB Provider for Oracle
Utilizing the Password Expiration functionality
First open a connection with a connection string. When the connection is opened, an error is raised because the password have expired. Catch the error and execute the OpenWithNewPassword command supplying the new password.
Using distributed transactions
This 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).
Другие поставщики данных и дополнительные сведения
Сведения о подключении к Oracle с помощью поставщика данных, не представленного в этом списке, см. в разделе Строки подключения Oracle. Этот сторонний сайт также содержит дополнительные сведения о поставщиках данных и параметрах подключения, описанных на этой странице.
Эта функция будет удалена в следующей версии Windows. Избегайте использования этого компонента в новых разработках и запланируйте изменение существующих приложений, в которых он применяется. Вместо этого используйте драйвер ODBC, предоставляемый Oracle.
Вместо использования диалогового окна некоторым приложениям может потребоваться строка подключения, указывающая сведения о соединении с источником данных. Строка подключения состоит из нескольких атрибутов, которые определяют способ подключения драйвера к источнику данных. Атрибут определяет конкретный фрагмент информации, который должен быть определен драйвером, прежде чем он сможет создать соответствующее соединение с источником данных. Каждый драйвер может иметь другой набор атрибутов, но формат строки подключения всегда один и тот же. Строка подключения имеет следующий формат: .
Драйвер Microsoft ODBC для Oracle поддерживает формат строки подключения для первой версии драйвера, который использовался CONNECTSTRING вместо SERVER= .
при подключении к поставщику источника данных, который поддерживает проверку подлинности Windows, следует указать Trusted_Connection=yes в строке подключения вместо сведений об идентификаторе пользователя и пароле.
Необходимо указать имя источника данных, если не указать атрибуты UID, PWD, SERVER (или CONNECTSTRING) и DRIVER. Однако все остальные атрибуты являются необязательными. Если атрибут не указан, то по умолчанию этот атрибут имеет значение, указанное на вкладке соответствующая DSN в диалоговом окне Администратор источников данных ODBC . Значение атрибута может быть с учетом регистра.
Ниже приведены атрибуты для строки подключения.
Например, строка подключения, которая подключается к источнику данных MyDataSource с помощью сервера Мйораклесервероракле и пользователя Oracle Мюсерид, будет выглядеть так:
Строка подключения, которая подключается к источнику данных Мйосердатасаурце с помощью проверки подлинности операционной системы и сервера Мйосерораклесервероракле, будет выглядеть так:
Please help. I have researched this for hours. I get some parts to work but not others.
What I am trying to do is write all the connection strings in excel VBA to connect to Oracle 11g database. I don't want to set up the User DSN in ODBC Administrator and I don't want to have to maintain a tnsnames.ora file.
I can get this to work for OLEDB connection strings but I believe this is no longer supported by Oracle so I want to use the Oracle ODBC Driver commands only.
This is what I have got to work (which requires a tnsnames.ora file)
I have tried this also but I get a TNS Protocol error
Other useful information might be that i get my connections to work just fine when I use the DSN name as per the ODBC Administrator.
Any suggestions would be greatly appreciated
Are you able to do TNSPING to the database? Are all the components (Excel, Oracle ODBC Driver) on 64 bit (or all on 32 bit)?
Standard Security
Easy Connect Naming Method to connect to a dedicated server instance
This 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.
Using Connection Pooling
The connection pooling service will create a new pool if it can't find any existing pool that exactly match the new connections connection string properties. If there is a matching pool a connection will be recycled from 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 first connection opened creates the connection pool. The service initially creates the number of connections defined by the Min Pool Size parameter.
The Incr Pool Size attribute defines the number of new connections to be created by the connection pooling service when more connections are needed.
When a connection is closed, the connection pooling service determines whether the connection lifetime has exceeded the value of the Connection Lifetime attribute. If so, the connection is closed; otherwise, the connection goes back to the connection pool.
The connection pooling service closes unused connections every 3 minutes. The Decr Pool Size attribute specifies the maximum number of connections that can be closed every 3 minutes.
Runtime Connection Load Balancing
Optimizes 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).
Using integrated security
Data Source = (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=MyOracleSID))); User Id = myUsername; Password = myPassword;
Oracle in OraClient11g_home1
Proxy Authentication
Data Source = myOracleDB; User Id = myUsername; Password = myPassword; Proxy User Id = pUserId; Proxy Password = pPassword;
Oracle XE, C++ ADO
Provider = OraOLEDB.Oracle; Data Source = localhost:1521/XE; Initial Catalog = myDataBase; User Id = myUsername; Password = myPassword;
Privileged Connection with SYSDBA privileges
Restricting Pool size
Use 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 Size attribute sets the maximum number of connections for the connection pool. If a new connection is requested, but no connections are available and the limit for Max Pool Size has been reached the connection pooling service waits for the time defined by the Connection Timeout attribute. If the Connection Timeout time has been reached, and there are still no connections available in the pool, the connection pooling service raises an exception indicating that the request has timed-out.
3 Answers 3
OLEDB Provider from Oracle ( Provider=OraOLEDB.Oracle ) is still supported, just the provider from Microsoft ( Provider=msdaora ) is deprecated. Microsoft recommends to use the Oracle provider.
Microsoft provider msdaora does even not exist for 64 Bit.
I think your connection string for Oracle ODBC must be this (without the line breaks):
Note, for the Oracle driver you must use DBQ instead of Server .
Server is the attribute for the Microsoft ODBC driver (e.g. Driver= )
This now works. I didn't need the entire tnsnames entry. Basically the code was amended to con.ConnectionString = "DRIVER=
ODBC is more generic (you have ODBC driver even for CSV files),thus you may have some limitations when you work with Oracle. You should use it when you have to work with a various number of different data sources. OLE DB is more specific to RDBMS, I think OLE DB brings more gain (at least on paper, no idea about the practical differences) when you work with Oracle DB.
There are no possibility to connect using Oracle ODBC driver without TNS alias already configured in tnsnames.ora file.
All configuration steps mentioned in Oracle ODBC Driver documentation requires it:
Also in documentation for SQLDriverConnect implementation DBQ parameter noted as required, and passing a name of TNS alias in this parameter is only method to specify server to connect to.
Because it's a common API function used by all clients of Oracle ODBC Driver, there are no possibility that some other interface (COM-object, configuration dialog or something else) may accept different parameters for connection.
There are no such possibility in Oracle Objects for OLE too.
Also, it works (at least for me):
Based on the above, I recommend to stay with OLEDB Provider, just update ODAC to a latest version.
If you don't want to use OLEDB at all, there are a variant with Microsoft ODBC driver( "Driver=;" ) with detailed server parameters specification in CONNECTSTRING :
Of course, this variant has its own drawbacks. At least, there are a much more probability of support cancellation from Microsoft's side than from side of Oracle.
This article explains how to connect to Oracle with ODBC when using Windows 10.
It covers the following topics:
- How to connect to Oracle (with code samples).
- How to setup ODBC:
- Configure ODBC using the ODBC Data Source Manager:
We recommend using system DSNs (available to all users).
We strongly recommend using connection objects to connect to databases, and this is what we demonstrate in the code samples.
Follow these steps to connect:
- Create a new channel to try out the code:
- Create a channel like this:
- Name: DB Oracle Connection (or similar)
- Source: From Translator
- Destination: To Channel
- Alternatively use an existing channel:
- Create a channel like this:
Your channel will need a From/To Translator or a Filter component.
If your script uses connection object methods conn:query<> or conn:execute<> to connect to a remote Oracle database, the location of the remote host is specified as part of the name parameter in the db.connect < >command. If you use conn:merge<> to connect the remote host is specified as part of the name parameter in the conn:merge<> command itself.
Note: Iguana may not always support the very latest version of Oracle drivers, so you can simply download an earlier version if necessary. This is particularly true if you are not able to use the the latest version of Iguana.
To set up a new ODBC data source for Oracle Server:
-
Setup the Oracle instant client OCI drivers:
The Oracle ODBC package depends on the OCI drivers — so you need to set them up first.
Windows 10 (64 bit version) supports 32 and 64 bit ODBC sources — always use the 64 bit ODBC Administrator.
- Problems with connection settings:
Speak to your DBA (database administrator) for general connection issues like: User name and password, Database Server network name etc.
Login authentication errors:
Authentication errors usually relate to how the Oracle Server you are connecting to is setup. You will need to talk to the DBA (database administrator) that manages the Oracle server.
If you wish to use Windows integrated security speak to your DBA (database administrator).
Controling the fetchsize
This 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 value must be set appropriately depending on the data size and the response time of the network. If the value is set too high, this could result in more wait time during the execution of the query. If the value is set too low, this could result in many more round trips to the database. Valid values are 1 to 429,496,296. The default is 100.
Oracle XE, VB6 ADO
Provider = OraOLEDB.Oracle; dbq = localhost:1521/XE; Database = myDataBase; User Id = myUsername; Password = myPassword;
Server SID
Oracle Provider for OLE DB
Ввод строки подключения
Введите строку подключения в поле ConnectionString либо введите имя DSN в поле Dsn на странице Выбор источника данных или Выбор назначения. После того как вы введете строку подключения, мастер проанализирует ее и отобразит отдельные свойства и их значения в списке.
Ниже показан экран, который появляется после ввода строки подключения.
Omiting tnsnames.ora Alternative
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;
Standard
User ID = myUsername; Password = myPassword; Host = ora; Pooling = true; Min Pool Size = 0; Max Pool Size = 100; Connection Lifetime = 0;
Using Windows user authentication
Oracle 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.
Privileged Connection with SYSOPER privileges
Oracle in OraHome92
Easy Connect Naming Method to connect to an Instance
This one does not specify a service or a port.
Data Source = username/password@myserver//instancename;
Disable Pooling
Подключение к Oracle с помощью драйвера Microsoft ODBC для Oracle
Windows Authentication
Specifying Pooling parameters
By default, connection pooling is enabled. This one controls the pooling mechanisms. The connection pooling service creates connection pools by using 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 first connection opened creates the connection pool. The service initially creates the number of connections defined by the Min Pool Size parameter.
The Incr Pool Size attribute defines the number of new connections to be created by the connection pooling service when more connections are needed.
When a connection is closed, the connection pooling service determines whether the connection lifetime has exceeded the value of the Connection Lifetime attribute. If so, the connection is closed; otherwise, the connection goes back to the connection pool.
The connection pooling service closes unused connections every 3 minutes. The Decr Pool Size attribute specifies the maximum number of connections that can be closed every 3 minutes.
Controling the chunksize
This 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 high value for this attribute improves performance, but requires more memory to store the data in the rowset. Valid values are 1 to 65535. The default is 100.
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 the parameters bound to the command are of Oracle's N datatypes (NCHAR, NVARCHAR or NCLOB). This information is required by OraOLEDB to detect and bind the parameters appropriately. This property should not be set for commands executing SELECT statements. However, this property must be set for all other SQLs such as INSERT, UPDATE, and DELETE.
SPPrmsLOB: This property allows the consumer to specify whether one or more of the parameters bound to the stored procedures are of Oracle's LOB datatype (CLOB, BLOB, or NCLOB). OraOLEDB requires this property to be set to TRUE, in order to fetch the parameter list of the stored procedure prior to execution. The use of this property limits the processing overhead to stored procedures having one or more LOB datatype parameters.
Oracle in XEClient
Trusted Connection
This one specifies OS authentication to be used when connecting to an Oracle database.
Provider = OraOLEDB.Oracle; Data Source = MyOracleDB; OSAuthent = 1;
Controling rowset cache mechanism
Specifies the type of caching used by the provider to store rowset data. OraOLEDB provides two caching mechanisms; File and Memory.
Provider = OraOLEDB.Oracle; Data Source = MyOracleDB; User Id = myUsername; Password = myPassword; CacheType = File;
Memory is the default value. All the rowset data is stored in-memory which provides better performance at the expense of higher memory utilization.
File = All the rowset data is stored on disk. This caching mechanism limits the memory consumption at the expense of performance.
dotConnect for Oracle
Указываемые параметры (драйвер ODBC для Oracle)
Параметры подключения для этого поставщика данных и драйвера ODBC одинаковы независимо от того, является ли сервер Oracle источником или назначением. Таким образом, на страницах Выбор источника данных и Выбор назначения мастера отображаются одинаковые параметры.
Чтобы подключиться к Oracle с помощью драйвера ODBC для Oracle, соберите строку подключения, используя указанные ниже параметры и их значения. Полный формат строки подключения приведен после списка параметров.
Вы можете получить помощь в построении строки подключения. Кроме того, вместо указания строки подключения вы можете предоставить существующее имя DSN (имя источника данных) или создать новое. Дополнительные сведения об этих возможностях см. в разделе Подключение к источнику данных ODBC.
Драйвер
Имя драйвера ODBC — Microsoft ODBC for Oracle.
Server
Имя сервера Oracle.
Uid и Pwd
Идентификатор пользователя и пароль для подключения.
Using TNS
Формат строки подключения
Ниже приведен формат типичной строки подключения.
Standard
Oracle 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;
Какое имя у моего сервера Oracle?
Выполните один из следующих запросов, чтобы узнать имя сервера Oracle.
SELECT host_name FROM v$instance
SELECT sys_context('USERENV','SERVER_HOST') FROM dual
Standard
Standard security
This connection string uses a provider from Microsoft.
Provider = msdaora; Data Source = MyOracleDB; User Id = myUsername; Password = myPassword;
Privileged Connections
Oracle 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.
Читайте также: