Get Number In Word Using Oracle Function.

CREATING FUNCTION TO GET NUMBER IN WORD 
=========================================================================
SQL> CREATE OR REPLACE FUNCTION DFU_NUMBER_IN_WORD
  2  (
  3  P_AMOUNT NUMBER
  4  )
  5  RETURN VARCHAR2
  6  /* *****************************************************************************************
  7    * DATABASE FUNCTION UGET AMOUNT IN WORD                                                   *
  8    * USE PARAMETER VALUE FOR                                                                 *
  9    *  PP_AMOUNT =>> AMOUNTT IN NUMBER                                                        *
 10    *               WRITE AS 18/01/2012 BY MOHAMMAD RAJIB PRADHAN                             *
 11    *****************************************************************************************/
 12
 13  IS
 14    V_MAINAMUT VARCHAR2(2000) ;
 15    V_TOPAMOUN VARCHAR2(2000) ;
 16    V_BOTTAMUT VARCHAR2(2000) ;
 17    V_DECIMALT VARCHAR2(2000) ;
 18    V_TOPNUMBR NUMBER(20,5) ;
 19    V_MAIN_AMT NUMBER(20,5) ;
 20    V_TOPANTNU NUMBER(20,5) ;
 21    V_BOTTANNU NUMBER(20,5) ;
 22    V_DECIMALN NUMBER(20,5) ;
 23    V_AMTNUMBR NUMBER(20,5);
 24    V_TEXTRETN VARCHAR2(2000) ;
 25      BEGIN
 26        V_MAIN_AMT := NULL ;
 27        V_TOPAMOUN := NULL ;
 28        V_BOTTAMUT := NULL ;
 29        V_DECIMALT := NULL ;
 30
 31        -- TO GET PAISA PART
 32       V_DECIMALN    := P_AMOUNT - TRUNC(P_AMOUNT) ;
 33
 34         IF V_DECIMALN >0 THEN
 35        V_DECIMALN := V_DECIMALN *100;
 36        END IF;
 37
 38         V_AMTNUMBR  := TRUNC(P_AMOUNT) ;
 39
 40
 41         V_TOPNUMBR  := TRUNC(V_AMTNUMBR / 100000) ;
 42         V_MAIN_AMT  := TRUNC(V_TOPNUMBR / 100);
 43         V_TOPANTNU  := V_TOPNUMBR - V_MAIN_AMT * 100 ;
 44         V_BOTTANNU  :=  V_AMTNUMBR - (V_TOPNUMBR * 100000) ;
 45
 46        IF V_MAIN_AMT > 0 THEN
 47            V_MAINAMUT   := TO_CHAR(TO_DATE(V_MAIN_AMT,'J'),'JSP') ;
 48           IF V_MAIN_AMT  = 1 THEN
 49              V_MAINAMUT := V_MAINAMUT || ' CRORE ' ;
 50           ELSE
 51              V_MAINAMUT := V_MAINAMUT || ' CRORES ' ;
 52            END IF ;
 53         END IF ;
 54
 55         IF V_TOPANTNU > 0 THEN
 56            V_TOPAMOUN   := TO_CHAR(TO_DATE(V_TOPANTNU,'J'),'JSP') ;
 57            IF V_TOPANTNU = 1 THEN
 58              V_TOPAMOUN := V_TOPAMOUN || ' LAKH ' ;
 59            ELSE
 60              V_TOPAMOUN := V_TOPAMOUN || ' LAKHS ' ;
 61            END IF;
 62         END IF ;
 63        IF V_BOTTANNU > 0 THEN
 64          V_BOTTAMUT := TO_CHAR(TO_DATE(V_BOTTANNU,'J'),'JSP') ;
 65        END IF ;
 66        IF V_DECIMALN > 0 THEN
 67           IF NVL(V_BOTTANNU,0) + NVL(V_TOPANTNU,0) > 0 THEN
 68              V_DECIMALT := ' AND ' || TO_CHAR(TO_DATE(V_DECIMALN,'J'),'JSP') || ' Paise ' ;
 69           ELSE
 70              V_DECIMALT :=  TO_CHAR(TO_DATE(V_DECIMALN,'J'),'JSP') ||' Paise ';
 71            END IF ;
 72              END IF ;
 73
 74
 75         V_TEXTRETN := LOWER(V_MAINAMUT || V_TOPAMOUN || V_BOTTAMUT || ' Tk'  || V_DECIMALT || ' ONLY') ;
 76
 77         V_TEXTRETN := UPPER(SUBSTR(V_TEXTRETN,1,1))|| SUBSTR(V_TEXTRETN,2);
 78        V_TEXTRETN  := ' '|| V_TEXTRETN;
 79      RETURN INITCAP(V_TEXTRETN);
 80
 81     END DFU_NUMBER_IN_WORD;
 82  /

Function created.

USING FUNCTION TO GET NUMBER IN WORD 
=========================================================================





SQL> SELECT DFU_NUMBER_IN_WORD(152415.25) FROM DUAL;

DFU_NUMBER_IN_WORD(152415.25)
--------------------------------------------------------------------------------
 One Lakh Fifty-Two Thousand Four Hundred Fifteen Tk And Twenty-Five Paise  Only

SQL>