rem -----------------------------------------------------------------------
rem Filename:   cheqprint.sql
rem Purpose:    This function will convert a number to words, handy for 
rem             ptinting  cheques. 
rem Notes:      The number before the decimal should be between 1..5373484
rem Date:       24-Feb-2003
rem Author:     Sebastian Thomas, sebastianthomas@rediffmail.com
rem -----------------------------------------------------------------------

CREATE OR REPLACE FUNCTION NUMBER_CONVERSION(NUM NUMBER) RETURN VARCHAR2
IS
  A VARCHAR2(1000);
  B VARCHAR2(20);
  X NUMBER;
  Y NUMBER := 1;
  Z NUMBER;
  LSIGN NUMBER;
  NO NUMBER;
BEGIN
  X:= INSTR(NUM, '.');
  LSIGN := SIGN(NUM);
  NO := ABS(NUM);	
  IF X = 0 THEN 
     SELECT 	TO_CHAR(TO_DATE(NO, 'J'), 'JSP')  INTO A FROM DUAL;
  ELSE
     SELECT 	to_char(to_date(SUBSTR(NO, 1,
     		NVL(INSTR(NO, '.')-1, LENGTH(NO))),
     			'J'), 'JSP') INTO A FROM DUAL;
     SELECT	LENGTH(SUBSTR(NO, INSTR(NO, '.')+1)) INTO Z FROM DUAL;
     A := A ||' POINT ';
     WHILE Y< Z+1 LOOP
	 SELECT TO_CHAR(TO_DATE(SUBSTR(NO, (INSTR(NO, '.')+Y), 1), 'J'), 'JSP')
	       INTO B FROM DUAL;
		A := A || B ||' ';
		y :=y+1;
     END LOOP;
  END IF;
  IF LSIGN = -1 THEN
     RETURN 'NEGATIVE '||A;
  ELSE
     RETURN A;
  END IF;
END;
/
show errors

-- Examples:
Select number_conversion(-3786.9899876) from dual;
Select number_conversion(7685.78788) from dual;
Select number_conversion(7678) from dual;