Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Number to word convertion error

Re: Number to word convertion error

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Thu, 28 Apr 2005 18:14:08 +0200
Message-ID: <42710b98$0$18381$626a14ce@news.free.fr>

"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:

http://asktom.oracle.com/pls/ask/f?p=4950:8:2460859015550127839::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:1407603857650

Regards
Michel Cadot Received on Thu Apr 28 2005 - 11:14:08 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US