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/
Posts
