validating emails in oracle db using java stored function => and testing from ruby

1. email validation function is written in java (flexibility) and compiled as stored procedure in oracle db (performance)

1a) write EmailValidations.java:

import java.lang.String;
import java.util.regex.Pattern;
import java.util.regex.Matcher;

class EmailValidations {
  public static int is_valid(String email) {
    if (email == null || email == "") return 0;

    String upper_email = email.toUpperCase();

    Pattern p = Pattern.compile("^[\\w\\.-]*[^\\.]@([\\w\\-]+\\.)+[A-Z]{2,4}$");
    Matcher m = p.matcher(upper_email);
    return m.matches() ? 1: 0;
  }
}

1b) load and compile java class into your oracle db:

$ loadjava -u scott/tiger@emeadb11 -v -resolve EmailValidations.java
--> replace scott/tiger@emeadb11 with your valid username/password@tnsname_entry

2a). create package that will contain your function

create or replace PACKAGE             "MISC_PKG"
IS
FUNCTION is_email_valid (email_address in varchar) RETURN number;
END MISC_PKG;
/

2b). create package body:

create or replace
PACKAGE BODY             "MISC_PKG" AS

FUNCTION is_email_valid (email_address in varchar) RETURN number
AS LANGUAGE JAVA
NAME 'EmailValidations.is_valid(java.lang.String) return int';

END MISC_PKG;
/

3) test with ruby-plsql

3a) spec_helper.rb that will contain your credentials and include rspecs and ruby-plsql gems

require 'rubygems'
require 'rspec'
require 'ruby-plsql'

DB_USER = "scott"
DB_PWD  = "tiger"
DB_SRVR = "emeadb11"

plsql.connection = OCI8.new DB_USER, DB_PWD, DB_SRVR
p "connected"

plsql.connection.autocommit = false

RSpec.configure do |config|
  # config.filter_run :focus => true
  config.after(:each) do
    plsql.rollback
  end
end

3b) write ruby specs spec_is_email_valid.rb

require File.dirname(__FILE__) + '/spec_helper'

describe "check is_email_valid function" do
  
  it "should return 1 for valid emails" do
    plsql.misc_pkg.is_email_valid("c123@o2.pl").should == 1 
    plsql.misc_pkg.is_email_valid("c@o2.pl").should == 1 
  end
  
  it "should return 0 for invalid emails" do
    plsql.misc_pkg.is_email_valid("c123@o2,pl").should == 0 
    plsql.misc_pkg.is_email_valid("c@o2pl").should == 0 
  end

  it "should return 0 as in email_err_reason" do
    plsql.misc_pkg.is_email_valid("S.J@GMAIL").should == 0
    plsql.misc_pkg.is_email_valid("SY.STORT").should == 0
    plsql.misc_pkg.is_email_valid("01516081607").should == 0
  end
    
    
  it "should return 0 when invalid characters in it" do
    plsql.misc_pkg.is_email_valid("J.AMS.@CATS.ORG").should == 0
    plsql.misc_pkg.is_email_valid("STNJ@ISON.CO.ZA/JACO@VTR.CO.ZA").should == 0
  end
  
  it "should return 0 when null email" do
    plsql.misc_pkg.is_email_valid("").should == 0
    plsql.misc_pkg.is_email_valid(nil).should == 0
  end
end

4) you can test it with:

$ rspec spec_is_email_valid.rb 
"connected"
.....

Finished in 5.64 seconds
5 examples, 0 failures

You can easily extend your tests once the change was done to your is_valid() java function.

For more information on testing pl/sql functions from ruby see excelent blog (author of ruby-plsl):
http://blog.rayapps.com/2009/11/27/oracle-plsql-unit-testing-with-ruby/