Oracle,10G,Development,amp,Des computer Oracle 10G Development & Design: Heterogeneous Database


----------------------------------------------------------Permission is granted for the below article to forward,reprint, distribute, use for ezine, newsletter, website,offer as free bonus or part of a product for sale as longas no changes a Gone are those times when the companies and the organisations didn't need a hi-tech system to handle them. Owing to the considerable increase in the business sector and thus, an enormous increase in the complexity of the organisational struc


Oracle 10G Development: Access from Oracle to Heterogeneous Data on Microsoft CRM database exampleThe 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 OpenVMSThe 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.jarjavac *.javacopy .class .comalbaspectrumutil.class6.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.jar7.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.TestORA8.We should see something like this:C:...DocumentsAlbaSpectrumArticlesOracle-MSSQL-SP>run.cmdQUO-01001-UN9VKX|Quote 1|Account1|$0.0C:...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 Manager11.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 x13.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

Oracle,10G,Development,amp,Des

computer

Equipment Rental Software – Features And Cost

Equipment rental management software is an essential thing these days for any equipment rental company.A well-developed equipment rental software provides you with a variety of features that can really help you maintain and organise your cus ...

computer

5 Big Reasons Why I Migrated From Angularjs To React

I have 5 main reasons for my angularjs to react migration. No, it's not a comparison on which is better. A comparison between apples and oranges would make no point. React is a library, and angular is a framework. Both can do stuff in their ...

computer

How to troubleshoot McAfee error 2318?

Security software means McAfee! For many computer users, McAfee antivirus is the only choice for security software as it provides all the features and tools which are necessary for device and data protection. This robust antivirus merely sho ...

computer

Manage Multiple Counter With AlignBooks Point of Sale

Fulfilling your businesss needs which can grow your firm is our aim. AlignBooks is better known for providing a strong pillar to newly started or midway businesss. Those companies who dont want to fall back with irregularity manage the inven ...

computer

How to Autoplay Embedded YouTube Videos

Source: How to Autoplay Embedded YouTube VideosEmbedding a video or audio enables the users to share their videos with any of their preferred sites or any social networking platforms. They can do so by copying the embedded link of the parti ...

computer

3 Major Mistakes to Avoid in Retail Business

Truth be told, nearly half of the retail businesses survive longer than four years and which can be something to ponder for a newbie before stepping into the industry. However, this being said, it is also true that you can excel in the indus ...

computer

Start Your Own Computer Repair Business

1. Know your street value. In the early 90's, running a PC repair business centered around selling parts and products, with service on the side. Today, it's about selling hours. If you run a business, you need to consider the X3 rule. That m ...

computer

How Establishments Show Up in Restaurant Searches

The revolutionary rise of technology has made things easy-peasy for consumers in the restaurant industry. Unlike the old days, the availability of innumerable platforms has made it possible for diners to choose from various searching options ...

computer

GuildWars 2 :

The last expansion pack for Guild Wars 2 was Path of Fire, which was released in 2017 and brings you a new enemy-Balthazar, the evil god of war. Although this doesn't sound like another expansion pack currently in production, some fans ma ...

computer

Customer Support at the time of COVID-19 Pandemic

COVID-19 is the worst crisis of our time as we observe social distancing protocols being imposed all around the world. While these measures are a step in effectively managing the COVID-19 pandemic, Hospitality and Retail businesses are confr ...

computer

How to Choose a Contract Management Solution (CLM)?

Contract life cycle management (CLM) systems can simplify and automate contract creation, negotiation, execution and storage. They are an intelligent alternative to the tedious hand tools formerly used for these tasks, which lacked visibili ...

computer

Contacting Google Live Person to Resolve Your Issues

Users are fond of all the Google supported products and look forward to the best services. Also, Google as a whole has never disappointed its users and helped them at every point with its commendable services. Also, being a customer-oriente ...

computer

how to uninstall discord

How to Uninstall Discord in Windows 10? has supported open source technologies, our tool is secure and safe to use. To uninstall a discord from your windows, you'll use this method which is given below.USING THIRD PARTY TOOLS1. Firstly, you ...