Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Number to word convertion error
"RT" <robbyt_at_unocal.com> a écrit dans le message de
news:1114684040.993866.199510_at_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.
|
A definitive (and 4 years old) answer to the issue:
Regards
Michel Cadot
Received on Thu Apr 28 2005 - 11:14:08 CDT