Excel подключение к oracle
Обновлено: 03.07.2024
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 (это название схемы)
Далее нажмите кнопку 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.
Либо, откройте этот файл и посмотрите процедуру
ConnectToOracle64bit, в которой будет понятно, что делать с данными из DSN
подключения, которые вы создали в пункте 3.
Файл НЕ содержит вредоносных макросов.
1) Настраиваем ODBC источник (System DSN), называем его EMPXLS. В качестве Workbook указываем путь к emp.xls.
2) Выполняем скрипт если он еще не был выполнен
Проверить, был ли выполнен скрипт, можно след. образом
3) Редактируем файл listener.ora, добавляем запись о новом сервисе EMPXLS
4) Создаем и редактируем файл
Имя файла должно точно соответствовать init<SID>.ora, где <SID> это имя сервиса прописанного в listener.ora.
HS_FDS_CONNECT_INFO=EMPXLS -- имя odbc источника
5) Редактируем файл tnsnames.ora, добавляем запись о EMPXLS
Файл должен находится на сервере с базой, так как база выступает клиентом обращающимся к hs агенту.
6) Перегружаем listener и делаем проверку
7) Создаем dblink в базе
8) Запрашиваем имена таблиц и колонок
9) Запрашиваем данные. Используем двойные кавычки, чтобы сохранить регистр в именах таблиц и колонок.
10) Закрываем dblink, перед этим необходимо закончить транзакцию, даже если это был select
Особенности использования hsodbс при GLOBAL_NAMES = TRUE.
Если база в режиме GLOBAL_NAMES, то необходимы следующие дополнительные настройки.
1) Указать имя источника и домена в %ORACLE_HOME%\hs\admin\initEMPXLS.ora
2) Указать домен по умолчанию в sqlnet.ora
3) Добавить имя домена к TNS алиасу в tnsnams.ora
4) Убедиться, что GLOBAL_NAME базы включает имя домена.
если нет, то выполнить команду
5) Создаем dblink в базе
При создании к dblink автоматически должно быть добавлено имя домена.
6) Теперь dblink работает в режиме GLOBAL_NAMES
1) Если Excel документ находится на сетевом диске
Пользователь, от имени которого запущены сервисы Oracle, должен иметь
необходимые права доступа на этот сетевой диск/документ. По умолчанию сервис Oracle в Windows запускается от имени администратора локальной машины, поэтому следует запускать его от имени доменного пользователся, имеющего необходимые права доступа.
2) Одновременная работа с документом в нескольких сессиях
При работе с Excel документом в режиме чтение/запись через ODBC драйвер, только один пользователь может работать с ним одновременно, или, иными словами, в таком случае ODBC драйвер открывает документ в монопольном режиме. Поэтому, если DB Link уже открыт в одной сессии, то при попытке обратиться к нему из другой сессии возникает ошибка:
Если для работы достаточно режима только чтение, в настройках ODBC источника следует указать опцию read only. Тогда несколько сессий смогут читать из него одновременно.
3) Извлечение данных из документа со сложной или не четко упорядоченной структурой данных
Иногда, Excel документ представляет собой настоящий "винигрет" из данных, элементов красочного оформления, пояснений, комментариев, свободного текста итп. Читать данные из такого документа в Oracle не всегда получается легко. В этом случае рекомендуется создавать дополнительный лист, где будут храниться очищенные данные в строгой табличной форме. Обновление данных в этом вспомогательном листе можно автоматизировать, задав их зависимость от исходных данных. Таким образом, с одной стороны, не накладываются дополнительные ограничения на пользователей, работающих с исходным листом, с другой, обеспечивается строгая структуризация данных для их беспроблемного извлечения в Oracle.
Стоит добавить сюда, что начиная с 11.2 (можт и 11.1 - не пробовал) вместо
PROGRAM = hsodbc
нужно писать
PROGRAM = dg4odbc
<СПРАШИВАЛ>hellhound 26 ноября 2008, 14:12 Оценка: N/A
Люди подскажите, я неопытный. Для чего комит при селекте?</СПРАШИВАЛ>
Commit -- закончить транзакцию перед закрытием DBLINKa
alter session close database link EMPXLS;--закрываем линк
commit;--ИМХО ещераз коммит чтобы отсоединить линк и использовать в других сессиях.
Протестил на примере подключения к *.DBF
Пока линк не отцепишь - не дает удалить dbf файл.
На основе селекта можно создать представления которыми можно пользоваться в других приложениях без изврата с dblinkami
Я пытаюсь подключиться к базе данных Oracle на нашем сервере с листа Excel, но я не понимаю, почему.
Я в настоящее время имеют 32-разрядные и 64-разрядные Oracle 12c установлены в разных ORACLE_HOME и имеют 32-разрядную установку Excel на моем 64-разрядном компьютере.
Я пытаюсь подключиться к базе данных Oracle в Excel с помощью мастера создания запросов.
Я могу продолжить работу с моим текущим провайдером, но когда я на самом деле пытаюсь подключиться к базе данных, я получаю следующее:
Я не понимаю, почему я получаю эту ошибку, когда у меня установлены 32-разрядные и 64-разрядные версии Oracle 12c, установленные на моем компьютере. Оба включены в мою PATH (сначала 32-битные), и я также включил определенный ORACLE_HOME и TNS_ADMIN чтобы указать на мою 32-битную установку, так как мой excel 32-разрядный.
Мне очень нужна возможность запроса из базы данных через Excel, но эта проблема сбивает меня с толку.
РЕДАКТИРОВАТЬ
Я также добавил драйвер ODBC из моего дома Oracle в инструмент администратора источника данных ODBC.
В настоящее время он может подключаться к этому инструменту.
Но когда я пытаюсь подключиться к работе с мастером подключения ODBC, я знаю, что это:
Как вы установили 32-битный и 64-битный клиент Oracle?
Ваше преимущество - 32 бит, и вы пытаетесь использовать 32-битный Oracle. Я предполагаю, что вы запускаете 64-битную версию "Администратора ODBC" - возможно, это несоответствие. Или у вас есть проблема в вашей PATH относительно %ORACLE_HOME% и/или %ORACLE_HOME%\bin папки
Ваш Excel 32-битный, так что в целом вы сделали правильный подход, поставив 32-битный клиент в PATH и ORACLE_HOME , вы не можете смешивать 32-битные и 64-битные сборки в одном процессе. Кстати, когда вы будете следовать инструкциям выше, Windows будет управлять этим автоматически.
Возможно, у вас есть 2 драйвера ODBC, один из которых обычно называют Oracle в OraClient12_home1, а один из Microsoft называется Microsoft ODBC для Oracle (который должен быть установлен по умолчанию при установке Windows, однако для этого также требуется Oracle Client).
Драйвер ODBC от Oracle доступен для 32-битного и 64-битного, драйвер Microsoft выходит только на 32-разрядный. У вас есть 2 администратора ODBC, 32 бит (запустите c:\Windows\SysWOW64\odbcad32.exe ) и 64 бит (запустите c:\Windows\System32\odbcad32.exe ). Там вы должны увидеть установленные драйверы для 32 или. 64-битная.
Поставщик данных Oracle
другие
И последнее, но не менее важное: у вас также есть поставщик OLE DB. Опять один из Microsoft (поставщик Microsoft OLE DB для Oracle) и один из Oracle (Oracle Provider for OLE DB). Поставщик Microsoft существует только для 32 бит и устарел.
Задача. У вас есть файл Excel – и вы хотите, чтобы эти данные были помещены в таблицу. Я покажу вам, как это делается, и мы задокументируем каждый шаг с большим количеством иллюстраций.
Прочитав этот пост, вы будете готовы с уверенностью импортировать данные в существующую таблицу из Excel. Хотите создать новую таблицу из Excel? Мы тоже можем это сделать.
В нашем примере я буду использовать таблицу HR.EMPLOYEES для создания XLS-файла для нашего импорта. Мы будем использовать этот файл Excel для заполнения пустой копии таблицы EMPLOYEES в другой схеме.
Шаг 0: Пустая таблица Oracle и ваш файл Excel
У вас есть таблица Oracle и один или несколько файлов Excel.
Шаг 1: Выбираем "Импорт данных" по правому щелчку мыши
Когда вы выберете файл, мы захватим первые 100 строк для просмотра ниже. Этот "Предел строк предварительного просмотра" определяет, сколько строк вы можете использовать для проверки ИМПОРТА по мере прохождения мастера. Вы можете увеличить его, но это потребует больше ресурсов, так что не сходите с ума.
Кроме того, есть ли в вашем файле Excel заголовки столбцов? Хотим ли мы рассматривать их как строку к таблице? Скорее всего, нет. Если вы снимите флажок "Заголовок", имена столбцов станут новой строкой в вашей таблице – и, вероятно, не будут вставлены.
Иногда ваш файл Excel имеет несколько заголовков, или вам может потребоваться импортировать только определенное подмножество электронной таблицы. Используйте опцию "Пропустить строки", чтобы получить правильные данные.
Шаг 3: Создайте сценарий или импортируйте автоматически
Для этого упражнения будет использоваться метод "Вставки" (Insert). Каждая строка, обработанная в файле Excel, приведет к выполнению инструкции INSERT в таблице, в которую мы импортируем.
Если вы выберете "Вставить скрипт", мастер завершит работу скриптом ВСТАВКИ на вашем листе SQL. Это хорошая альтернатива, если вы хотите настроить SQL, или если вам нужно отладить/посмотреть, почему метод "Insert" не работает.
Шаг 4: Выберите столбцы Excel для импорта
У вас может быть файл Excel со 100 столбцами, но ваша таблица имеет только 30. Здесь вы даете команду SQL Developer, какие столбцы должны использоваться для импорта. Вы также можете изменить порядок столбцов, что может сделать следующий шаг немного проще.
Шаг 5: Сопоставьте столбцы Excel со столбцами таблицы
Если вы не обращаете внимания и просто позволяете Мастеру импорта делать всё по умолчанию, то сейчас самое время проснуться. Есть большая вероятность, что порядок столбцов файла Excel не будет соответствовать определению вашей таблицы. На этом шаге вы указываете SQL Developer-у, какие столбцы в электронной таблице совпадают с какими столбцами в таблице Oracle.
А помните, как мы установили это окно предварительного просмотра на 100 строк? Мы внимательно изучаем данные, ищем проблемы, пытаясь вписать их в столбец вашей таблицы. Если мы обнаружим проблему, мы пометим столбцы этими "предупреждающими" символами.
Я загрязнил свой файл Excel некоторыми намеренно ошибочными значениями, которые, как я знаю, не будут "подходить". Когда эти строки будут обнаружены в мастере, база данных их отклонит, но остальные строки будут введены.
Давайте на секунду поговорим о форматах даты и времени.
О TIMESTAMP тоже. В файле Excel у вас, вероятно, будут некоторые поля даты и времени, которые вы хотите переместить в столбцы формата DATE или TIMESTAMP . SQL Developer обрабатывает эти значения как строки – и ВАМ нужно сообщить SQL Developer формат DATE или TIMESTAMP , чтобы иметь возможность их преобразовать.
Давайте посмотрим на HIREDATE.
Это значение "ха – ха" никогда не войдет в качестве значения HIRE_DATE – если только вы не храните ДАТЫ в VARCHAR2 , - и если вы делаете это, то делаете это НЕПРАВИЛЬНО. Всегда храните ДАТЫ в формате DATE !
Видите выпадающий селектор "Формат" (Format)? SQL Developer по умолчанию установил строку формата ДАТЫ в ‘ DD-MON-RR ’ – мы пытаемся угадать это на основе строк, которые мы рассматриваем в этом окне предварительного просмотра 100.
Если мы ошиблись в догадках или не смогли разобраться, вам нужно будет ввести это самостоятельно. Документы Oracle могут помочь вам определить правильную модель формата ДАТЫ. Если вы видите небольшое предупреждающее изображение рядом со значениями даты на панели данных, возможно, у вас неправильный формат.
Шаг 6: Проверьте свои настройки и ВПЕРЕД!
Нажмите на кнопку "Готово" (Finish).
Если Мастер столкнется с какими-либо проблемами при выполнении вставок, вы увидите следующее:
Нажав ‘Да", мы доберемся до конца нашей истории и наших данных!
Шаг 7: Посмотрите, Что сработало, а Что нет
Если были строки, отклоненные базой данных, мы увидим их сейчас.
Теперь давайте посмотрим на наши новые табличные данные!
Я люблю сладкий запах данных по утрам!
Обратите внимание на панель "Журнал" (Log). Там можно увиеть файл, с которым мы работали, и сколько времени потребовалось для загрузки данных.
Всё! Можно выдохнуть свободно. Импорт из файла Excel в таблицу базы данных Oracle завершен. В шапке статьи вы можете посмотреть видео инструкцию - копию этого мануала на английском языке.
Читайте также: