Настройка ODBC и подключение к базе данных Oracle.
Создание подключения к базе Oracle через ODBC с использованием DSN (Data Source Name)
1. Перед установкой необходимо выяснить
1.1. Битность WINDOWS: 32x или 64x.
1.2. Битность Excel: 32x или 64x.
1.3. Версию Oracle. Можно посмотреть запросом:
SELECT * FROM V$VERSION
Рекомендую (но не обязательно) использовать Excel с такой же битностью, как и
Windows, например, MS Office x64 и Windows 10 x64.
В соответствии с битностью Excel и версией Oracle скачать архивы "Basic Package" и
"ODBC Package" по ссылкам:
для Excel 32x:
https://www.oracle.com/ru/database/technologies/instant-client/microsoft-windows-32-downloads.html
для Excel 64x:
https://www.oracle.com/ru/database/technologies/instant-client/winx64-64-downloads.html#close
Не забудьте установить также Microsoft Visual Studio по вышеперечисленным ссылкам,
там есть ссылки на скачивание в зависимости от версии Oracle и битности Excel.
Файлы из архивов "Basic Package" и "ODBC Package" надо распаковать в одну папку,
например: C:\instantclient_12_2
Далее в этой папке следует запустить файл "odbc_install.exe" (если вы скачали
32-битный клиент, но используете Windows 10, то обязательно поставьте режим запуска
совместимый с Windows 8 (или 7), в свойствах файла "odbc_install.exe").
2. Далее необходимо найти расположение файла TNSNAMES.ORA (с помощью поиска в вашей
папке, куда установлен Oracle). Может найтись два файла, выберите тот, что больше,
скорее всего в нем есть актуальная информация о подключении к базе.
И вы можете взять имя TNS из этого файла. Либо спросите у своего системного
администратора данную информацию.
Вот пример блока кода с TNS-именем "ORCLPDB" одного из моих подключений:
ORCLPDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orclPDB)
)
)
3. После установки компонентов Oracle: Basic Package и ODBC Package и определения
имени TNS необходимо запустить файл настройки ODBC соединения, например:
- если Excel 32x, то открыть C:\Windows\System32\odbcad32.exe
- если Excel 64x, то открыть C:\Windows\SysWOW64\odbcad32.exe
И нажать добавить.
Кстати, если вы установите оба драйвера (32х и 64х) из пункта 1, то в окне
"Create New Data Source" (создание нового источника данных), например:
для 64х Excel следует выбрать драйвер "Oracle in OraDB12Home1" (12.02.00.01)
для 32x Excel следует выбрать драйвер "Oracle in instantclient_12_2" (12.02.00.01)
И ввести необходимые данные, например для моего TNS-имени можно создать следующее:
Data Source Name = DSN_ORCLPDB (здесь пишется на ваше усмотрение на английском)
Description = D_ORCLPDB (здесь пишется на ваше усмотрение на английском)
TNS Service Name = ORCLPDB (здесь пишется имя TNS)
User ID = MyUser (это название схемы)
Далее нажмите кнопку Test Connection, появится окно с пустым полем пароля, куда
необходимо ввести ваш пароль, привязанный к вашему User ID и нажмите OK. Должно
появится окно с уведомлением об успешном подключении "Connection successful".
Кстати, если вы используете PL/SQL Developer, то в нем при входе на форме:
Database это "TNS Service Name", Username это "User ID".
4. Далее необходимо создать пользовательскую (не системную) переменную среды:
имя переменной = TNS_ADMIN
значение переменной = здесь указывается местоположение вашего файла tnsnames.ora,
например C:\app\myu\product\12.2.0\dbhome_1\network\admin
5. Далее в файле Excel, в котором планируете использовать подключение, необходимо
перейти в среду разработки Visual Basic и в меню: Tools - References активировать
библиотеки (поставить галочки):
Microsoft ActiveX Data Objects 2.8 Library
Microsoft ActiveX Data Objects Recordset 2.8 Library
Либо можно выбрать:
Microsoft ActiveX Data Objects 6.1 Library
Microsoft ActiveX Data Objects Recordset 6.0 Library
6. Далее необходимо написать код в вашем файле с подключением к базе данных.
Но вы можете скачать уже ГОТОВЫЙ файл, в котором требуется ввести Data Source Name,
User ID и пароль к этому User ID.
Скачать: Файл Excel с возможностью выполнения запросов в БД Oracle
Либо, откройте этот файл и посмотрите процедуру
ConnectToOracle64bit, в которой будет понятно, что делать с данными из DSN
подключения, которые вы создали в пункте 3.
Файл НЕ содержит вредоносных макросов.
P.S. Также в файле есть процедура ConnectToOracle32bit, но она создана
для другого типа подключения: путем регистрации библиотеки для провайдера MSDAORA.1.
MSDAORA.1 служит для 32-битного Excel, если не получится сделать DSN для
32-битного Excel. В любом случае, пытайтесь создать ODBC подключение, как описано
выше.
7. Процедура, применяемая для подключения с использованием DSN:
Public Sub ConnectToOracle()
Dim cn As New ADODB.Connection
Set cn = CreateObject("ADODB.Connection")
cn.Open "DSN=MyServer;Data Source=MyServer;User Id=MyID;Password=MyPassword"
Dim result As New ADODB.Recordset
Set result = CreateObject("ADODB.Recordset")
result.Open "SELECT * FROM DUAL", cn
For iCols = 0 To result.Fields.Count - 1
ActiveSheet.Cells(1, iCols + 1).Value = result.Fields(iCols).Name
Next
ActiveSheet.Range(ActiveSheet.Cells(1, 1), _
ActiveSheet.Cells(1, result.Fields.Count)).Font.Bold = True
ActiveSheet.Range("A2").CopyFromRecordset result
'ili mojno vstavit` dannie v zaranee sozdannyy dinami4eskyy
'tablicy (naprimer "myTable")
'Range("" + myTable + "").CopyFromRecordset myRes
result.Close
cn.Close
End Sub
Теперь при выполнении этого кода будет выполняться запрос в строке result.Open.
Запрос можно выводить в MsgBox
result.GetString или
ActiveSheet.Range("A1").CopyFromRecordset result, либо использовать
вышеприведенный код полностью, что наиболее оптимально.
Дополнительно вы можете посмотреть данное видео:
https://youtu.be/Adz0zZFePf8