CREATE OR REPLACE PACKAGE wb_rt_pattern_analysis AUTHID CURRENT_USER AS

DATE_TYPE        NUMBER:= 0;
NUMBER_TYPE      NUMBER:= 1;
VARCHAR2_TYPE    NUMBER:= 2;
NULL_TYPE        NUMBER:= 3;
DATE_TYPE_0      NUMBER:= 4;
DATE_TYPE_1      NUMBER:= 5;
DATE_TYPE_2      NUMBER:= 6;
DATE_TYPE_3      NUMBER:= 7;

DDMONYYYY        VARCHAR2(1000):=
  '^([[:space:]]*[0-9]{2}[[:punct:]](JAN|FEB|MAR|APR|MAY|JUN|JUL|AUG|SEP|OCT|NOV|DEC){1}[[:punct:]][0-9]{4}[[:space:]]*)$';
DDMONYY          VARCHAR2(1000):=
  '^([[:space:]]*[0-9]{2}[[:punct:]](JAN|FEB|MAR|APR|MAY|JUN|JUL|AUG|SEP|OCT|NOV|DEC){1}[[:punct:]][0-9]{2}[[:space:]]*)$';
DDMMYY           VARCHAR2(1000):=
  '^([[:space:]]*[0-9]{2}[[:punct:]][0-9]{1}[[:punct:]][0-9]{2}[[:space:]]*)$';
DDMMYYYY         VARCHAR2(1000):=
  '^([[:space:]]*[0-9]{2}[[:punct:]][0-9]{1}[[:punct:]][0-9]{4}[[:space:]]*)$';

NUMPATTERN       VARCHAR2(1000):= 
  '^([[:space:]]*[+-]?[0-9]+\.[0-9]+[[:space:]]*)$';

US_SS_PATTERN    VARCHAR2(1000):= 
  '^([[:space:]]*[0-9]{3}[[:punct:]][0-9]{2}[[:punct:]][0-9]{4}[[:space:]]*|[[:space:]]*[0-9]{9}[[:space:]]*)$';
US_PHONE_PATTERN VARCHAR2(1000):= 
  '^([[:space:]]*[0-9]{3}[[:punct:]][0-9]{3}[[:punct:]][0-9]{4}[[:space:]]*|[[:space:]]*[0-9]{10}[[:space:]]*)$';
EMAIL_PATTERN    VARCHAR2(1000):=
  '^([[:space:]]*[A-Za-z0-9]+[\.[A-Za-z0-9]+]*\@[A-Za-z0-9]+\.[A-za-z]{3}[[:space:]]*)$';
IP_PATTERN    VARCHAR2(1000):=
  '^([[:space:]]*[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}[[:space:]]*)$';

FUNCTION getPattern(value in VARCHAR2) RETURN VARCHAR2;
FUNCTION getGeneralPattern(value in VARCHAR2) RETURN VARCHAR2;
PROCEDURE generatePatternLookups(tblID IN NUMBER);
PROCEDURE dropPatternLookups(tblID IN NUMBER);
FUNCTION getPatternLookupName(column_name IN VARCHAR2,tblID IN NUMBER) 
  RETURN VARCHAR2;
FUNCTION simpleFormat(pattern IN VARCHAR2) RETURN VARCHAR2;

END wb_rt_pattern_analysis;
/