|
|
Oracle 10G Development: Oracle-MS SQL Server Integration Tips
Noris Makushkin
Two Worlds, two different ideologies, two constant rivals - is it possible to give more precise description for two RDBMS World giants - Oracle Database and MS SQL Server? Mountains of TPC tests, proving superiority of one or the other side. Legions of supporters for one product and the other, ardently proving the advantages and accepting absolutely no arguments, revealing their favorite product' weak points. Counterparts of SQL Servers& Is it possible to coexist in one environment for these two absolutely different beings? Yes, of course it is! And the goal of our publication, consisting from several articles - to give you few hints and tips for seamless data transfer and integration between these two universes.
Our today's article topic is Oracle demonstration to use primary and secondary keys in the Microsoft SQL Server style (Guid datatype). Oracle specific is built-in java machine utilization - you can create stored procedures of any complexity level with this machine. We'll use this feature in our article. Let's begin.
1. GUID generation reason - we need unique key to identify the entity. Oracle suggests to use sequence mechanism, but in order to provide application transferability between Oracle and MS SQL Server it is better to choose the mechanism deployed in MS SQL Server (at least - this is my opinion to use this approach)
2. To realize GUID generation we will use unique time values, IP addresses and several random values from the pseudo random numbers table. In model environment - this is OK, however in the production system it is recommended to use your network card MAC address - you can read it with the help of C/C++ library, you can access it trhough JMI interface. Let's look at GUID generation:
package com.albaspectrum.util;
import java.security.*;
import java.util.*;
import java.io.*;
import java.net.*;
import java.rmi.server.*;
public class Guid {
private String internalGuid = null;
public String newGuid() throws Exception {
setGuid(generateGuid());
return getGuid();
}
public String getGuid() {
return internalGuid;
}
public void setGuid(String assignedGuid) {
internalGuid = assignedGuid;
}
public String generateGuid() throws Exception {
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
PrintStream printStream = new PrintStream(outputStream);
MessageDigest messageDigest = MessageDigest.getInstance("MD5");
messageDigest.update(getSourceMessage());
byte[] byteDigest = messageDigest.digest();
if (byteDigest != null) {
for(int i = 0; i < byteDigest.length; i++)
hexDigit(printStream, byteDigest[i]);
} else {
throw new Exception("Got broken GUID");
}
return (outputStream.toString());
}
private static void hexDigit(PrintStream p, byte x) {
char c;
c = (char) ((x >> 4) & 0xf);
if (c > 9) {
c = (char) ((c - 10) + 'a');
} else {
c = (char) (c + '0');
}
p.write(c);
c = (char) (x & 0xf);
if (c > 9) {
c = (char)((c - 10) + 'a');
} else {
c = (char)(c + '0');
}
p.write(c);
}
protected byte[] getSourceMessage() throws Exception {
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
PrintStream printStream = new PrintStream(outputStream);
printStream.print(getSourceString());
return outputStream.toByteArray();
}
protected String getSourceString() throws Exception {
StringBuffer resultString = new StringBuffer(InetAddress.getLocalHost().toString());
resultString.append("; ").append(new Date().toString());
resultString.append("; ").append(new Integer(new Random().nextInt()).toString());
resultString.append("; ").append(new Integer(new Random().nextInt()).toString());
resultString.append("; ").append(new Integer(new Random().nextInt()).toString());
resultString.append("; ").append(new Integer(new Random().nextInt()).toString());
resultString.append("; ").append(new UID().toString());
return resultString.toString();
}
public static String getMsStyleGuid(String guid) {
String formattedGuid = "{" + guid.substring(0, 8) + "-" + guid.substring(8, 12) + "-" +
guid.substring(12, 16) + "-" + guid.substring(16, 20) + "-" + guid.substring(20, 32) + "}";
return formattedGuid;
}
public static String getGuidForOracle() throws Exception {
Guid guid = new Guid();
String rawGuid = guid.newGuid();
return getMsStyleGuid(rawGuid);
}
}
1. In this class we are looking at the last method - getGuidForOracle(). This method gives us GUID value, that we are used to in MS SQL Server.
2. We will compile our class with javac compiler to be sure that we are bug-free. We can use this small program for testing purpose:
package com.albaspectrum.util;
public class TestGuid {
public static void main(String args[]) throws Exception {
Guid guid = new Guid();
String rawGuid = guid.newGuid();
System.out.println("Generated GUID: " + rawGuid);
System.out.println("Generated MS-style GUID: " + Guid.getMsStyleGuid(rawGuid));
}
}
3. Then, transfer file Guid.java (which contains our generator) to the host, where we have Oracle Database. Register as Oracle user and load our class into Oracle Java Machine:
oracle@vega:~/product/10gR1/bin> loadjava -u system/manager -v -resolve /tmp/Guid.java
arguments: '-u' 'system/manager' '-v' '-resolve' '/tmp/Guid.java'
creating : source com/albaspectrum/util/Guid
loading : source com/albaspectrum/util/Guid
created : CREATE$JAVA$LOB$TABLE
resolving: source com/albaspectrum/util/Guid
oracle@vega:~/product/10gR1/bin>
4. Check our class - in order to do this give this command in iSQL:
SELECT * FROM user_objects WHERE object_type LIKE JAVA%'
Pay special attention to the fields OBJECT_NAME and STATUS
5. Now we need to create PL/SQL function to access the method getGuidForOracle:
create or replace function GET_GUID
RETURN VARCHAR2
AS LANGUAGE JAVA
NAME 'com.albaspectrum.util.Guid.getGuidForOracle() return String';
6. Let's test the function, just created:
SET SERVEROUTPUT ON
VARIABLE guid VARCHAR2(38);
CALL GET_GUID() INTO :guid;
PRINT guid
7. Finally we can set up default values for required fields in our Oracle tables as the returning value from GET_GUID(). And we are now done!
Happy developing, and integrating! If you want us to do the job - give us a call 1-866-528-0577 or 1-630-961-5918! help@albaspectrum.com
Boris Makushkin is Lead Software Developer in Alba Spectrum Technologies - USA nationwide Oracle, Navision, Microsoft CRM, Microsoft Great Plains customization company, serving Chicago, California, Arizona, Colorado, Texas, Georgia, Florida, Canada, Australia, UK, Russia, Europe and internationally ( http://www.albaspectrum.com ), he is Oracle, Unix, Microsoft CRM SDK, Navision, C#, VB.Net, SQL developer.
Author Site
1/23/2008
|
|
