Age calculation with function in oracle

/*Use the function  to calculate age between two date*/

CREATE OR REPLACE FUNCTION f_calculate_age (p_birth_date IN DATE,p_compare_date in date)
   RETURN VARCHAR
IS
   p_year    NUMBER;
   p_month   NUMBER;
   p_day     NUMBER;
   p_age     VARCHAR2 (200);
/******************************************************************************
   NAME:       f_calculate_age
   PURPOSE:    Calculate age
******************************************************************************/
BEGIN
   SELECT ABS(TRUNC (MONTHS_BETWEEN (p_compare_date, dob) / 12)) YEAR,
          ABS(TRUNC (MOD (MONTHS_BETWEEN (p_compare_date, dob), 12))) MONTH,
          ABS(TRUNC (  p_compare_date
                 - ADD_MONTHS (dob,
                                 TRUNC (MONTHS_BETWEEN (p_compare_date, dob) / 12)
                                 * 12
                               + TRUNC (MOD (MONTHS_BETWEEN (p_compare_date, dob),
                                             12)
                                       )
                              )
                )) DAY
     INTO p_year,
          p_month,
          p_day
     FROM (SELECT TO_DATE (TO_CHAR (p_birth_date, 'DDMMRRRR'),
                           'DDMMRRRR') dob
             FROM DUAL);

   SELECT    p_year
          || DECODE (abs(p_year), 1, 'Year ', 0, 'Year ', ' Years ')
          || p_month
          || DECODE (abs(p_month), 1, ' Month ', 0, ' Month ', ' Months ')
          || p_day
          || DECODE (abs(p_day), 1, 'Day.', 0, ' Day.', ' Days.')
     INTO p_age
     FROM DUAL;

   RETURN p_age;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      NULL;
   WHEN OTHERS
   THEN
      -- Consider logging the error and then re-raise
      RAISE;
END f_calculate_age;
/

Number conversion to word in Bangla text in Oracle

/*Use the function to convert number to bangla text*/
CREATE OR REPLACE FUNCTION VALUE_IN_bangla(a_number NUMBER) RETURN CHAR IS
    TYPE numtab IS TABLE OF VARCHAR2(30)
    INDEX BY BINARY_INTEGER;
    number_chart numtab;
    num_value number;
    word_value varchar2(500);
    crore number;
    lac number;
    thou number;
    hund number;
    doubl number;
    sing number;
    deci number;
BEGIN
   --The number_chart Table

number_chart(1):= 'GK';
number_chart(2):= '`yB';
number_chart(3):= 'wZb';
number_chart(4):= 'Pvi';
number_chart(5):= 'cuvP';
number_chart(6):= 'Qq';
number_chart(7):= 'mvZ';
number_chart(8):= 'AvU';
number_chart(9):= 'bq';
number_chart(10):= '`k';
number_chart(11):= 'GMvi';
number_chart(12):= 'evi';
number_chart(13):= '‡Zi';
number_chart(14):= '‡PŠÏ';
number_chart(15):= 'c‡bi';
number_chart(16):= '‡lvj';
number_chart(17):= 'm‡Zi';
number_chart(18):= 'AvVv‡iv';
number_chart(19):= 'Ewbk';
number_chart(20):= 'wek';
number_chart(21):= 'GKzk';
number_chart(22):= 'evBk';
number_chart(23):= '‡ZBk';
number_chart(24):= 'PweŸk';
number_chart(25):= 'cuwPk';
number_chart(26):= 'QvweŸk';
number_chart(27):= 'mvZvk';
number_chart(28):= 'AvUvk';
number_chart(29):= 'EbwÎk';
number_chart(30):= 'wÎk';
number_chart(31):= 'GKwÎk';
number_chart(32):= 'GKwÎk';
number_chart(33):= '‡ZwÎk';
number_chart(34):= '‡PŠwÎk';
number_chart(35):= 'cuqwÎk';
number_chart(36):= 'QwÎk';
number_chart(37):= 'QwÎk';
number_chart(38):= 'AvUwÎk';
number_chart(39):= 'EbPwjøk';
number_chart(40):= 'Pwjøk';
number_chart(41):= 'GKPwjøk';
number_chart(42):= 'weqvwjøk';
number_chart(43):= '‡ZZvwjøk';
number_chart(44):= 'Pzqvwjøk    ';
number_chart(45):= 'cuqZvwjøk';
number_chart(46):= '†QPwjøk';
number_chart(47):= 'mvZPwjøk';
number_chart(48):= 'AvUPwjøk';
number_chart(49):= ' EbcÂvk';
number_chart(50):= 'cÂvk';
number_chart(51):= 'GKvbœ';
number_chart(52):= 'evqvbœ';
number_chart(53):= '‡Zàvbœ';
number_chart(54):= 'Pzqvbœ';
number_chart(55):= 'cÂvb';
number_chart(56):= 'Qv&àvbœ';
number_chart(57):= 'mvZvbœ';
number_chart(58):= 'AvUvbœ';
number_chart(59):= 'EblvU';
number_chart(60):= 'lvU';
number_chart(61):= 'GKlw&Æ';
number_chart(62):= 'evlw&Æ';
number_chart(63):= '†ZlwÆ';
number_chart(64):= '†PŠlwÆ';
number_chart(65):= 'cuqlwÆ';
number_chart(66):= '†QlwÆ';
number_chart(67):= 'mvZlwÆ';
number_chart(68):= 'AvUlwÆ';
number_chart(69):= 'EbmËi';
number_chart(70):= 'mËi';
number_chart(71):= 'GKvËi';
number_chart(72):= 'evnvËi';
number_chart(73):= 'wZqvËi';
number_chart(74):= 'PzqvËi';
number_chart(75):= 'cuPvËi';
number_chart(76):= 'wQqvËi';
number_chart(77):= 'mvZvËi';
number_chart(78):= 'AvUvËi';
number_chart(79):= 'EbAvwk';
number_chart(80):= 'Avwk';
number_chart(81):= 'GKvwk';
number_chart(82):= 'weivwk';
number_chart(83):= 'wZivwk';
number_chart(84):= 'Pzivwk';
number_chart(85):= 'cuPvwk';
number_chart(86):= 'wQqvwk';
number_chart(87):= 'mvZvwk';
number_chart(88):= 'AvUvwk';
number_chart(89):= 'EbbeŸB';
number_chart(90):= 'beŸB';
number_chart(91):= 'GKvbeŸB';
number_chart(92):= 'weivbeŸB';
number_chart(93):= 'wZivbeŸB';
number_chart(94):= 'PzivbeŸB';
number_chart(95):= 'cuPvbeŸB';
number_chart(96):= 'wQqvbeŸ&&&&&B';
number_chart(97):= 'mvZvbeŸB';
number_chart(98):= 'AvUvbeŸB';
number_chart(99):= 'wbivbeŸB';

    --Converting
    crore:=floor(a_number/10000000);
     lac:=floor((a_number-trunc(a_number,-7))/100000);
     thou:=floor((a_number-trunc(a_number,-5))/1000);
     hund:=floor((a_number-trunc(a_number,-3))/100);
     doubl:=trunc(a_number-trunc(a_number,-2),0);
--     sing:=trunc(a_number-trunc(a_number,-1),0);
     deci:=(a_number-trunc(a_number,0))*100;

    if crore<>0 then    --Crore conversion
        if crore between 100 and 999 then
          word_value:=number_chart(floor(crore/100))||' kZ '||number_chart(substr(crore,2))||' '||'†KvwU ';
        else
            word_value:=number_chart(crore)||' '||'†KvwU ';
        end if;
    end if;

    if lac <>0 then    --Lac conversion
    word_value:=word_value||number_chart(lac)||' '||'jÿ ';
    end if;

    if thou <>0 then    --Thousand conversion
         word_value:=word_value||number_chart(thou)||' '||'nvRvi ';
    end if;

    if hund <>0 then    --Hundred conversion
     word_value:=word_value||number_chart(hund)||' '||'kZ ';
    end if;

    if doubl <>0 then    --Double conversion
     word_value:=word_value||number_chart(doubl)||' ';
    end if;

    if deci <>0 then    --Decimal conversion
      word_value:=word_value||' '||number_chart(deci)||' '||'cqmv ';
    end if;
    return(lower(word_value));
END;
/

Creating password in oracle

/*Use the function to create password with user name*/

CREATE OR REPLACE function f_Password(p_username in varchar2, p_password in varchar2)
return varchar2
is
begin
  return dbms_obfuscation_toolkit.md5
     (input =>
         utl_raw.cast_to_raw(upper(p_username)||'/'||p_password)
     );
end f_Password;
/

Value Conversion from Number to Word

/*Use the function to convert a value from number to word in oracle*/
CREATE OR REPLACE FUNCTION VALUE_IN_WORD(a_number NUMBER) RETURN CHAR IS
  TYPE numtab IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
  number_chart numtab;
  --  num_value number;
  word_value varchar2(500);
  crore      number;
  lac        number;
  thou       number;
  hund       number;
  doubl      number;
  --  sing number;
  deci number;
BEGIN
  --The number_chart Table
  number_chart(1) := 'one';
  number_chart(2) := 'two';
  number_chart(3) := 'three';
  number_chart(4) := 'four';
  number_chart(5) := 'five';
  number_chart(6) := 'six';
  number_chart(7) := 'seven';
  number_chart(8) := 'eight';
  number_chart(9) := 'nine';
  number_chart(10) := 'ten';
  number_chart(11) := 'eleven';
  number_chart(12) := 'twelve';
  number_chart(13) := 'thirteen';
  number_chart(14) := 'fourteen';
  number_chart(15) := 'fifteen';
  number_chart(16) := 'sixteen';
  number_chart(17) := 'seventeen';
  number_chart(18) := 'eighteen';
  number_chart(19) := 'nineteen';
  number_chart(20) := 'twenty';
  number_chart(21) := 'twenty one';
  number_chart(22) := 'twenty two';
  number_chart(23) := 'twenty three';
  number_chart(24) := 'twenty four';
  number_chart(25) := 'twenty five';
  number_chart(26) := 'twenty six';
  number_chart(27) := 'twenty seven';
  number_chart(28) := 'twenty eight';
  number_chart(29) := 'twenty nine';
  number_chart(30) := 'Thirty';
  number_chart(31) := 'Thirty One';
  number_chart(32) := 'Thirty Two';
  number_chart(33) := 'Thirty Three';
  number_chart(34) := 'Thirty Four';
  number_chart(35) := 'Thirty Five';
  number_chart(36) := 'Thirty Six';
  number_chart(37) := 'Thirty Seven';
  number_chart(38) := 'Thirty Eight';
  number_chart(39) := 'Thirty Nine';
  number_chart(40) := 'Forty';
  number_chart(41) := 'Forty One';
  number_chart(42) := 'Forty Two';
  number_chart(43) := 'Forty Three';
  number_chart(44) := 'Forty Four';
  number_chart(45) := 'Forty Five';
  number_chart(46) := 'Forty Six';
  number_chart(47) := 'Forty Seven';
  number_chart(48) := 'Forty Eight';
  number_chart(49) := 'Forty Nine';
  number_chart(50) := 'Fifty';
  number_chart(51) := 'Fifty One';
  number_chart(52) := 'Fifty Two';
  number_chart(53) := 'Fifty Three';
  number_chart(54) := 'Fifty Four';
  number_chart(55) := 'Fifty Five';
  number_chart(56) := 'Fifty Six';
  number_chart(57) := 'Fifty Seven';
  number_chart(58) := 'Fifty Eight';
  number_chart(59) := 'Fifty Nine';
  number_chart(60) := 'Sixty';
  number_chart(61) := 'Sixty One';
  number_chart(62) := 'Sixty Two';
  number_chart(63) := 'Sixty Three';
  number_chart(64) := 'Sixty Four';
  number_chart(65) := 'Sixty Five';
  number_chart(66) := 'Sixty Six';
  number_chart(67) := 'Sixty Seven';
  number_chart(68) := 'Sixty Eight';
  number_chart(69) := 'Sixty Nine';
  number_chart(70) := 'Seventy';
  number_chart(71) := 'Seventy One';
  number_chart(72) := 'Seventy Two';
  number_chart(73) := 'Seventy Three';
  number_chart(74) := 'Seventy Four';
  number_chart(75) := 'Seventy Five';
  number_chart(76) := 'Seventy Six';
  number_chart(77) := 'Seventy Seven';
  number_chart(78) := 'Seventy Eight';
  number_chart(79) := 'Seventy Nine';
  number_chart(80) := 'Eighty';
  number_chart(81) := 'Eighty One';
  number_chart(82) := 'Eighty Two';
  number_chart(83) := 'Eighty Three';
  number_chart(84) := 'Eighty Four';
  number_chart(85) := 'Eighty Five';
  number_chart(86) := 'Eighty Six';
  number_chart(87) := 'Eighty Seven';
  number_chart(88) := 'Eighty Eight';
  number_chart(89) := 'Eighty Nine';
  number_chart(90) := 'Ninety';
  number_chart(91) := 'Ninety One';
  number_chart(92) := 'Ninety Two';
  number_chart(93) := 'Ninety Three';
  number_chart(94) := 'Ninety Four';
  number_chart(95) := 'Ninety Five';
  number_chart(96) := 'Ninety Six';
  number_chart(97) := 'Ninety Seven';
  number_chart(98) := 'Ninety Eight';
  number_chart(99) := 'Ninety Nine';

  --Converting
  crore := floor(a_number / 10000000);
  lac   := floor((a_number - trunc(a_number, -7)) / 100000);
  thou  := floor((a_number - trunc(a_number, -5)) / 1000);
  hund  := floor((a_number - trunc(a_number, -3)) / 100);
  doubl := trunc(a_number - trunc(a_number, -2), 0);
  --  sing:=trunc(a_number-trunc(a_number,-1),0);
  deci := (a_number - trunc(a_number, 0)) * 100;

  if crore <> 0 then
    --Crore conversion
    if crore between 100 and 999 then
      word_value := number_chart(floor(crore / 100)) || ' hundred ' ||
                    number_chart(substr(crore, 2)) || ' ' || 'crore ';
    else
      word_value := number_chart(crore) || ' ' || 'crore ';
    end if;
  end if;

  if lac <> 0 then
    --Lac conversion
    word_value := word_value || number_chart(lac) || ' ' || 'lac ';
  end if;

  if thou <> 0 then
    --Thousand conversion
    word_value := word_value || number_chart(thou) || ' ' || 'thousand ';
  end if;

  if hund <> 0 then
    --Hundred conversion
    word_value := word_value || number_chart(hund) || ' ' || 'hundred ';
  end if;

  if doubl <> 0 then
    --Double conversion
    word_value := word_value || number_chart(doubl) || ' ';
  end if;

  if deci <> 0 then
    --Decimal conversion
    word_value := word_value || 'and ' || number_chart(deci) || ' ' ||
                  'paisa ';
  end if;
  return(lower(word_value));
END;
/

When divisor is equal to 0 (zero)

/*Use this function when divisor is equal to 0 (zero)*/
CREATE OR REPLACE FUNCTION f_valid_devisor (p_number IN NUMBER)
   RETURN NUMBER
IS
   RESULT   NUMBER;
BEGIN
   IF NVL (p_number, 0) = 0
   THEN
      RESULT := 1;
   ELSE
      RESULT := p_number;
   END IF;

   RETURN (RESULT);
END;
/