Oracle 10G Development & Design: Heterogeneous Database Access

Oracle 10G Development: Access from Oracle to Heterogeneous Data on Microsoft CRM database example

The designer of nowadays information system often faces difficult task of heterogeneous data access unification.  Heterogeneous means that data maybe stored on different database platforms.  Oracle Corporation provides developer with tools to address heterogeneous data access: Oracle Transparent Gateways and Generic Connectivity.  Generic Connectivity gives you common solution to access database via ODBC and OLE DB mechanisms to FoxPro, Microsoft Access, etc.  More interesting is 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 databasesClass.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 connectionoracleConn.setAutoCommit(false);                                // Create Oracle temp tableStatement 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 tableStatement 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 statementStatement selectOracleStmt = oracleConn.createStatement();// Query all columns from the EMP tableResultSet 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 offset PATH=%PATH%;C:j2sdk1.4.2_06binset CLASSPATH=.;%CLASSPATH%;ojdbc14.jar javac *.javacopy .class .comalbaspectrumutil.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 offset PATH=%PATH%;C:j2sdk1.4.2_06binset CLASSPATH=.;%CLASSPATH%;ojdbc14.jar;msbase.jar;mssqlserver.jar;msutil.jarjava com.albaspectrum.util.TestORA

8. We should see something like this:

C:...DocumentsAlbaSpectrumArticlesOracle-MSSQL-SP>run.cmdQUO-01001-UN9VKX|Quote 1|Account1|$0.0 C:...DocumentsAlbaSpectrumArticlesOracle-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 islanguage 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()

Happy developing and designing!  If you would like us to do the job, give as a call 1-630.961.5918 or 1-866.528.0577 help@albaspectrum.com