Родион Константинов
<!a target=_blank class=bodylink href="mailto://rodionk@gala.net">rodionk@gala.net
СОДЕРЖАНИЕ
Впервые COM Automation Feature появилась в Oracle 8i на платформе Microsoft Windows и сразу же привлекла внимание разработчиков.
Эта нововведение позволяет на сервере базы данных Oracle в хранимых процедурах PL/SQL и Java использовать технологию Component Object Model (COM).
С ее помощью разработчику предоставляется механизм доступа к свойствам и методам COM-объектов, причем как к локальным, так и к удаленным (DCOM).
Технология COM - это объектно-ориентированная спецификация, предложенная компанией Microsoft для упрощения интеграции различных программ между собой.
Каждый COM-объект, который создается в соответствии с этой спецификацией, может взаимодействовать с другими COM-объектами.
Одним из расширений этой технологией является так называемая “автоматизация” (Automation) - позволяющая одному приложению программно управлять другим.
В ней различаются клиентская часть(automation controller) и серверная часть(automation object). Сервер автоматизации – это объект который управляется клиентом.
Центральным элементом технологии COM Automation является интерфейс IDispatch, позволяющий клиенту делать запросы к объекту-серверу.
В обязательном порядке IDispatch обеспечивает следующую функциональность:
Программный интерфейс COM Automation Feature реализован через вызовы внешних процедур Oracle (external procedure).
Алгоритм работы выглядит следующим образом:
В процессе инсталляции, в каталог ;ORACLE_HOME\bin помещаются файлы динамических библиотек, все остальные файлы будут располагаться в каталоге ORACLE_HOME\com.
После создания экземпляра базы данных необходимо сконфигурировать сетевой прослушивающий процесс Oracle Listener. В случае использования инструмента Net Configuration Assistant, файлы listener.ora и tnsnames.ora создаются автоматически, но также их можно создать и вручную. Далее приведены прмеры этих файлов.
#пример файла listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)
(HOST = myserver)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oracle\product\10.2.0\db)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME=ORCL)
(ORACLE_HOME = C:\oracle\product\10.2.0\db)
)
)
#пример файла tnsnames.ora
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = IPC)
(KEY = EXTPROC0)
)
(CONNECT_DATA = (SID = plsextproc))
)
)
Проверить правильность конфигурации можно запустив утилиту LSNRCTL с параметром status - должен присутствовать сервис PLSExtProc.
C:\>Lsnrctl status
. . .
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
В ходе установки пакет ORDCOM, который предоставляет PL/SQL API для работы с COM не компилируется. Разработчик должен скомпилировать его самостоятельно в схеме приложения.
Для демонстрации работы создадим пользователя testcom и дадим ему необходимые полномочия:
CREATE USER testcom IDENTIFIED BY testcom
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON users;
GRANT connect, resource TO testcom;
GRANT create library TO testcom;
Необходимо отметить, что пользователь, в схеме которого будет компилироваться пакет ORDCOM, должен иметь привилегию CREATE LIBRARY.
Подключимся вновь созданным пользователем в среде SQL*Plus и создадим пакет ORDCOM:
CONNECT testcom/testcom;
@c:\oracle\product\10.2.0\db\com\comwrap.sql
Работа с COM-объектами в PL/SQL
В Oracle COM Automation Feature предоставляются следующие подпрограммы:
Общая схема работы с COM-объектом такова:
DECLARE
-- все методы возвращают результат работы - значение типа HRESULT
-- в шестнадцатиричном формате имеет вид 0x800nnnnn в десятичном -214nnnnnnn
hResult binary_integer := 0;
-- переменные для хранения ссылок на объекты
AppHandle binary_integer := -1;
WorkbooksHandle binary_integer := -1;
WorkbookHandle binary_integer := -1;
DummyHandle binary_integer := -1;
BEGIN
-- в качестве первого параметра передается строковый идентификатор COM-объекта –
-- Program ID, имеющий соответствующий ему Globally Unique Identifier (GUID), 128-битное
-- число однозначно идентифицирующее COM-объект
hResult := OrdCOM.CreateObject('Excel.Application', 0, '', AppHandle);
-- обработка ошибок
-- Вызов любого метода API(кроме самого GetLastError) очищает информацию о
-- предыдущих ошибках
IF ( hResult !=0 ) THEN
OrdCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
dbms_output.put_line ('Error creating application, aborting: ' || hResult);
dbms_output.put_line(error_src);
dbms_output.put_line(error_description);
dbms_output.put_line(error_helpfile);
END IF;
hResult := OrdCOM.GetProperty(AppHandle, 'WorkBooks', 0, WorkBooksHandle);
-- Передача параметров в вызываемый метод
-- инициализация коллекции параметров
OrdCOM.InitArg();
-- для вызова метода добавления новой рабочей книги WorkBooks.Add
-- используется шаблон xlWBATWorksheet -4167, I4-тип данных
-- вызов SetArg инициализирует первый параметр
-- последующие вызовы будут инициализировать второй, третий и т.д параметры.
OrdCOM.SetArg(-4167,'I4');
hResult := OrdCOM.Invoke(WorkbooksHandle, 'Add',1,WorkbookHandle);
hResult := OrdCOM.Invoke(AppHandle, 'Quit', 0, DummyHandle);
-- уничтожение объекта
hResult := OrdCOM.Destroy(AppHandle);
END;
Мы рассмотрели стандартную схему работы с COM-объектом. Компания Oracle облегчила работу тем разработчикам, которые будут использовать COM Automation Future для работы с файлами формата Microsoft Office (Word, Excel, PowerPoint), в каталоге ORACLE_HOME\com\demos имеются наглядные примеры и готовые пакеты процедурреализующие стандартные действия с учетом специфики каждого типа файла.
Например пакет ORDExcel предоставляет следующие возможности для работы с MS Excel:
Теперь посмотрим как Oracle COM Automation Feature можно использовать в реальных проектах. Как разработчик хранилищ данных я регулярно сталкиваюсь с ситуацией когда часть необходимых для загрузки данных заказчик хранит в нескольких(иногда их большое количество) файлах формата MS Excel. Если формат листов представляет из себя таблицу то можно такой файл подключить к БД Oracle при помощи механизма General Connectivity или экспортировать в текстовый формат и загрузить данные утилитой SQL*Loader. Но в случае когда формат сложный, скажем в виде отчета, то для его обработки необходимо написать парсер на каком-либо из высокоуровневых языков. Такое разделение ETL на клиентскую и серверную часть вносит дополнительное усложнение в процесс разработки. Использование же COM Automation Future дает возможность обойтись только сервером БД и языком PL/SQL.
Файл может содержать отчетность за несколько дней и имеет следующий формат:
Для работы с уже имеющимися файлами я добавил три функции в пакет стандартный пакет ORDExcel:
FUNCTION CreateExcelApplication(servername VARCHAR2) RETURN binary_integer IS
BEGIN
dbms_output.put_line('Creating Excel application...');
i := OrdCOM.CreateObject('Excel.Application', 0, servername,applicationToken);
IF ( i != 0 ) THEN
ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
dbms_output.put_line(error_src);
dbms_output.put_line(error_description);
dbms_output.put_line(error_helpfile);
END IF;
dbms_output.put_line('Invoking Workbooks...');
i := ORDCOM.GetProperty(applicationToken, 'WorkBooks', 0, WorkBooksToken);
IF ( i != 0 ) THEN
ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
dbms_output.put_line(error_src);
dbms_output.put_line(error_description);
dbms_output.put_line(error_helpfile);
END IF;
RETURN i;
END CreateExcelApplication;
FUNCTION OpenExcelFile(filename VARCHAR2, sheetname VARCHAR2) RETURN binary_integer IS
BEGIN
dbms_output.put_line('Opening Excel file ' || filename ||' ...');
ORDCOM.InitArg();
ORDCOM.SetArg(filename, 'BSTR');
i := ORDCOM.Invoke(WorkBooksToken, 'Open', 1, DummyToken);
IF ( i != 0 ) THEN
ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
dbms_output.put_line(error_src);
dbms_output.put_line(error_description);
dbms_output.put_line(error_helpfile);
END IF;
dbms_output.put_line('Opening WorkBook');
i := ORDCOM.GetProperty(applicationToken, 'ActiveWorkbook', 0, WorkBookToken);
IF ( i != 0 ) THEN
ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
dbms_output.put_line(error_src);
dbms_output.put_line(error_description);
dbms_output.put_line(error_helpfile);
END IF;
dbms_output.put_line('Invoking WorkSheets..');
i := ORDCOM.GetProperty(applicationToken, 'WorkSheets', 0, WorkSheetToken1);
IF ( i != 0 ) THEN
ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
dbms_output.put_line(error_src);
dbms_output.put_line(error_description);
dbms_output.put_line(error_helpfile);
END IF;
dbms_output.put_line('Invoking WorkSheet');
ORDCOM.InitArg();
ORDCOM.SetArg(sheetname,'BSTR');
i := ORDCOM.GetProperty(WorkBookToken, 'Sheets', 1, WorkSheetToken);
-- можно получить ссылку на активный лист, а не на заданный именем
-- i := ORDCOM.GetProperty(applicationToken, 'ActiveSheet', 0, WorkSheetToken);
IF ( i != 0 ) THEN
ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
dbms_output.put_line(error_src);
dbms_output.put_line(error_description);
dbms_output.put_line(error_helpfile);
END IF;
dbms_output.put_line('Opened ');
RETURN i;
END OpenExcelFile;
FUNCTION ConvertFormulaA1(formulaR1C1 VARCHAR2) RETURN VARCHAR2 IS
res VARCHAR2(10);
BEGIN
ORDCOM.InitArg();
ORDCOM.SetArg(formulaR1C1,'BSTR');
ORDCOM.SetArg(-4150,'I4');
ORDCOM.SetArg(-4151,'I4');
i := ORDCOM.Invoke(applicationToken, 'ConvertFormula', 3, res);
IF ( i != 0 ) THEN
ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
dbms_output.put_line(error_src);
dbms_output.put_line(error_description);
dbms_output.put_line(error_helpfile);
END IF;
RETURN res;
END ConvertFormulaA1;
Для хранения данных полученных из файла MS Excel создадим таблицу SHOPS_REPORTS_TBL
CREATE TABLE SHOPS_REPORTS_TBL ( SHOP_NAME VARCHAR2(32), MANAGER_NAME VARCHAR2(128), PERIOD DATE, SALE NUMBER(10,2), PURCHASE NUMBER(10,2), CHARGES NUMBER(10,2) );
Следующий пример кода загружает данные из файла MS Excel с данными о продажах в таблицу SHOPS_REPORTS_TBL
DECLARE
result INTEGER;
range_count INTEGER;
rec shops_reports_tbl%ROWTYPE;
BEGIN
-- создаем объект Excel.Application
result := ORDExcel.CreateExcelApplication('');
-- открываем файл c:\load_data.xls и лист Sheet1
result := ORDExcel.OpenExcelFile('c:\Data\Sales\load_data.xls', 'Sheet1');
-- читаем Фамилию ответственного менеджера из ячейки B1
rec.manager_name := ORDExcel.GetDataStr('B1');
dbms_output.put_line(shop_name);
-- читаем наименование магазина из ячейки B3
rec.shop_name := ORDExcel.GetDataStr('B3');
dbms_output.put_line(manager);
-- цикл по 31 дню
FOR range_count IN 4..34 LOOP
BEGIN
dbms_output.put_line(ORDExcel.ConverFormulaA1('R5C' || range_count) );
-- т.к. данные расположены горизонтально в столбцах D5 E5 F5... то
-- ссылку задаем в стиле R1C1 а затем конвертируем в стиль A1
rec.period := ORDExcel.GetDataDate(ORDExcel.ConverFormulaA1('R5C' || range_count));
IF rec.period IS NULL THEN
-- если данные заканчиваются раньше, выходим из цикла
EXIT;
END IF;
dbms_output.put_line(measure_date);
-- читаем значения показателей
rec.sale := ORDExcel.GetDataReal(ORDExcel.ConverFormulaA1('R6C' || range_count));
rec.purchase := ORDExcel.GetDataReal(ORDExcel.ConverFormulaA1('R7C' || range_count));
rec.charges := ORDExcel.GetDataReal(ORDExcel.ConverFormulaA1('R8C' || range_count));
-- записываем в таблицу данные за один день
INSERT INTO shops_reports_tbl VALUES rec;
END;
END LOOP;
COMMIT;
result := ORDExcel.ExitExcel();
END;
Просмотрев содержимое таблицы, можно убедиться в правильности работы процедуры загрузки:
SELECT * FROM shops_reports_tbl SHOP_NAME MANAGER_NAME PERIOD SALE PURCHASE CHARGES --------------------------------------------------------------------------------------------- Химки-33 Иванова И.И. 01.01.2007 254584,00 67700,00 15000,00 Химки-33 Иванова И.И. 02.01.2007 348900,00 118500,00 16000,00Заключение
С использованием COM Automation Future разработчики приложений для СУБД Oracle могут задействовать всю мощь технологии COM. Классическим примером использования служит работа с файлами MS Ofice в хранимых процедурах PL/SQL. Необходимо отметить, что COM Automation Future доступен только для СУБД Oracle на платформе MS Windows.
Последние комментарии