Number to word convertion error

From: RT <robbyt_at_unocal.com>
Date: 28 Apr 2005 03:27:21 -0700
Message-ID: <1114684040.993866.199510@l41g2000cwc.googlegroups.com>

Hi,
I'm trying to spell out 1,721,131.31 using a plsql and got an error saying "ORA-01841: (full) year must be between -4713 and +9999, and not be 0"

Is anybody ever come across this error before? I'm using a function posted on the web as follows:

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; Thanks in advance. Received on Thu Apr 28 2005 - 05:27:21 CDT