JoeGarrepy.com Oracle Pipelined Function Example

Programming/
Tools
      Oracle
      JavaScript
      C#

Fun Stuff
      Music
      Martial Arts
      Robotech

Other
      Main
DISCLAIMER
This disclaimer is intended to cover all software/freeware, and other various files which are downloadable through or from this site.
Joe Garrepy, isp, and web host can not be held responsible for any software downloaded from this site. All freeware is downloaded at your own risk. Software which is listed on this site is assumed to work properly, but may or may not have different effects on different computer systems. The same rules apply to these programs. Joe Garrepy assumes no responsibility for those unusual occurrences where software has adverse effects on users computer. Joe Garrepy does not warrant these programs/files, and as such, can offer no guidelines in which to use them. All freeware/software/files, trademarks, service marks, and copyrights are acknowledged as belonging to their owners. Any of these which the owner wishes to be removed will be done so immediately. By downloading any software or viewing pages in which software can be downloaded, you agree to be bound to this agreement.
JoeGarrepy.com Oracle String Package

This package is designed to work with Strings (VARCHAR2, VARCHAR, CHAR) in Oracle and make processing them easier. Inside you will find functions that will split strings, turn them into tables, check to see if they are mixed case, a number, a date or an ordinal and toggle case in various combinations. This link will take you to the PLDOC for this Package..
Alphabet Table SQL Script
CREATE OR REPLACE
PACKAGE str
IS
 /**
 * <HR>
 * STR Package 12/21/2004 JG<BR>
 * Package contains different procedures and functions to deal with strings
 * <HR>AUDIT TRAIL
 * <HR>VERSION 1.0 3/8/2005<BR>
 * Package created to help processing strings.
 * <HR>
 * @headcom
 */
 --
 /**
 * TYPE is a record of string for string procesing
 */
  TYPE rec_str IS RECORD (string_value VARCHAR2(4000));
 /**
 * TYPE is a table of rec_str
 */
  TYPE tab_str IS TABLE OF rec_str;
 /**
 * Function will split a string.  The maximum size returnable
 * is 4000 bytes and up to 15 fields.  <br>
 * For Exammple:<br>
 * <PRE>
 * SQL> select str.split('a,b,c,d',3,',') from dual;
 *
 * STR.SPLIT('A,B,C,D',3,',')
 * --------------------------------------------------------------------------------
 * c
 *</PRE>
 * select str.split('a,b,c,d',3,',') from dual;
 * @param in_del_field             string to be split
 * @param in_position              position of split character(s) to return
 * @param in_del                   delimter to split by
 * @return Returns a single value from a string, up to 4K long
 */
  FUNCTION split
  (in_del_field  IN     VARCHAR2,
   in_pos        IN     NUMBER,
   in_del        IN     VARCHAR2)
  RETURN VARCHAR2;
 /**
 * Type is a collection/array of values returned from the split_array function
 */
  TYPE tab_split IS TABLE OF VARCHAR2(4000);
 /**
 * Funciton takes a string passed to it and returns it as a collection of the
 * type tab_split.  For Example
 * <PRE>
 * DECLARE
 *   t_split str.tab_split := str.split_array('a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z',',');
 *   i       INTEGER;
 * BEGIN
 *   FOR i IN t_split.FIRST .. t_split.LAST LOOP
 *     DBMS_OUTPUT.PUT_LINE(t_split(i));
 *   END LOOP;
 * END;
 * /
 * </PRE>
 * @param in_del_field             string to be split
 * @param in_del                   delimter to split by
 * @return Returns an Array of type str.tab_split with a row for each delimited value in in_del_field.
 */
  FUNCTION split_array
  (in_del_field  IN     VARCHAR2,
   in_del        IN     VARCHAR2)
  RETURN tab_split;
 /**
 * Pipelined Function returns a table of one column with the deleminted string
 * passed to it.  For example, you could do the following to return a table of
 * the alphabet...
 * <PRE>
 * SELECT *
 * FROM TABLE(str.split_pipe('a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z',','))
 * </PRE>
 * @param in_del_field             string to be split
 * @param in_del                   delimter to split by
 * @return Returns a Piplined Table of Table Type tab_str with a row for each delimited value in in_del_field.
 */
  FUNCTION split_pipe
  (in_del_field  IN     VARCHAR2,
   in_del        IN     VARCHAR2)
  RETURN tab_str PIPELINED;
 /**
 * Function returns a TRUE if all characters in string are upper Case
 * @param in_string                String that could be upper case
 * @return Returns a boolean TRUE if string is a upper case string, else a FALSE is returned
 */
  FUNCTION is_upper_case
  (in_string   IN   VARCHAR2)
  RETURN BOOLEAN;
 /**
 * Function returns a TRUE if all characters in string are Lower Case
 * @param in_string                String that could be Lower case
 * @return Returns a boolean TRUE if string is a Lower case string, else a FALSE is returned
 */
  FUNCTION is_lower_case
  (in_string   IN   VARCHAR2)
  RETURN BOOLEAN;
 /**
 * Function will return a BOOLEAN TRUE if string passed to it has mixed case.
 * @param in_string                String that could contain mixed case
 * @return Returns a boolean TRUE if string is a mixed string, else a FALSE is returned
 */
  FUNCTION is_mixed_case
  (in_string   IN   VARCHAR2)
  RETURN BOOLEAN;
 /**
 * Function will return a boolean TRUE if a string passed to the function
 * is a numeric value for example
 * <PRE>
 * BEGIN
 *   If str.is_number('3') Then
 *     DBMS_OUTPUT.PUT_LINE('True');
 *   Else
 *     DBMS_OUTPUT.PUT_LINE('False');
 *   End If;
 * END;
 * </PRE>
 * @param in_value Value that is evaluated to see if it is a number
 * @return Returns a BOOLEAN True if Chacter value passed is considered a number, else return a BOOLEAN False.
 */
  FUNCTION is_number
  (in_value   IN   VARCHAR2)
  RETURN BOOLEAN;
 /**
 * Function will return a VARCHAR2 value of 'TRUE' if a string passed to the function
 * is a numeric value, else it will return a value of 'FALSE'.  This function was
 * designed to be used in SQL statements since booleans values are only easliy handeled
 * from PL/SQL.  For example..
 * <PRE>
 * SQL> SELECT str.is_number_sql('10') FROM dual;
 *
 * str.IS_NUMBER_SQL('10')
 * --------------------------------------------------------------------------------
 * TRUE
 * </PRE>
 * @param                          in_value Value that is evaluated to see if it is a number
 * @return Returns a VARCHAR2 value of 'TRUE' if Chacter value passed is considered a number, else 'FALSE' is returned.
 */
  FUNCTION is_number_sql
  (in_value   IN   VARCHAR2)
  RETURN VARCHAR2;
 /**
 * Function will return a boolean TRUE if the string passed is an ordinal (1st, 2cd, 3rd etc.).
 * @param in_value                 String to be evaluated
 * @return Returns a boolean TRUE if string passed is an ordinal, else it returns a FALSE
 */
  FUNCTION is_ordinal
  (in_value   IN   VARCHAR2)
  RETURN BOOLEAN;
 /**
 * Function returns like is_ordinal but instead returns a VARCHAR2 value of 'TRUE' if the
 * string passed is an ordinal or returns a value of 'FALSE' if it is not.
 * @param in_value                 Value that is evaluated to see if it is an ordinal
 * @return Returns a VARCHAR2 value of 'TRUE' if Chacter value passed is considered an ordianal, else 'FALSE' is returned.
 */
  FUNCTION is_ordinal_sql
  (in_value   IN   VARCHAR2)
  RETURN VARCHAR2;
 /**
 * Function returns a boolean TRUE if the string passes is a roman numeral.
 * <PRE>
 * For Example...
 * <PRE>
 * BEGIN
 *   IF str.is_roman_numeral('XXsII') THEN
 *     Dbms_Output.Put_Line('true');
 *   ELSE
 *     Dbms_Output.Put_Line('false');
 *   END IF;
 * END;
 * </PRE>
 * @param in_value                 String to be evaluated
 * @return Returns a boolean TRUE if string passed is a roman numeral, else it returns a FALSE
 */
  FUNCTION is_roman_numeral
  (in_value   IN   VARCHAR2)
  RETURN BOOLEAN;
 /**
 * Function returns like is_roman_numeral but instead returns a VARCHAR2 value of 'TRUE' if the
 * string passed is a roman numeral or returns a value of 'FALSE' if it is not.
 * For Example...
 * <PRE>
 * SQL> SELECT str.is_roman_numeral_sql('XXII') FROM DUAL;
 *
 * STR.IS_ROMAN_NUMERAL_SQL('XXII')
 * --------------------------------------------------------------------------------
 * TRUE
 *
 * SQL>
 * </PRE>
 * @param in_value                 Value that is evaluated to see if it is an ordinal
 * @return Returns a VARCHAR2 value of 'TRUE' if Chacter value passed is considered an ordianal, else 'FALSE' is returned.
 */
  FUNCTION is_roman_numeral_sql
  (in_value   IN   VARCHAR2)
  RETURN VARCHAR2;
 /**
 * Function will return a boolean TRUE if a string passed to the function is
 * an exceptable date.  Here is an example...
 * <PRE>
 * BEGIN
 *   If str.is_date('5-dec-2004') Then
 *     DBMS_OUTPUT.PUT_LINE('True');
 *   Else
 *     DBMS_OUTPUT.PUT_LINE('False');
 *   End If;
 * END;
 * </PRE>
 * @param                          in_value Value that is evaluated to if it is a date
 * @return Returns a BOOLEAN True if Chacter value passed is considered a date, else return a BOOLEAN False.
 */
  FUNCTION is_date
  (in_value   IN   VARCHAR2)
  RETURN BOOLEAN;
 /**
 * Function evaluates a date in the same way as is_date but returns a VARCHAR2
 * value of 'TRUE' if the string is a date or a value of 'FALSE' if it is not.
 * For Example....
 * <PRE>
 * SQL> select str.is_date_sql('10-dec-2004') from dual;
 *
 * STR.IS_DATE_SQL('10-DEC-2004')
 * --------------------------------------------------------------------------------
 * TRUE
 * </PRE>
 * @param                          in_value Value that is evaluated to if it is a date
 * @return Returns a VARCHAR2 value of 'TRUE' if Chacter value passed is considered a date, else 'FALSE' is returned.
 */
  FUNCTION is_date_sql
  (in_value   IN   VARCHAR2)
  RETURN VARCHAR2;
 /**
 * Function will return the last date format used to evaluate a date with the
 * is_date function and the is_date_sql function.  For example...
 * <PRE>
 * SQL> select str.is_date_sql('10-dec-2004') from dual;
 *
 * STR.IS_DATE_SQL('10-DEC-2004')
 * --------------------------------------------------------------------------------
 * TRUE
 *
 * SQL> select str.is_date_format from dual;
 *
 * IS_DATE_FORMAT
 * --------------------------------------------------------------------------------
 * DDMMYYYY
 *
 * SQL>
 * </PRE>
 * @return Returns the date format that evalueted the IS_DATE call.
 */
  FUNCTION is_date_format
  RETURN VARCHAR2;
 /**
 * Function works similiar to UPPER and LOWER builtins except that it upper cases
 * Only the first letter of each word and lower cases the rest of the word.
 * Similiar to what MS Word would do if you changed the case of a sentance to Title.
 * For Example...
 * <PRE>
 * SQL> SELECT str.title('joe garrepy') FROM dual;
 *
 * str.TITLE('JOEGARREPY')
 * --------------------------------------------------------------------------------
 * Joe Garrepy
 *
 * SQL>
 * </PRE>
 * @param  in_value                in_value is the string to be formated
 * @return Returns a VARCHAR2 value with all character string delimeted by spaces
 *         displaying an upper case character as the first character of each filed
 *         and the rest of the characters in each field displaying lower case.
 */
  FUNCTION title
  (in_value   IN   VARCHAR2)
  RETURN VARCHAR2;
 /**
 * Function will Toggle the case of string, so what was upper is no lower and
 * vice versa.  For Example...
 * <PRE>
 * SQL> SELECT str.toggle('Toggle Case') FROM dual;
 *
 * str.TOGGLE('TOGGLECASE')
 * --------------------------------------------------------------------------------
 * tOGGLE cASE
 *
 * SQL>
 *
 * </PRE>
 * @param  in_value                in_value is the string to be formated
 * @return Returns the passed string with the Case reversed.
 */
  FUNCTION toggle
  (in_value   IN   VARCHAR2)
  RETURN VARCHAR2;
 /**
 * Function will remove double space from a string and replace them with a single space.
 * The default remove is a space, but by changing the in_char parameter this will also remove
 * other types of double characters
 * For Example...
 * <PRE>
 * SQL>  select str.remove_dbl_space('Test  of  double  space  .') from dual;
 *
 * str.REMOVE_DBL_SPACE('TESTOFDOUBLESPACE.')
 * --------------------------------------------------------------------------------
 * Test of double space .
 *
 * SQL>
 * </PRE>
 * @param in_value                 Value that will have all double space removed from it.
 * @param in_char                  Character that you want to replace double occurances out
 * @return Returns in_value with sigle spaces replacing all double spaces.
 */
  FUNCTION remove_dbl_space
  (in_value   IN   VARCHAR2,
   in_char    IN   VARCHAR2 DEFAULT ' ')
  RETURN VARCHAR2;
 --
END str;
/
CREATE OR REPLACE
PACKAGE BODY str
IS
 -- ******************************************************************************** --
 -- Private Package Functions, Procedures, Cursors and Variables
 -- ******************************************************************************** --
  globDateFormat     VARCHAR2(100) := NULL;
  globLastDateValue  VARCHAR2(32767) := NULL;
  TYPE tab_str_strip IS TABLE OF VARCHAR2(1);
  t_str_strip tab_str_strip := tab_str_strip('-','/','\',' ');
  TYPE tab_century IS TABLE OF VARCHAR2(3);
  t_centruy tab_century := tab_century('CC',    --> Century
                                       'SCC');  --> Century BC prefixed with -
  TYPE tab_year IS TABLE OF VARCHAR2(5);
  t_year tab_year := tab_year('YYYY',  --> Year 2001
                              'SYYY',  --> Year BC prefixed with -
                              'IYYY',  --> ISO Year 2001
                              'YY',    --> Year 01
                              'RR',    --> Year 01 rollover for Y2K compatibility *
                              'YEAR',  --> Year spelled out
                              'SYEAR', --> Year spelled out BC prefixed with -
                              'BC');   --> BC/AD Indicator *
  TYPE tab_quarter IS TABLE OF VARCHAR2(1);
  t_quarter tab_quarter := tab_quarter('Q'); --> Quarter : Jan-Mar=1, Apr-Jun=2
  TYPE tab_month IS TABLE OF VARCHAR2(7);
  t_month tab_month := tab_month('MM',       --> Month of year 01, 02...12
                                 'RM',       --> Roman Month I, II...XII *
                                 'MONTH',    --> In full [January  ]...[December ]
                                 'FMMONTH',  --> In full [January]...[December]
                                 'MON');     --> JAN, FEB
  TYPE tab_week IS TABLE OF VARCHAR2(2);
  t_week tab_week := tab_week('WW',  --> Week of year 1-52
                              'W',   --> Week of month 1-5
                              'IW'); --> ISO std week of year

  TYPE tab_day IS TABLE OF VARCHAR2(6);
  t_day tab_day := tab_day('DDD',    --> Day of year 1-366 *
                           'DD',     --> Day of month 1-31
                           'D',      --> Day of week 1-7
                           'DAY',    --> In full [Monday   ]...[Sunday   ]
                           'FMDAY',  --> In full [Monday]...[Sunday]
                           'DY',     --> MON...SUN
                           'DDTH',   --> Ordinal Day 7TH
                           'DDSPTH', --> Spell out ordinal SEVENTH
                           'J');     --> Julian Day (days since 31/12/4713)
  TYPE tab_title_lower IS TABLE OF VARCHAR2(5);
  t_title_lower tab_title_lower := tab_title_lower('OF','DE','DES','LA','LAS',
                                                   'LE','LES','DA','AND','IN','ON',
                                                   'BY','THE','FOR');
 /**
 * used for function is_date
 */
  FUNCTION is_date_local
  (in_value   IN  VARCHAR2,
   in_format  IN  VARCHAR2)
  RETURN BOOLEAN IS
    retDate   DATE;
  BEGIN
    retDate := TO_DATE(in_value,in_format);
    RETURN TRUE;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN FALSE;
  END is_date_local;
 /**
 * Used for processing internal casing in funciton TITLE
 */
  FUNCTION f_internal_title
  (in_value   IN   VARCHAR2,
   in_split   IN   VARCHAR2 DEFAULT '-')
  RETURN VARCHAR2 IS
    k    INTEGER;
    retSplit   VARCHAR2(32767);
    varEval  VARCHAR2(32767);
    t_col_split      str.tab_split;
    boolRtrim        BOOLEAN;
  BEGIN
   --> Check to see if split item needs to be added back to the end of the string
    If SUBSTR(in_value,LENGTH(in_value),1) = in_split Then
      boolRtrim := TRUE;
    Else
      boolRtrim := FALSE;
    End If;
   --> set up array to be split
    t_col_split := str.split_array('**' || in_value || '**',in_split);
   --
    FOR k IN t_col_split.FIRST .. t_col_split.LAST LOOP
     --
      varEval := UPPER(SUBSTR(t_col_split(k),1,1)) || SUBSTR(t_col_split(k),2,Length(t_col_split(k)));     --
      If retSplit IS NULL Then
        retSplit := varEval;
      Else
        retSplit := retSplit || in_split || varEval;
      End If;
     --
    END LOOP;
   --
    If boolRtrim Then
      retSplit := retSplit || in_split;
    End If;
   --
    If boolRtrim Then
      RETURN SUBSTR(retSplit,3,(LENGTH(retSplit)-5));
    Else
      RETURN SUBSTR(retSplit,3,(LENGTH(retSplit)-4));
    End If;
   --
  END f_internal_title;
 -- ******************************************************************************** --
 -- Public Package Functions, Procedures, Cursors and Variabels
 -- ******************************************************************************** --
  FUNCTION split
  (in_del_field  IN     VARCHAR2,
   in_pos        IN     NUMBER,
   in_del        IN     VARCHAR2)
  RETURN VARCHAR2 IS
    retVal       VARCHAR2(4000) := NULL;
    varFldDel    VARCHAR2(2000) := LTRIM(RTRIM(in_del_field)) || in_del;
  BEGIN
   --
    If in_pos = 1 Then
     --
      retVal := SUBSTR(varFldDel,1,(INSTR(varFldDel,in_del,1,in_pos)-1));
     --
    ElsIf in_pos > 1 Then
     --
      retVal := SUBSTR(varFldDel,(INSTR(varFldDel,in_del,1,(in_pos-1))+1),
                                 (INSTR(varFldDel,in_del,1,in_pos)-
                                 (INSTR(varFldDel,in_del,1,(in_pos-1))+1)));
     --
    End If;
   --
    RETURN retVal;
   --
  END split;
 -- ******************************************************************************** --
  FUNCTION split_array
  (in_del_field  IN     VARCHAR2,
   in_del        IN     VARCHAR2)
  RETURN tab_split IS
    t_return   tab_split := tab_split(NULL);
    i          INTEGER := 1;
  BEGIN
   --
    LOOP
     --
      EXIT WHEN str.split(in_del_field,i,in_del) IS NULL;
     --
      If i > 1 Then
        t_return.EXTEND;
      End If;
     --
      t_return(i) := str.split(in_del_field,i,in_del);
      i := i + 1;
     --
    END LOOP;
   --
    RETURN t_return;
   --
  END split_array;
 -- ******************************************************************************** --
  FUNCTION split_pipe
  (in_del_field  IN     VARCHAR2,
   in_del        IN     VARCHAR2)
  RETURN tab_str PIPELINED IS
    t_split   str.tab_split := str.split_array(in_del_field,in_del);
    i         INTEGER;
    out_row   rec_str;
  BEGIN
   --
    FOR i IN t_split.FIRST .. t_split.LAST LOOP
      out_row.STRING_VALUE := t_split(i);
      PIPE ROW (out_row);
    END LOOP;
   --
    RETURN;
   --
  END split_pipe;
 -- ******************************************************************************** --
  FUNCTION is_upper_case
  (in_string   IN   VARCHAR2)
  RETURN BOOLEAN IS
    i          INTEGER;
    j          INTEGER := 0;
    varSearch  VARCHAR2(32767) := REPLACE(in_string,' ','');
    retBool    BOOLEAN := FALSE;
    intAscii   INTEGER;
  BEGIN
    If varSearch IS NOT NULL Then
      FOR i IN 1 .. LENGTH(varSearch) LOOP
        intAscii := ASCII(SUBSTR(in_string,i,1));
        If intAscii > 64   AND
           intAscii < 123  AND
           intAscii NOT IN (91,92,93,94,95,96)
        Then
          If intAscii <= 90 AND
             intAscii >= 65
          Then
            retBool := TRUE;
          Else
            j := j + 1;
          End If;
        End If;
      END LOOP;
      If j > 0 Then
        retBool := FALSE;
      End If;
    End If;
    RETURN retBool;
  END is_upper_case;
 -- ******************************************************************************** --
  FUNCTION is_lower_case
  (in_string   IN   VARCHAR2)
  RETURN BOOLEAN IS
    i          INTEGER;
    j          INTEGER := 0;
    varSearch  VARCHAR2(32767) := REPLACE(in_string,' ','');
    retBool    BOOLEAN := FALSE;
    intAscii   INTEGER;
  BEGIN
    If varSearch IS NOT NULL Then
      FOR i IN 1 .. LENGTH(varSearch) LOOP
        intAscii := ASCII(SUBSTR(in_string,i,1));
        If intAscii > 64   AND
           intAscii < 123  AND
           intAscii NOT IN (91,92,93,94,95,96)
        Then
          If intAscii <= 122 AND
             intAscii >= 97
          Then
            retBool := TRUE;
          Else
            j := j + 1;
          End If;
        End If;
      END LOOP;
      If j > 0 Then
        retBool := FALSE;
      End If;
    End If;
    RETURN retBool;
  END is_lower_case;
 -- ******************************************************************************** --
  FUNCTION is_mixed_case
  (in_string   IN   VARCHAR2)
  RETURN BOOLEAN
  IS
  BEGIN
    If NOT is_upper_case(in_string) AND
       NOT is_lower_case(in_string)
    Then
      RETURN TRUE;
    Else
      RETURN FALSE;
    End If;
  END is_mixed_case;
 -- ******************************************************************************** --
  FUNCTION is_number
  (in_value   IN   VARCHAR2)
  RETURN BOOLEAN IS
    retNum   NUMBER(10);
  BEGIN
    retNum := TO_NUMBER(in_value);
    RETURN TRUE;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN FALSE;
  END is_number;
 -- ******************************************************************************** --
  FUNCTION is_number_sql
  (in_value   IN   VARCHAR2)
  RETURN VARCHAR2 IS
    retVal   VARCHAR2(5) := 'FALSE';
  BEGIN
    If is_number(in_value) Then
      retVal := 'TRUE';
    End If;
    RETURN retVal;
  END is_number_sql;
 -- ******************************************************************************** --
  FUNCTION is_ordinal
  (in_value   IN   VARCHAR2)
  RETURN BOOLEAN IS
    i            INTEGER;
    boolNumber   BOOLEAN := FALSE;
    boolOrdinal  BOOLEAN := FALSE;
  BEGIN
   --
    boolOrdinal := FALSE;
   --> determine if ordinal
    FOR i IN 1 .. LENGTH(in_value) LOOP
     --
      If str.is_number(SUBSTR(in_value,i,1)) Then
        boolOrdinal := FALSE;
        boolNumber := TRUE;
      Else
        If boolNumber Then
          If UPPER(SUBSTR(in_value,i,LENGTH(in_value))) IN ('ST','ND','RD','TH') Then
            boolOrdinal := TRUE;
          End If;
        End If;
        boolNumber := FALSE;
      End If;
     --
    END LOOP;
   --
    RETURN boolOrdinal;
   --
  END is_ordinal;
 -- ******************************************************************************** --
  FUNCTION is_ordinal_sql
  (in_value   IN   VARCHAR2)
  RETURN VARCHAR2 IS
  BEGIN
   If is_ordinal(in_value) Then
     RETURN 'TRUE';
   Else
     RETURN 'FALSE';
   End If;
  END is_ordinal_sql;
 -- ******************************************************************************** --
  FUNCTION is_roman_numeral
  (in_value   IN   VARCHAR2)
  RETURN BOOLEAN IS
    intV    INTEGER := 0;
    intX    INTEGER := 0;
    intI    INTEGER := 0;
    i       INTEGER;
  BEGIN
   --
    FOR i IN 1 .. LENGTH(in_value) LOOP
     --
      CASE SUBSTR(UPPER(in_value),i,1)
        WHEN 'X' Then
          intX := intX + 1;
        WHEN 'I' Then
          intI := intI + 1;
        WHEN 'V' Then
          intV := intV + 1;
        ELSE NULL;
      END CASE;
     --
    END LOOP;
   --
    If LENGTH(in_value) = intX + intI + intV Then
      RETURN TRUE;
    Else
      RETURN FALSE;
    End If;
   --
  END is_roman_numeral;
 -- ******************************************************************************** --
  FUNCTION is_roman_numeral_sql
  (in_value   IN   VARCHAR2)
  RETURN VARCHAR2 IS
  BEGIN
    If is_roman_numeral(in_value) Then
      RETURN 'TRUE';
    Else
      RETURN 'FALSE';
    End If;
  END is_roman_numeral_sql;
 -- ******************************************************************************** --
  FUNCTION is_date
  (in_value   IN   VARCHAR2)
  RETURN BOOLEAN IS
    d         INTEGER;
    m         INTEGER;
    y         INTEGER;
    varDate   VARCHAR2(32767) := UPPER(REPLACE(REPLACE(REPLACE(REPLACE(in_value,'-',''),'/',''),'\',''),' ',''));
  BEGIN
   --> RESET DATE FORMAT
    globDateFormat := NULL;
    globLastDateValue := in_value;
   --
    FOR d IN t_day.FIRST .. t_day.LAST LOOP
     --> Check Day
      If is_date_local(varDate,t_day(d)) Then
        globDateFormat := t_day(d);
        RETURN TRUE;
      Else --> Check Day Month/Month Day
        FOR m IN t_month.FIRST .. t_month.LAST LOOP
         --
          If is_date_local(varDate,t_day(d) || t_month(m)) Then
            globDateFormat := t_day(d) || t_month(m);
            RETURN TRUE;
          End If;
         --
          If is_date_local(varDate,t_month(m) || t_day(d)) Then
            globDateFormat := t_month(m) || t_day(d);
            RETURN TRUE;
          End If;
         --> Check Year
          FOR y IN t_year.FIRST .. t_year.LAST LOOP
           --> check DAY MONTH YEAR
            If is_date_local(varDate,t_day(d) || t_month(m) || t_year(y)) Then
              globDateFormat := t_day(d) || t_month(m) || t_year(y);
              RETURN TRUE;
            End If;
           --> check DAY YEAR MONTH
            If is_date_local(varDate,t_day(d) || t_year(y) || t_month(m)) Then
              globDateFormat := t_day(d) || t_year(y) || t_month(m);
              RETURN TRUE;
            End If;
           --> check MONTH YEAR DAY
            If is_date_local(varDate,t_month(m) || t_year(y) || t_day(d)) Then
              globDateFormat := t_month(m) || t_year(y) || t_day(d);
              RETURN TRUE;
            End If;
           --> check MONTH DAY YEAR
            If is_date_local(varDate,t_month(m) || t_day(d) || t_year(y)) Then
              globDateFormat := t_month(m) || t_day(d) || t_year(y);
              RETURN TRUE;
            End If;
           --> check YEAR DAY MONTH
            If is_date_local(varDate,t_year(y) || t_day(d) || t_month(m)) Then
              globDateFormat := t_year(y) || t_day(d) || t_month(m);
              RETURN TRUE;
            End If;
           --> check YEAR MONTH DAY
            If is_date_local(varDate,t_year(y) || t_month(m) || t_day(d)) Then
              globDateFormat := t_year(y) || t_month(m) || t_day(d);
              RETURN TRUE;
            End If;
           --
          END LOOP;
          y := NULL;
         --
        END LOOP;
        m := NULL;
      End If;
     --
    END LOOP;
   --
    RETURN FALSE;
   --
  END is_date;
 -- ******************************************************************************** --
  FUNCTION is_date_sql
  (in_value   IN   VARCHAR2)
  RETURN VARCHAR2 IS
    retVal  VARCHAR2(5) := 'FALSE';
  BEGIN
   If is_date(in_value) Then
     retVal := 'TRUE';
   End If;
   RETURN retVal;
  END is_date_sql;
 -- ******************************************************************************** --
  FUNCTION is_date_format
  RETURN VARCHAR2 IS
    retVal    VARCHAR2(32767) := globDateFormat;
    s         INTEGER;
    i         INTEGER;
    intInstr  INTEGER;
  BEGIN
   --> find where dashes and special characters should be and put them back...
    FOR s IN t_str_strip.FIRST .. t_str_strip.LAST LOOP
      i := 1;
      LOOP
        intInstr := INSTR(globLastDateValue,t_str_strip(s),i);
        EXIT WHEN intInstr < 1;
        retVal := SUBSTR(retVal,1,intInstr) || t_str_strip(s) || SUBSTR(retVal,intInstr,LENGTH(retVal));
        i := i + 1;
      END LOOP;
    END LOOP;
   --
    RETURN globDateFormat;
   --
  END is_date_format;
 -- ******************************************************************************** --
  FUNCTION title
  (in_value   IN   VARCHAR2)
  RETURN VARCHAR2 IS
    i            INTEGER;
    j            INTEGER;
    s            VARCHAR2(1) := ' ';
    t_split      str.tab_split := str.split_array(in_value,s);
    varEval      VARCHAR2(32767);
    retVal       VARCHAR2(32767);
    boolLower    BOOLEAN := FALSE;
  BEGIN
   --
    If in_value IS NOT NULL Then
      FOR i IN t_split.FIRST .. t_split.LAST LOOP
       --> check if word should always be lower case
        If i > 1 Then
          j := NULL;
          FOR j IN t_title_lower.FIRST .. t_title_lower.LAST LOOP
           --
            If UPPER(t_split(i)) = UPPER(t_title_lower(j)) Then
              boolLower := TRUE;
              varEval := LOWER(t_split(i));
            End If;
           --
          END LOOP;
        End If;
       --
        If NOT boolLower Then
          varEval := UPPER(SUBSTR(t_split(i),1,1))
                  || LOWER(SUBSTR(t_split(i),2,LENGTH(t_split(i))));
        End If;
       --> look MAC AND MC in names and Ucase correct characters
        If UPPER(SUBSTR(t_split(i),1,3)) = 'MAC' Then
          varEval := UPPER(SUBSTR(t_split(i),1,1))
                  || LOWER (SUBSTR(t_split(i),2,2))
                  || UPPER(SUBSTR(t_split(i),4,1))
                  || LOWER(SUBSTR(t_split(i),5,LENGTH(t_split(i))));
        ElsIf UPPER(SUBSTR(t_split(i),1,2)) = 'MC' Then
          varEval := UPPER(SUBSTR(t_split(i),1,1))
                  || LOWER(SUBSTR(t_split(i),2,1))
                  || UPPER(SUBSTR(t_split(i),3,1))
                  || LOWER(SUBSTR(t_split(i),4,LENGTH(t_split(i))));
        End If;
       --> for each value in array, split with a - and upper anything after the -
        varEval := f_internal_title(varEval,'-');
       --> for each value in array, split with a / and upper anything after the /
        varEval := f_internal_title(varEval,'/');
       --> for each value in array, split with a \ and upper anything after the \
        varEval := f_internal_title(varEval,'\');
       --> for each value in array, split with a . and upper anything after the .
        varEval := f_internal_title(varEval,'.');
       --> for each value in array, split with a , and upper anything after the ,
        varEval := f_internal_title(varEval,',');
       --> for each value in array, split with a ( and upper anything after the (
        varEval := f_internal_title(varEval,'(');
       --> for each value in array, split with a " and upper anything after the "
        varEval := f_internal_title(varEval,'"');
       --> for each value in array, split with a , and upper anything after the '
        If SUBSTR(varEval,LENGTH(varEval)-1,1) <> '''' Then
          varEval := f_internal_title(varEval,'''');
        End If;
       --
        If i > 1 Then
          retVal := retVal || s || varEval;
        Else
          retVal := varEval;
        End If;
        boolLower := FALSE;
      END LOOP;
    End If;
   --
    RETURN retVal;
   --
  END title;
 -- ******************************************************************************** --
  FUNCTION toggle
  (in_value   IN   VARCHAR2)
  RETURN VARCHAR2 IS
    i        INTEGER;
    varEval  VARCHAR2(1);
    retVal   VARCHAR2(32767);
  BEGIN
   --
    FOR i IN 1 .. LENGTH(in_value) LOOP
     --> get current character to evaluate
      varEval := SUBSTR(in_value,i,1);
     --> Evaluation character
      If is_upper_case(varEval) Then
        retVal := retVal || LOWER(varEval);
      ElsIf is_lower_case(varEval) Then
        retVal := retVal || UPPER(varEval);
      Else
        retVal := retVal || varEval;
      End If;
     --
    END LOOP;
   --
    RETURN retVal;
   --
  END toggle;
 -- ******************************************************************************** --
  FUNCTION remove_dbl_space
  (in_value   IN   VARCHAR2,
   in_char    IN   VARCHAR2 DEFAULT ' ')
  RETURN VARCHAR2 IS
    s        VARCHAR2(32767) := in_char;
    ss       VARCHAR2(32767) := in_char || in_char;
    i        INTEGER := 1;
    retVal   VARCHAR2(32767) := in_value;
  BEGIN
   --
    If in_value IS NOT NULL Then
     --
      LOOP
       --
        EXIT WHEN INSTR(retVal,ss) < 1;
       --
        retVal := REPLACE(retVal,ss,s);
       --
        i := i + 1;
       --
      END LOOP;
     --
    End If;
   --
    RETURN retVal;
   --
  END remove_dbl_space;
 -- ******************************************************************************** --
END str;
/

 --> Grant(s)

GRANT EXECUTE ON str TO PUBLIC;

 --> Synonym(s)

CREATE PUBLIC SYNONYM str FOR str;
If you find any bugs or want to contact me I can be reached @ doughjoe77@hotmail.com.