Using java to extend dbms_crypto


I don’t normally write a lot of java stored procedures.  They simply aren’t necessary most of the time; but today somebody asked me a question that just seemed a perfect fit.  They wanted to know how to generate a SHA-256 hash for a given string.  For most hashing tasks I’d recommend Oracle’s built-in package DBMS_CRYPTO; but it can only support the SHA1 algorithm, for 256 bits we need to go to SHA2.  Fortunately, implementing such a thing in java is quite simple.

Using java.security.MessageDigest we can implement 256, 384 and 512 bit algorithms.

Here’s a simple function that supports all three.

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED sha2 AS
import java.security.MessageDigest;
import oracle.sql.*;

public class sha2
{
public static oracle.sql.RAW get_digest( String p_string, int p_bits ) throws Exception
{
MessageDigest v_md = MessageDigest.getInstance( “SHA-” + p_bits );
byte[] v_digest;
v_digest = v_md.digest( p_string.getBytes( “UTF-8” ) );
return RAW.newRAW(v_digest);
}
}
/

CREATE OR REPLACE FUNCTION sha2(p_string in VARCHAR2, p_bits in number)
RETURN RAW
AS
LANGUAGE JAVA
NAME ‘sha2.get_digest( java.lang.String, int ) return oracle.sql.RAW’;
/

Using the examples in http://en.wikipedia.org/wiki/SHA-2 we can verify the algorithms return the expected results.
SELECT sha2(‘The quick brown fox jumps over the lazy dog’,256) FROM DUAL
union all
SELECT sha2(‘The quick brown fox jumps over the lazy dog’,384) FROM DUAL
union all
SELECT sha2(‘The quick brown fox jumps over the lazy dog’,512) FROM DUAL

Oracle Database Gateway for ODBC


I’ve known about Heterogeneous Services and Gateways for a while but recently got around to playing with them.  Like others, I chose the ODBC Gateway because the database license includes the ODBC usage, unlike the rest of the Gateways that are each licensed separately.  I’ll post a link to a full article later, but I wanted to post just how easy it really is.  First, create an ODBC DSN for whatever database and platform you want.  Oracle doesn’t supply the drivers for you though.

Once you have the DSN, a few config files are all that is needed to use it.

In your listener.ora

(SID_DESC =
(PROGRAM = dg4odbc)   #for 10g you would use “hsodbc” instead of “dg4odbc”
(SID_NAME = xxx)
(ORACLE_HOME = D:\oracle\product\11.2.0)   #Obviously, change this to your home
)

In your tnsnames.ora (or ldap if you use that) create an entry like you would for any other db except add the (HS=OK ) stanza.

xxx.your.domain.com =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=mydbserver)(PORT=1521))
(CONNECT_DATA=(SID=xxx))
(HS=OK)
)

Create a heterogeneous services initialization file: $ORACLE_HOME/hs/admin/initxxx.ora  It could be as simple as one line, but I include the TRACE and SUPPORT lines as placeholders but disabled

HS_FDS_CONNECT_INFO = xxx
HS_FDS_TRACE_LEVEL = off
HS_FDS_SUPPORT_STATISTICS=FALSE

That’s all there is to it.  Now you can create a db link just as you would for an Oracle database.

CREATE DATABASE LINK xxx_link CONNECT TO “user” IDENTIFIED BY “password” USING ‘xxx’;

There are, of course, some limitations, but I’ve been happy so far with the easy of setup and the ability to transfer data easily without the need for flat file export/import or 3rd party migration tools.

The instructions work for both 11g XE and Enterprise Editions I’ve tested with

%d bloggers like this: