Using Regular Expression with Oracle 8 and above

From: Manuel FLURY <manuel.flury_at_free.fr>
Date: Tue, 14 Sep 2004 20:17:59 +0200
Message-Id: <414735bb$0$29065$626a14ce@news.free.fr>

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

I've try to find this info on different search engine unsuccessfully, so let's post this here !

HOW TO USE REGEXP IN ORACLE 8 AND ABOVE : I suppose that you've installed the java virtual machine that comes with the Oracle DB, plus the regexp.jar file.

Check JRE symlink in $ORACLE_HOME

Set PATH + CLASSPATH correctly

PATH=$ORACLE_HOME/bin:$ORACLE_HOME/jdk/bin:$PATH CLASSPATH=$CLASSPATH:$ORACLE_HOME/jlib/regexp.jar:$ORACLE_HOME/jlib

Now load into Oracle the regexp.jar class :

$ cd $ORACLE_HOME/jlib
$ $ORACLE_HOME/bin/loadjava -user ORAUSER/PASSWORD_at_ORADB -resolve -verbose
$ORACLE_HOME/jlib/regexp.jar

arguments: '-user' 'ORAUSER/PASSWORD_at_ORADB' '-resolve' '-verbose' 'regexp.jar'

created  : JAVA$CLASS$MD5$TABLE
creating : resource META-INF/MANIFEST.MF
created  : CREATE$JAVA$LOB$TABLE
loading  : resource META-INF/MANIFEST.MF
creating : class org/apache/regexp/ReaderCharacterIterator
loading  : class org/apache/regexp/ReaderCharacterIterator

.../...

resolving: class org/apache/regexp/REDemo
skipping : class org/apache/regexp/REDemo$1
skipping : class org/apache/regexp/CharacterIterator
resolving: class org/apache/regexp/CharacterArrayCharacterIterator

Create a file somewhere on the machine hosting the database called EXACTLY with same filename as the java class defined in the file. I guess you can do this from a client with everything needed installed (oracle client + java dev tools) and remotely load the java class.

RegExpUtil.java :

- --------------------------------------------------------------------------

package org.dir.div.common;

import org.apache.regexp.RE;

/**
 * Regular Expression resolver.<br>
 */

public class RegExpUtil {

    public static final int MATCH = 0;
    public static final int NO_MATCH = 1;     public static final int ERROR = 2;

    public static int match(String pattern, String value) {

        // Check parameters
        if (pattern ==null || value ==null) return ERROR;

        // Instanciate Jakarta regular expression evaluator/matcher class.
        RE resolver = null;
        try {
            resolver = new RE(pattern);
        } catch (Exception exp) {
            exp.printStackTrace();
            return ERROR;
        } // end try

        // Evaluate value string
        return resolver.match(value)==true?MATCH:NO_MATCH;

    } // end public int match()

} // end public class RegExpUtil

- --------------------------------------------------------------------------

Compile it :

$ORACLE_HOME/jdk/bin/javac RegExpUtil.java

Load it into Oracle :

$ORACLE_HOME/bin/loadjava -user ORAUSER/PASSWORD_at_ORADB -resolve -verbose
RegExpUtil.class

Check that Oracle now it with :

SELECT * FROM dba_objects WHERE object_type LIKE '%JAVA%' AND OWNER='ORAUSER'; Now create the PL/SQL wrapper :

( Some syntax highlighting editor change the case of some words, java may not work with such behaviour (ex. : JAVA uppercased for instance in java.lang.String), uses SQL*Plus only )

CREATE OR REPLACE FUNCTION Regexp_Like (

   pattern IN VARCHAR2,
   val IN VARCHAR2)
   RETURN NUMBER
AS LANGUAGE JAVA
   NAME 'org.dir.div.common.RegExpUtil.match (

            java.lang.String, java.lang.String)
            return int';

/

And then to find in table EMPLOYEES records with a SURNAME column matching starts with DENI :

select * from dir_subs where regexp_like('^DENI',SURNAME)=0

With number :

SELECT * FROM dir_subs WHERE regexp_like('[:digit:]',SURNAME)=0

...etc...

Note that there is nothing to do with the REGEXP% function introduced in 10g.

Thanks to Olivier for this info !

Manuel FLURY

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (GNU/Linux)

iD8DBQFBRzXdvLx06ky64ukRAo2gAJ9Vqk+ULY4T+CATzayAAaeVQLea+wCfUrNz qREWGjq7jNNsMFQVBFxfm2A=
=NZkL
-----END PGP SIGNATURE----- Received on Tue Sep 14 2004 - 13:17:59 CDT