понедельник, 10 сентября 2012 г.

Импорт в OeBS из Excel



Создание гетерогенного сервиса с использованием 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
-----------------------------------------------
Статья не моего авторства,  импорт - вещь нужная, часто в разработке используемая
Еще вариант импорта -  с помощью пакета SYS.utl_file   - но он присутствует не на всех версиях базы.
-----------------------------------------------

Имеется Excel документ emp.xls с листом employee содержащим
следующие данные
name  age
----  ---
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)
    )
  )

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)
  )

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)

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

2) Указать домен по умолчанию в sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES=(NTS)
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)
  )

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"
  • 31 мая 2010, 11:50 Dronablo
Стоит добавить сюда, что начиная с 11.2 (можт и 11.1 - не пробовал) вместо
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
  • 26 ноября 2008, 14:12 hellhound
Люди подскажите, я неопытный. Для чего комит при селекте?
Нужно добавить.
Если был установлены обновления для Оракла. Убедиться, что были так же установлены обновления для tg4msql. Например, для обновления 9.2.0.8 нужно распаковать все jar архивы из папки 9.2.0.8\stage\Patches\oracle.tg.tg4msql\9.2.0.8.0\1\DataFiles и разложить все файлы в соответствующие папки установленного Оракла.
Я из-за этого два дня бился.

 
Автор: на 1 комментарий:
Подписаться на: Сообщения (Atom)