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.



Comments

  • Problems connecting with PHP n MySql

    Posted by mk2 on 09/27/2006 08:09pm

    This is Sastry MKS. I am not able to connect PHP with MySql. Basically, I want to log on to MySql server from PHP page, all located on same computer. Any hints ?? Any sample code? Thanks in advance - MKS Sastry, Hyderabad, India

    • Interfacing Visual C++ with Oracle

      Posted by mk2 on 03/28/2007 09:21pm

      This is Musti Sastry looking for help on Oracle.  What is the best way to interface Visual C++ with Oracle 10g?  We have a big application developed using Visual C++.  Now we want the data generated to go into a Oracle DB.  
      
      What are the advantages if we use .NET/ VC++ .NET? please elaborate.
      
      Musti Sastry, Hyderabad

      Reply
    Reply
  • it is working

    Posted by mk2 on 01/15/2006 07:08pm

    This is Sastry MKS Thanks for a nice example. Is it possible to implement the same with MySql?? Please reply -MKS Sastry, Hyderabad, India

    Reply
Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Live Event Date: October 29, 2014 @ 11:00 a.m. ET / 8:00 a.m. PT Are you interested in building a cognitive application using the power of IBM Watson? Need a platform that provides speed and ease for rapidly deploying this application? Join Chris Madison, Watson Solution Architect, as he walks through the process of building a Watson powered application on IBM Bluemix. Chris will talk about the new Watson Services just released on IBM bluemix, but more importantly he will do a step by step cognitive …

  • Businesses are moving more and more of their customer transactions to the web. Security is understandably a top concern as online transactions increase, so it is important to make sure your electronic signature provider meets the highest security standards. That means more than simply passing a security audit or obtaining a certification. This white paper provides recommendations for taking a broader view of e-signature security, and answers key questions that help identify the security requirements against …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds