Oracle 10G Development: Access from Oracle to Heterogeneous Data on Microsoft CRM database example.
The designer of nowadays information systems often faces the difficult task of heterogeneous data access unification. Heterogeneous means that data maybe stored on different database platforms. Oracle Corporation provides developers with tools to address heterogeneous data access: Oracle Transparent Gateways and Generic Connectivity. Generic Connectivity gives you a common solution to access databases via ODBC and OLE DB mechanisms to FoxPro, Microsoft Access, etc. More interesting is the second product – Oracle Transparent Gateways. Its components are created individually for each platform, resulting in more efficient and fast access and better performance. Currently line of products is the following:
Oracle Transparent Gateway for Informix available on Solaris, HP/UX
Oracle Transparent Gateway for MS SQL Server available on NT.
Oracle Transparent Gateway for Sybase available on Solaris, HP/UX, NT, AIX, Tru64
Oracle Transparent Gateway for Ingres available on Solaris, HP/UX
Oracle Transparent Gateway for Teradata available on Solaris, NT, HP/UX
Oracle Transparent Gateway for RDB available on Alpha OpenVMS
Oracle Transparent Gateway for RMS available on Alpha OpenVMS
The disadvantage is the fact that these products are not available for all platforms.
The goal of today’s article is heterogeneous database access example from Oracle stored procedures to MS SQL Server 2000. The sample dataset will be pulled from MS CRM database. We’ll use rich functionality of Oracle stored procedures with Java utilization to access Microsoft CRM quotes via cursors mechanism. Our sample will have two parts – first we’ll model complete functionality of stored procedure in separate Java application and then we’ll transfer the code into Oracle RDBMS. Let’s begin:
1. We’ll create file CRMConnector.java, containing class definition to work with MS CRM data and returning dataset in the form of Java ResultSet – it will become the skeleton of the stored procedure method:
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. Some comments to this long-code method. In the first part of the method we are opening connection to MS CRM and Oracle database, where we’ll store pulled dataset in temporary table – created for cursor building in the next paragraphs. Next we check the existence of the temporary table definition for quotes storing in Oracle database. If table doesn’t exist – we execute its creation. DDL for the table is taken from QuoteBase definition in MS CRM database. The following is simple – we make selection from Microsoft CRM table and transfer the resulting data into Oracle temporary table. Important point is setCreateStatementAsRefCursor method call for resulting statement. ResultSet gives us the possibility to pull the data to form Oracle REF Cursor.
3. Just to remind you about temp table creation in Oracle. Temporary table, called also as global temporary tables are created in temporary table space of the user. Created once these tables exist until the moment its explicit deletion. But data in these tables “live” depending on the parameters givin at the table creation – in the scope and time of user session (ON COMMIT PRESERVE ROWS) or in the scope and time of the transaction (ON COMMIT DELETE ROWS). Syntax to create temporary table: CREATE GLOBAL TEMPORARY TABLE tablename (columns) [ON COMMIT PRESERVE|DELETE ROWS]. we need unique ability, provided by temporary table – temporary segments clearing upon the termination of the user session, considering the fact that table structure is similar for every user, but data is uniqu for each session. To get more detail information of the temporary table logic we suggest you to look at “Oracle Database Concepts” manual
4. Lets create small application to test CRM connector functionality:
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();
                }
           }
}
5. Build the project in command prompt ( you may need to change paths for the components installed on your computer:
@echo off
set PATH=%PATH%;C:\j2sdk1.4.2_06\bin\
set CLASSPATH=.;%CLASSPATH%;ojdbc14.jar
javac *.java
copy .class .\com\albaspectrum\util\.class
6. To make MS SQL JDBC driver functional, we place in the current catalogue these file msbase.jar, mssqlserver.jar, msutil.jar. For Oracle JDBC – ocrs12.zip and ojdbc14.jar
7. To launch execution(do not forget the paths as mentioned above):
@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
8. We should see something like this:
C:\...Documents\AlbaSpectrum\Articles\Oracle-MSSQL-SP>run.cmd
QUO-01001-UN9VKX|Quote 1|Account1|$0.0
C:\...Documents\AlbaSpectrum\Articles\Oracle-MSSQL-SP>
9. Our connector works. It is time to create stored procedure on its base, but first we import our JAR and JAVA files into Oracle JVM:
loadjava -thin -user system/manager@oraclehost:1521:ORCLSID -resolve -verbose /tmp/OraCRM/*
10. Lets test classes loading in Oracle Enterprise Manager
11. Create stored procedure:
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';
/
12. Test its work:
SQL>variable x refcursor
SQL>execute :x := getquotes;
SQL>print x
13. And our goal is achieved!
14. As a final task we can increase the connector performance using Batching Updates in Oracle. JDBC in this case builds the queue of the updates and executes actual update when we call the method ((OraclePreparedStatement)preparedStatemnt).sendBatch()
Boris Makushkin is senior software developer in Alba Spectrum Technologies US nation-wide Great Plains, Microsoft CRM customization company, based in Chicago and having locations in multiple states and internationally (www.albaspectrum.com ), he is Unix, SQL, C#.Net, Crystal Reports, Microsoft CRM SDK and Exchange Server SDK developer. You can reach Boris: borism@albaspectrum.com
