Создание гетерогенного сервиса
с использованием ODBC в Oracle для Windows на примере Excel.
Используемая версия
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
-----------------------------------------------
Статья не моего авторства, импорт - вещь нужная, часто в разработке используемая
Еще вариант импорта - с помощью пакета SYS.utl_file - но он присутствует не на всех версиях базы.
Еще вариант импорта - с помощью пакета SYS.utl_file - но он присутствует не на всех версиях базы.
-----------------------------------------------
Имеется Excel документ emp.xls с листом employee содержащим
следующие данные
Имеется Excel документ emp.xls с листом employee содержащим
следующие данные
name age
---- ---
Bob 44
Steve 33
Alex 44
---- ---
Bob 44
Steve 33
Alex 44
1) Настраиваем ODBC источник (System DSN), называем его EMPXLS. В качестве Workbook указываем путь к emp.xls.
2) Выполняем скрипт если он еще не был выполнен
@%ORACLE_HOME%\rdbms\admin\caths.sql
Проверить, был ли выполнен скрипт, можно след. образом
SQL> select view_name from
dba_views
2 where owner='SYS' and view_name='HS_FDS_CLASS';
VIEW_NAME
------------------------------
HS_FDS_CLASS
3) Редактируем файл listener.ora, добавляем запись о новом сервисе EMPXLS
LISTENER =
(ADDRESS_LIST =
(ADDRESS= (PROTOCOL=tcp)
(HOST = localhost)
(PORT = 1521)
)
)
SID_LIST_LISTENER =
(SID_LIST =
... -- прочие SID
(SID_DESC =
(PROGRAM = hsodbc)
(ORACLE_HOME = d:\oracle\ora92) -- ORACLE_HOME
(SID_NAME = EMPXLS)
)
)
(ADDRESS_LIST =
(ADDRESS= (PROTOCOL=tcp)
(HOST = localhost)
(PORT = 1521)
)
)
SID_LIST_LISTENER =
(SID_LIST =
... -- прочие SID
(SID_DESC =
(PROGRAM = hsodbc)
(ORACLE_HOME = d:\oracle\ora92) -- ORACLE_HOME
(SID_NAME = EMPXLS)
)
)
4) Создаем и редактируем файл
%ORACLE_HOME%\hs\admin\initEMPXLS.ora
Имя файла должно точно соответствовать init<SID>.ora, где <SID> это имя сервиса прописанного в listener.ora.
вносим строку
HS_FDS_CONNECT_INFO=EMPXLS -- имя odbc источника
5) Редактируем файл tnsnames.ora, добавляем запись о EMPXLS
Файл должен находится на сервере с базой, так как база выступает клиентом обращающимся к hs агенту.
... -- прочие SID
EMPXLS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =(SID = EMPXLS))
(HS = OK)
)
EMPXLS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =(SID = EMPXLS))
(HS = OK)
)
6) Перегружаем listener и делаем проверку
lsnrctl
reload
tnsping EMPXLS
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = localhost)(PORT = 1521))) (CONNECT_DATA =(SID = EMPXLS)) (HS = OK))
OK (100 msec)
tnsping EMPXLS
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = localhost)(PORT = 1521))) (CONNECT_DATA =(SID = EMPXLS)) (HS = OK))
OK (100 msec)
7) Создаем dblink в базе
create public database link
EMPXLS
connect to dummy identified by "dummy"
using 'EMPXLS';
8) Запрашиваем имена таблиц и колонок
SQL> select table_name, column_name from dba_tab_columns@EMPXLS;
TABLE_NAME COLUMN_NAME
------------------------------
------------------------------
employee$ name
employee$ age
9) Запрашиваем данные. Используем двойные кавычки, чтобы сохранить регистр в именах таблиц и колонок.
SQL> SQL> select "name", "age"
from "employee$"@EMPXLS;
name age
------------------------------
----------
Bob 44
Steve 33
Alex 44
10) Закрываем dblink, перед этим необходимо закончить транзакцию, даже если это был select
commit;
alter session close database link EMPXLS;
Особенности использования hsodbс при GLOBAL_NAMES = TRUE.
Если база в режиме GLOBAL_NAMES, то необходимы следующие дополнительные настройки.
1) Указать имя источника и домена в %ORACLE_HOME%\hs\admin\initEMPXLS.ora
HS_FDS_CONNECT_INFO
= EMPXLS
HS_DB_DOMAIN = DOMAIN_NAME
HS_DB_NAME = EMPXLS
HS_DB_DOMAIN = DOMAIN_NAME
HS_DB_NAME = EMPXLS
2) Указать домен по умолчанию в sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES=(NTS)
NAMES.DEFAULT_DOMAIN = DOMAIN_NAME
NAMES.DEFAULT_DOMAIN = DOMAIN_NAME
3) Добавить имя домена к TNS алиасу в tnsnams.ora
... -- прочие SID
EMPXLS.DOMAIN_NAME =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =(SID = EMPXLS))
(HS = OK)
)
EMPXLS.DOMAIN_NAME =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =(SID = EMPXLS))
(HS = OK)
)
4) Убедиться, что GLOBAL_NAME базы включает имя домена.
SQL> SELECT * FROM
GLOBAL_NAME;
GLOBAL_NAME
-------------------
TEST817
если нет, то выполнить команду
ALTER DATABASE TEST817 RENAME
GLOBAL_NAME TO TEST817.DOMAIN_NAME;
SQL> SELECT * FROM
GLOBAL_NAME;
GLOBAL_NAME
-------------------
TEST817.DOMAIN_NAME
5) Создаем dblink в базе
create public database link
EMPXLS
connect to dummy identified by "dummy"
using 'EMPXLS';
При создании к dblink автоматически должно быть добавлено имя домена.
SQL> SELECT DB_LINK FROM
DBA_DB_LINKS WHERE DB_LINK LIKE 'EMPXLS%';
DB_LINK
--------------------
EMPXLS.DOMAIN_NAME
6) Теперь dblink работает в режиме GLOBAL_NAMES
SQL> select name, value from
v$parameter where name='global_names';
NAME VALUE
---------------
---------------
global_names TRUE
SQL> select "name",
"age" from
"employee$"@EMPXLS;
name age
------------------------------
----------
Bob 44
Steve 33
Alex 44
Дополнения
1) Если Excel документ находится на сетевом диске
Пользователь, от имени которого запущены сервисы Oracle, должен иметь
необходимые права доступа на этот сетевой диск/документ. По умолчанию сервис Oracle в Windows запускается от имени администратора локальной машины, поэтому следует запускать его от имени доменного пользователся, имеющего необходимые права доступа.
2) Одновременная работа с документом в нескольких сессиях
При работе с Excel документом в режиме чтение/запись через ODBC драйвер, только один пользователь может работать с ним одновременно, или, иными словами, в таком случае ODBC драйвер открывает документ в монопольном режиме. Поэтому, если DB Link уже открыт в одной сессии, то при попытке обратиться к нему из другой сессии возникает ошибка:
SQL> select "name",
"age" from
"employee$"@EMPXLS;
select "name", "age"
from "employee$"@EMPXLS
*
ERROR at line 1:
ORA-28500: connection from
ORACLE to a non-Oracle system returned this
message:
[Generic
Connectivity Using ODBC][Microsoft][Драйвер ODBC Excel]
Файл '(нет данных)' не
может быть открыт ядром базы данных Microsoft Jet.
Файл уже открыт другим пользователем для монопольного
доступа
или требуется разрешение для просмотра данных.
(SQL State: S1000; SQL
Code: -1032)
ORA-02063:
preceding 2 lines from EMPXLS
Если для работы достаточно режима только чтение, в настройках ODBC источника следует указать опцию read only. Тогда несколько сессий смогут читать из него одновременно.
3) Извлечение данных из документа со сложной или не четко упорядоченной структурой данных
Иногда, Excel документ представляет собой настоящий "винигрет" из данных, элементов красочного оформления, пояснений, комментариев, свободного текста итп. Читать данные из такого документа в Oracle не всегда получается легко. В этом случае рекомендуется создавать дополнительный лист, где будут храниться очищенные данные в строгой табличной форме. Обновление данных в этом вспомогательном листе можно автоматизировать, задав их зависимость от исходных данных. Таким образом, с одной стороны, не накладываются дополнительные ограничения на пользователей, работающих с исходным листом, с другой, обеспечивается строгая структуризация данных для их беспроблемного извлечения в Oracle.
Комментарии
- 10 марта 2012, 02:56 РысЪ
Спасибо огромное! Отличное руководство.
Столкнулся я только с одной проблемой.
По аналогичной схеме определил дблинк для sybase, имя пользователя и пароль заданы в описании источника данных.
CREATE PUBLIC DATABASE LINK sybase_db USING 'SYBASE_DSN' выполняется без ошибок и предупреждений, но использовать созданный линк может только sys, остальные пользователи получают от sybase сообщение
"ORA-28500: соединение ORACLE с посторонней системой выдало сообщение:
[Generic Connectivity Using ODBC][Sybase][ODBC Driver][Adaptive Server
Anywhere]Invalid user authorization specification: Invalid user ID or password
(SQL State: 28000; SQL Code: -103)
ORA-02063: предшествующий 2 lines из SYBASE_DB"
Столкнулся я только с одной проблемой.
По аналогичной схеме определил дблинк для sybase, имя пользователя и пароль заданы в описании источника данных.
CREATE PUBLIC DATABASE LINK sybase_db USING 'SYBASE_DSN' выполняется без ошибок и предупреждений, но использовать созданный линк может только sys, остальные пользователи получают от sybase сообщение
"ORA-28500: соединение ORACLE с посторонней системой выдало сообщение:
[Generic Connectivity Using ODBC][Sybase][ODBC Driver][Adaptive Server
Anywhere]Invalid user authorization specification: Invalid user ID or password
(SQL State: 28000; SQL Code: -103)
ORA-02063: предшествующий 2 lines из SYBASE_DB"
- 31 мая 2010, 11:50 Dronablo
Стоит добавить сюда, что начиная с 11.2 (можт и 11.1 -
не пробовал) вместо
PROGRAM = hsodbc
нужно писать
PROGRAM = dg4odbc
PROGRAM = hsodbc
нужно писать
PROGRAM = dg4odbc
- 15 апреля 2010, 02:11 Malter
<СПРАШИВАЛ>hellhound 26 ноября 2008, 14:12
Оценка: N/A
Люди подскажите, я неопытный. Для чего комит при селекте?</СПРАШИВАЛ>
Commit -- закончить транзакцию перед закрытием DBLINKa
alter session close database link EMPXLS;--закрываем линк
commit;--ИМХО ещераз коммит чтобы отсоединить линк и использовать в других сессиях.
Протестил на примере подключения к *.DBF
Пока линк не отцепишь - не дает удалить dbf файл.
На основе селекта можно создать представления которыми можно пользоваться в других приложениях без изврата с dblinkami
Люди подскажите, я неопытный. Для чего комит при селекте?</СПРАШИВАЛ>
Commit -- закончить транзакцию перед закрытием DBLINKa
alter session close database link EMPXLS;--закрываем линк
commit;--ИМХО ещераз коммит чтобы отсоединить линк и использовать в других сессиях.
Протестил на примере подключения к *.DBF
Пока линк не отцепишь - не дает удалить dbf файл.
На основе селекта можно создать представления которыми можно пользоваться в других приложениях без изврата с dblinkami
- 26 ноября 2008, 14:12 hellhound
Люди подскажите, я неопытный. Для чего комит при
селекте?
- 29 октября 2008, 13:07 usein
Нужно добавить.
Если был установлены обновления для Оракла. Убедиться, что были так же установлены обновления для tg4msql. Например, для обновления 9.2.0.8 нужно распаковать все jar архивы из папки 9.2.0.8\stage\Patches\oracle.tg.tg4msql\9.2.0.8.0\1\DataFiles и разложить все файлы в соответствующие папки установленного Оракла.
Я из-за этого два дня бился.
Если был установлены обновления для Оракла. Убедиться, что были так же установлены обновления для tg4msql. Например, для обновления 9.2.0.8 нужно распаковать все jar архивы из папки 9.2.0.8\stage\Patches\oracle.tg.tg4msql\9.2.0.8.0\1\DataFiles и разложить все файлы в соответствующие папки установленного Оракла.
Я из-за этого два дня бился.