Live ORACLE

rem -----------------------------------------------------------------------
rem Filename:   indicheq.sql
rem Purpose:    This procedure will convert numbers to chars for printing
rem             cheques amount in Indian Style. It will print amount from
rem             Rs1 to Rs.989999999.
rem Note:       In order to see your output in SQL*Plus, set serverout on
rem Date:       22-Feb-2003
rem Author:     Birender Kumar, bir_canada@yahoo.com
rem Updateded:  04-Aug-2003
rem Updated By: marine, marine00072003@yahoo.com
rem -----------------------------------------------------------------------

SET SERVEROUTPUT ON;

CREATE OR REPLACE PROCEDURE cheq(n NUMBER)
as
  L       NUMBER := 0;
  OUTPUT  VARCHAR2(2000) := '';
  X       VARCHAR2(2000) := '';
  X1      VARCHAR2(2000) := '';
  C1      VARCHAR2(2000) := '';
BEGIN
   L := length(N);

   IF N < 0 OR N > 999999999 THEN
      DBMS_OUTPUT.PUT_LINE('INVALID AMOUNT');
   else

      if ( N = 0 ) THEN
         X := 'ZERO ';
      elsif ( N  <= 99999 ) THEN
         X := to_char(to_date(N,'J'),'JSP') || ' ';
      else
        
         if ( to_number(substr(N, L - 5 + 1)) = 0 ) then
            X := '';
         else
            X := to_char(to_date(to_number(substr(N, L - 5 + 1)),'J'),'JSP') || ' ';
         end if;

         if ( L = 6 ) then
            X1 := to_char(to_date(to_number(substr(N, 1, L - 5)),'J'),'JSP') || ' LAKH ';
         else
            if ( to_number(substr(N, L - 5 -1, 2)) = 0 ) then
               X1 := '';
            else
               X1 := to_char(to_date(to_number(substr(N, L - 5 - 1, 2)),'J'),'JSP') || ' LAKH ';
            end if;

            if ( L >= 8 ) then
               C1 := to_char(to_date(to_number(substr(N, 1, L-7)),'J'),'JSP')||' CRORE ';
            end if;
         end if;
      end if;

      if ( N = 0 OR N = 1 ) THEN
         DBMS_OUTPUT.PUT_LINE(N||' => '||X||'RUPEE ONLY');
      else
         DBMS_OUTPUT.PUT_LINE(N||' => '||C1||X1||X||'RUPEES ONLY');
      end if;
   end if;
END CHEQ;
/
show errors

convert numbers to chars for printing cheques amount in Indian Style