Oracle 10G Development & Design: Heterogeneous Database Access

Oracle 10G Development: Access from Oracle to Heterogeneous Data on a Microsoft CRM Database Example

The designers of a newer information system often faces the difficult task of heterogeneous data access unification. Heterogeneous means that data may be stored on different database platforms. Oracle Corporation provides the developer with tools to address heterogeneous data access: Oracle Transparent Gateways and Generic Connectivity. Generic Connectivity gives you a common solution to access a database via ODBC and OLE DB mechanisms to FoxPro, Microsoft Access, and so forth. More interesting is a second product—Oracle Transparent Gateways. Its components are created individually for each platform, resulting in more efficient and faster access and better performance. Currently, the 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 a heterogeneous database access example from Oracle stored procedures to MS SQL Server 2000. The sample dataset will be pulled from a MS CRM database. You’ll use the rich functionality of Oracle stored procedures with Java utilization to access Microsoft CRM quotes via a cursors mechanism. Your sample will have two parts: First, you’ll model the complete functionality of a stored procedure in a separate Java application; then, you’ll transfer the code into Oracle RDBMS. Here’s how to begin:

  1. Create a CRMConnector.java file, containing a class definition to work with MS CRM data and the returning dataset in the form of Java ResultSet—it will become the skeleton of the stored procedure method:
  2. 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;
       }
    }
    
  3. Here are some comments to this long-code method. In the first part of the method, you open a connection to MS CRM and an Oracle database, where you’ll store the pulled dataset in a temporary table—created for cursor building in the next paragraphs. Next, you check the existence of the temporary table definition for quotes storing in Oracle database. If the table doesn’t exist, you execute its creation. The DDL for the table is taken from the QuoteBase definition in the MS CRM database. The following is simple—you make a selection from the Microsoft CRM table and transfer the resulting data into the Oracle temporary table. The important point is the setCreateStatementAsRefCursor method call for the resulting statement. ResultSet gives you the potential to pull the data to form an Oracle REF Cursor.
  4. This is just to remind you about temp table creation in Oracle. A temporary table, also called a global temporary table, is created in the temporary table space of the user. Once created, these tables exist until the moment of their explicit deletion. But, data in these tables “live” depending on the parameters given at the table creation—in the scope and time of the user session (ON COMMIT PRESERVE ROWS) or in the scope and time of the transaction (ON COMMIT DELETE ROWS). Here is the syntax to create a temporary table: CREATE GLOBAL TEMPORARY TABLE tablename (columns) [ON COMMIT PRESERVE|DELETE ROWS]. You need the unique ability that is provided by a temporary table—temporary segments clearing upon the termination of the user session, considering the fact that the table structure is similar for every user, but the data is unique for each session. To get more detailed information about the temporary table logic, look at the “Oracle Database Concepts” manual.
  5. Now, create a small application to test CRM connector functionality:
  6. 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();
          }
       }
    }
    
  7. Build the project at a command prompt. (You may need to change paths for the components installed on your computer.)
  8. @echo off
    
    set PATH=%PATH%;C:\j2sdk1.4.2_06\bin\
    set CLASSPATH=.;%CLASSPATH%;ojdbc14.jar
    
    javac *.java
    
    copy .class .\com\albaspectrum\util\.class
    
  9. To make the MS SQL JDBC driver functional, we place msbase.jar, mssqlserver.jar, and msutil.jar in the current catalogue. For Oracle JDBC – ocrs12.zip and ojdbc14.jar.
  10. To launch execution (do not forget the paths as mentioned above):
  11. @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
    
  12. You should see something like this:
  13. C:\...Documents\AlbaSpectrum\Articles\Oracle-MSSQL-SP>run.cmd
    QUO-01001-UN9VKX|Quote 1|Account1|$0.0
    
    C:\...Documents\AlbaSpectrum\Articles\Oracle-MSSQL-SP>
    
  14. Your connector works. It is time to create a stored procedure on its base. But first, you import your JAR and JAVA files into Oracle JVM:
  15. loadjava -thin -user system/manager@oraclehost:1521:ORCLSID
             -resolve -verbose /tmp/OraCRM/*
    
  16. Test the classes loading in Oracle Enterprise Manager.
  17. Create a stored procedure:
  18. 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';
    /
    
  19. Test its work:
  20. SQL>variable x refcursor
    SQL>execute :x := getquotes;
    SQL>print x
    
  21. And your goal is achieved!
  22. As a final task, you can increase the connector performance using Batching Updates in Oracle. JDBC in this case builds the queue of the updates and executes the actual update when you call the ((OraclePreparedStatement)preparedStatemnt).sendBatch() method.

Abvout the Author

Boris Makushkin is a lead software developer with Alba Spectrum Technologies (http://www.albaspectrum.com), a USA nationwide MS CRM consulting company.

More by Author

Get the Free Newsletter!

Subscribe to Data Insider for top news, trends & analysis

Must Read