Использование Oracle COM Automation Feature

Родион Константинов
rodionk@gala.net

Использование Oracle COM Automation Feature.
(Профессионалу-разработчику)

Источник: сайт Realcoding.NET "Программирование для всех", 2007, http://www.realcoding.net/article/view/4536

СОДЕРЖАНИЕ

Введение
Архитектура
Работа с COM-объектами в PL/SQL
Пример использования в реальных проектах
Заключение

Введение

Впервые 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 обеспечивает следующую функциональность:

Вызов метода объекта
Получение значения атрибута объекта
Установка значения атрибута объекта

Oracle COM Automation Feature представляет собой оболочку над интерфейсом IDispatch, и предоставляет доступ ко всем его методам через удобный PL/SQL API. Также доступ к интерфейсу IDispatch возможен из хранимых процедур Java (использование Java в данной статье не рассматривается).
На рис 1 показана архитектура Oracle COM Automation Feature.

Рис. 1 Архитектура Oracle COM Automation Feature

Программный интерфейс COM Automation Feature реализован через вызовы внешних процедур Oracle (external procedure).

Алгоритм работы выглядит следующим образом:

интерпретатор PL/SQL посылает сообщение прослушивателю (listener) запустить процесс External Procedure Handler(EPH) - extproc.exe для текущей пользовательской сессии;
интерпретатор PL/SQL передает процессу EPH имя вызываемой внешней процедуры, параметры и путь к файлу DLL (orawpcomVER.dll), содержащим внешнюю процедуру;
процесс EPH загружает в память DLL и транслирует запрос на исполнение внешней процедуры в соответствующий вызов Win32 API, который и производит манипуляции с COM-объектом.

Установка

Рис.2 Выбор опции Oracle COM Automation Feature

В процессе инсталляции, в каталог ;ORACLE_HOMEbin помещаются файлы динамических библиотек, все остальные файлы будут располагаться в каталоге ORACLE_HOMEcom.

После создания экземпляра базы данных необходимо сконфигурировать сетевой прослушивающий процесс 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:oracleproduct10.2.0db)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME=ORCL)
(ORACLE_HOME = C:oracleproduct10.2.0db)
)
)

#пример файла 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:oracleproduct10.2.0dbcomcomwrap.sql

Работа с COM-объектами в PL/SQL

В Oracle COM Automation Feature предоставляются следующие подпрограммы:

CreateObject
DestroyObject
GetLastError
GetProperty
SetProperty
InitArg
InitOutArg
GetArg
SetArg
Invoke

Общая схема работы с 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_HOMEcomdemos имеются наглядные примеры и готовые пакеты процедурреализующие стандартные действия с учетом специфики каждого типа файла.

Например пакет ORDExcel предоставляет следующие возможности для работы с MS Excel:

FUNCTION CreateExcelWorkSheet
FUNCTION InsertData
FUNCTION InsertDataReal
FUNCTION GetDataNum
FUNCTION GetDataStr
FUNCTION GetDataReal
FUNCTION GetDataDate
FUNCTION InsertData
FUNCTION InsertChart
FUNCTION SaveExcelFile
FUNCTION ExitExcel

Пример использования в реальных проектах

Теперь посмотрим как Oracle COM Automation Feature можно использовать в реальных проектах. Как разработчик хранилищ данных я регулярно сталкиваюсь с ситуацией когда часть необходимых для загрузки данных заказчик хранит в нескольких(иногда их большое количество) файлах формата MS Excel. Если формат листов представляет из себя таблицу то можно такой файл подключить к БД Oracle при помощи механизма General Connectivity или экспортировать в текстовый формат и загрузить данные утилитой SQL*Loader. Но в случае когда формат сложный, скажем в виде отчета, то для его обработки необходимо написать парсер на каком-либо из высокоуровневых языков. Такое разделение ETL на клиентскую и серверную часть вносит дополнительное усложнение в процесс разработки. Использование же COM Automation Future дает возможность обойтись только сервером БД и языком PL/SQL.

Файл может содержать отчетность за несколько дней и имеет следующий формат:

Рис.3 Формат файла MS Excel для загрузки в СУБД Oracle

Для работы с уже имеющимися файлами я добавил три функции в пакет стандартный пакет ORDExcel:

CreateExcelApplication - создает объект Excel.Application но не добавляет новых рабочих книг и листов
OpenExcelFile - открывает существующий файл .XLS и рабочий лист в нем
ConverFormulaA1 - Преобразовывает ссылки стиля R1C1 в стиль A1

Исходный код указанных функций приведен ниже.

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:DataSalesload_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.

Oracle Magazine - Русское издание (Декабрь 2007)