Oracle 10G Development: доступ из Oracle к гетерогенным базам данных

 

By Boris Makushkin

Alba Spectrum Technologies

(095) 918-3314, (095) 918-3111, help@albaspectrum.com  

Oracle 10G Development: Access from Oracle to Heterogeneous Data

            Часто перед создателем современной информационной системы встает непростая задача организация и унификация доступа к разнородным данным, хранящимся в различных источниках. Корпорация Oracle представляет продукты для организации доступа к гетерогенным данным - это Oracle Transparent Gateways и Generic Connectivity. Продукт Generic Connectivity является общим решением для предоставления доступа к гетерогенным данным через механизмы ODBC или OLE DB, например к FoxPro, Microsoft Access и пр. Более интересен второй продукт – Oracle Transparent Gateways. Его компоненты специально созданы для доступа к тому или иному источнику данных, результатом чего является более эффективная организация работы этих компонентов, лучшая функциональность и лучшая производительность. В настоящий момент линейка продуктов представлена следующими компонентами:

 

 

Основным недостатком этих продуктов их высокая цена и недоступность на всех платформах.

            Целью сегодняшней нашей статьи является организация доступа их хранимых процедур Oracle Database к данным MS SQL Server 2000 на примере выборки данных из MS CRM. Мы воспользуемся богатыми возможностями хранимых процедур Oracle с использованием Java для реализации доступа к таблице квотаций MS CRM через механизм курсоров. Нашу работу мы разобьем на две части – сначала смоделируем полностью работу хранимой процедур в отдельном Java приложении, затем перенесем код в Oracle RDBMS. Итак, приступим:

  1. Создадим файл CRMConnector.java, содержащий определение класса для работы с MS CRM данными и возвращающий набор данных в виде Java ResultSet – он станет каркасом метода для нашей хранимой процедуры:

 

package com.albaspectrum.util;

 

import java.sql.SQLException;

import java.sql.DriverManager;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.Statement;

import java.sql.PreparedStatement;

import oracle.jdbc.driver.OracleDriver;

import oracle.jdbc.driver.OracleConnection;

 

public class CRMConnector {

                public static ResultSet getQuotes() throws Exception {

                                // Obtain connection to the databases

                                Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");

                                Class.forName("oracle.jdbc.driver.OracleDriver");

                               

                                Connection mssqlConn = DriverManager.getConnection("jdbc:microsoft:sqlserver://CRMDBSERVER:1433;DatabaseName=Adventure_Works_Cycle_MSCRM;SelectMethod=cursor", "sa", "password");

                                Connection oracleConn = DriverManager.getConnection("jdbc:oracle:thin:@ORACLEDBHOST:1521:ORINSTANCE", "test", "test");

                                //Connection oracleConn = new OracleDriver().defaultConnection();

                               

                                // Turn off autocommit for Oracle connection

                                oracleConn.setAutoCommit(false);

                               

                                // Create Oracle temp table

                                Statement oracleChkTempTableStmp = oracleConn.createStatement();

                                ResultSet rsCheckTmpTable = oracleChkTempTableStmp.executeQuery("SELECT COUNT(table_name) AS TempTableCounter FROM ALL_TABLES WHERE UPPER(table_name) = UPPER('TempQuotes')");

 

                                if (rsCheckTmpTable.next()) {

                                                if (rsCheckTmpTable.getInt("TempTableCounter") == 0) {

                                                                Statement oracleStmt = oracleConn.createStatement();

                               

                                                                oracleStmt.executeUpdate("CREATE GLOBAL TEMPORARY TABLE TempQuotes (QuoteNumber VARCHAR(100), QuoteName VARCHAR(300), TotalAmount NUMERIC, AccountName VARCHAR(160)) ON COMMIT PRESERVE ROWS");

                                                                oracleConn.commit();

                                                                oracleStmt.close();

                                                }

                                }

 

                                rsCheckTmpTable.close();

                                oracleChkTempTableStmp.close();

 

                                // Fetch MS SQL Data to Oracle temp table

                                Statement mssqlStmt = mssqlConn.createStatement();

                                ResultSet rs = mssqlStmt.executeQuery("select QuoteBase.Name as QuoteName, QuoteBase.QuoteNumber as QuoteNumber, QuoteBase.TotalAmount as TotalAmount, AccountBase.Name as AccountName from QuoteBase, AccountBase where QuoteBase.AccountId = AccountBase.AccountId");

 

                                while (rs.next()) {

                                                String quoteName = rs.getString("QuoteName");

                                                String accountName= rs.getString("AccountName");

                                                String quoteNumber = rs.getString("QuoteNumber");

                                                double totalAmount = rs.getDouble("TotalAmount");

 

                                                PreparedStatement insertOracleStmt = oracleConn.prepareStatement("INSERT INTO TempQuotes (QuoteNumber, QuoteName, TotalAmount, AccountName) VALUES (?, ?, ?, ?)");

 

                                                insertOracleStmt.setString(1, quoteName);

                                                insertOracleStmt.setString(2, quoteNumber);

                                                insertOracleStmt.setDouble(3, totalAmount);

                                                insertOracleStmt.setString(4, accountName);

                                               

                                                insertOracleStmt.executeUpdate();

 

                                                insertOracleStmt.close();              

                                }

 

                                oracleConn.commit();

 

                                rs.close();

                                mssqlStmt.close();

                                mssqlConn.close();

 

                                // Create any subsequent statements as a REF CURSOR

                                ((OracleConnection)oracleConn).setCreateStatementAsRefCursor(true);

 

                                // Create the statement

                                Statement selectOracleStmt = oracleConn.createStatement();

 

                                // Query all columns from the EMP table

                                ResultSet rset = selectOracleStmt.executeQuery("select * from TempQuotes");

 

                                oracleConn.commit();

 

                                // Return the ResultSet (as a REF CURSOR)

                                return rset;

                }

}

 
2.      Некоторые пояснения к этому, довольно большому, методу. В первой части метода мы открываем соединения к базе данных MS CRM и базе Oracle, где будет храниться временная таблица, с помощью которой будет формироваться результирующий набор данных (наличие временной таблицы необходимо для формирования курсора в будущих пунктах). Далее мы проверяем существование определения временной таблицы для хранения квотаций в базе Oracle – если таблицы не существует, то выполняем ее создание. DDL для таблицы взято из определения QuoteBase в базе данных MS CRM. Далее все достаточно просто – делаем выборку из MS CRM таблицы и переносим полученные данные во временную таблицу Oracle. Важным моментом является вызов метода setCreateStatementAsRefCursor для результирующего статемента. Подготовленный таким образом ResultSet дает возможность возвращать данные для формирования Oracle REF Cursor.
3.      Небольшое пояснение о временных таблицах Oracle. Временные таблицы, называемые также глобальными временными таблицами создаются в временном табличном пространстве пользователя. Однажды созданные, эти таблицы существуют до момента их явного уничтожения. А вот данные в таких таблицах «живут» в зависимости от параметров создания таблицы – в пределах пользовательской сессии (ON COMMIT PRESERVE ROWS) или в пределах транзакции (ON COMMIT DELETE ROWS). Синтаксис для создания временных таблиц следующий: CREATE GLOBAL TEMPORARY TABLE  tablename (columns) [ON COMMIT PRESERVE|DELETE ROWS]. Нам необходима та уникальная возможность, которую предоставляют временные таблицы – очистка временных сегментов после завершения пользовательской сессии, причем с учетом того, что структура таблицы одинакова для каждого пользователя, а вот данные специфичны для каждой сессии. Для более подробного описания логики работы временных таблицы мы советуем обратиться к руководству "Oracle Database Concepts"

 

4.      Создадим небольшую программу для проверки функционирования нашего CRM коннектора:
 
package com.albaspectrum.util;
 
import java.sql.ResultSet;
 
public class TestORA {
            public static void main(String args[]) throws Exception {
                                 try {
                                                     ResultSet rs = CRMConnector.getQuotes();
 
                                                     while (rs.next()) {
                                                                         String quoteName = rs.getString("QuoteName");
                                                                         String quoteNumber = rs.getString("QuoteNumber");
                                                                         double totalAmount = rs.getDouble("TotalAmount");
                                                                         String accountName = rs.getString("AccountName");
 
                                                                         System.out.println(quoteName + "|" + quoteNumber + "|" + accountName + "|$" + totalAmount);
                                                     }
                                 }
                                 catch (Exception e) {
                                                     System.out.println("Exception: " + e.toString());
                                                     e.printStackTrace();
                                 }
            }
}

 

  1. Соберем проект с помощью команд (возможно, вам потребуется исправить пути для компонент, установленных на вашем компьютере):

@echo off

 

set PATH=%PATH%;C:\j2sdk1.4.2_06\bin\

set CLASSPATH=.;%CLASSPATH%;ojdbc14.jar

 

javac *.java

 

copy *.class .\com\albaspectrum\util\*.class

 

  1. Для функционирования MS SQL JDBC драйвера, поместим в текущий каталог файлы msbase.jar, mssqlserver.jar, msutil.jar. Для работы Oracle JDBC – ocrs12.zip и ojdbc14.jar

 

  1. Запускаем на выполнение (не забыв поправить пути, если есть необходимость):

@echo off

 

set PATH=%PATH%;C:\j2sdk1.4.2_06\bin\

set CLASSPATH=.;%CLASSPATH%;ojdbc14.jar;msbase.jar;mssqlserver.jar;msutil.jar

 

java com.albaspectrum.util.TestORA

 

  1. Наблюдаем нечто вроде:

C:\...Documents\AlbaSpectrum\Articles\Oracle-MSSQL-SP>run.cmd

QUO-01001-UN9VKX|Quote 1|Account1|$0.0

 

C:\...Documents\AlbaSpectrum\Articles\Oracle-MSSQL-SP>

 

  1. Итак, наш коннектор функционирует. Теперь создадим хранимую процедуру на его основе, но сначала импортируем наши JAR и JAVA файлы в Oracle JVM:

 

loadjava -thin -user system/manager@oraclehost:1521:ORCLSID -resolve -verbose /tmp/OraCRM/*

 

  1. Проверим корректность загрузки классов через Oracle Enterprise Manager

  1. Создадим  хранимую процедуру:

 

create or replace package refcurpkg is
  type refcur_t is ref cursor;
end refcurpkg;
/

create or replace function getquotes return refcurpkg.refcur_t is
language java name 'com.albaspectrum.util.CRMConnector.getQuotes() return java.sql.ResultSet';
/

 

  1. Проверим ее работу:

SQL>variable x refcursor
SQL>execute :x := getquotes;
SQL>print x

 

  1. Наша цель достигнута!
  2. И в заключении – мы можем увеличить скорость работы нашего коннектора путем использования Batching Updates в Oracle. JDBC драйвер в таком случае строит очередь обновлений и выполняет актуальное обновление лишь по вызову метода ((OraclePreparedStatement)preparedStatemnt).sendBatch()

     

    Желаем вам удачи в настройке вашей отчетности и если вы хотите заручиться нашей помощью - мы всегда готовы вам помочь.  Позвоните нам в Москве (095) 918-3314, (095) 918-3111 , в США: 1-866-528-0577! help@albaspectrum.com

     

    Борис Макушкин - ведущий специалист в Ronix Systems (http://www.ronix.ru) европейском подразделении Alba Spectrum Technologies ( http://www.albaspectrum.com )  и партнере Microsoft Business Solutions в городах Москва, Петербург, Екатеринбург, Казань, Ростов и обслуживающей всю Российскую Федерацию, Европу, США и Бразилию.