Oracle Function to convert number to words

Oracle Function to convert number to words

Often you get the requirement to convert amount to words. This requirement mainly comes in cheque printing.

Give amount as parameter
Example
SQL> SELECT AMOUNT_TO_WORDS(155221.25) FROM DUAL;

AMOUNT_TO_WORDS(155221.25)
-------------------------------------------------------------------------------
One lakh fifty-five thousand two hundred twenty-one rupees and twenty-five paise only

CREATE OR REPLACE FUNCTION AMOUNT_TO_WORDS (P_AMT  IN NUMBER)                                       

RETURN VARCHAR2 IS

    M_MAIN_AMT_TEXT      VARCHAR2(2000) ;

    M_TOP_AMT_TEXT       VARCHAR2(2000) ;

    M_BOTTOM_AMT_TEXT    VARCHAR2(2000) ;

    M_DECIMAL_TEXT       VARCHAR2(2000) ;

    M_TOP                NUMBER(20,5) ;

    M_MAIN_AMT           NUMBER(20,5) ;

    M_TOP_AMT            NUMBER(20,5) ;

    M_BOTTOM_AMT         NUMBER(20,5) ;

    M_DECIMAL            NUMBER(20,5) ;

    M_AMT                NUMBER(20,5);

    M_TEXT               VARCHAR2(2000) ;

    BEGIN

       M_MAIN_AMT        := NULL ;

       M_TOP_AMT_TEXT    := NULL ;

       M_BOTTOM_AMT_TEXT := NULL ;

       M_DECIMAL_TEXT    := NULL ;

      

       -- To get paise part

       M_DECIMAL    := P_AMT - TRUNC(P_AMT) ;

      

       IF M_DECIMAL >0 THEN

       M_DECIMAL := M_DECIMAL *100;

       END IF;

      

       M_AMT        := TRUNC(P_AMT) ;         

    

    

       M_TOP        := TRUNC(M_AMT / 100000) ;

       M_MAIN_AMT   := TRUNC(M_TOP / 100);

       M_TOP_AMT    := M_TOP - M_MAIN_AMT * 100 ;

       M_BOTTOM_AMT :=  M_AMT - (M_TOP * 100000) ;

    

      IF M_MAIN_AMT > 0 THEN

          M_MAIN_AMT_TEXT := TO_CHAR(TO_DATE(M_MAIN_AMT,'J'),'JSP') ;

          IF M_MAIN_AMT = 1 THEN

            M_MAIN_AMT_TEXT := M_MAIN_AMT_TEXT || ' CRORE ' ;

          ELSE

            M_MAIN_AMT_TEXT := M_MAIN_AMT_TEXT || ' CRORES ' ;

          END IF ;

       END IF ;

    

       IF M_TOP_AMT > 0 THEN

          M_TOP_AMT_TEXT := TO_CHAR(TO_DATE(M_TOP_AMT,'J'),'JSP') ;

          IF M_TOP_AMT = 1 THEN

            M_TOP_AMT_TEXT := M_TOP_AMT_TEXT || ' LAKH ' ;

          ELSE

            M_TOP_AMT_TEXT := M_TOP_AMT_TEXT || ' LAKHS ' ;

          END IF;

       END IF ;

       IF M_BOTTOM_AMT > 0 THEN

          M_BOTTOM_AMT_TEXT := TO_CHAR(TO_DATE(M_BOTTOM_AMT,'J'),'JSP') ;

       END IF ;

       IF M_DECIMAL > 0 THEN

          IF NVL(M_BOTTOM_AMT,0) + NVL(M_TOP_AMT,0) > 0 THEN

             M_DECIMAL_TEXT := ' AND ' || TO_CHAR(TO_DATE(M_DECIMAL,'J'),'JSP') || ' Paise ' ;

          ELSE

             M_DECIMAL_TEXT :=  TO_CHAR(TO_DATE(M_DECIMAL,'J'),'JSP') ||' Paise ';

          END IF ;

            END IF ;

       M_TEXT := LOWER(M_MAIN_AMT_TEXT || M_TOP_AMT_TEXT || M_BOTTOM_AMT_TEXT || ' Rupees' || M_DECIMAL_TEXT || ' ONLY') ;

       M_TEXT := UPPER(SUBSTR(M_TEXT,1,1))|| SUBSTR(M_TEXT,2);

       M_TEXT := ' '|| M_TEXT;

       RETURN(M_TEXT);

    

    END AMOUNT_TO_WORDS;

    /