| Number to words ( using JSP giving error) [message #405504] |
Thu, 28 May 2009 02:28  |
|
|
Hi,
I am trying to give the following query and i am getting the error,
SELECT TO_CHAR (TO_DATE (6929104, 'J'), 'JSP') AS converted_form
FROM DUAL
Error
ORA-01854: julian date must be between 1 and 5373484
But i need to check the data more than 5373484 (Max Limit). How do i go about it ?
|
|
|
|
|
|
|
|
|
|
| Re: Number to words ( using JSP giving error) [message #405511 is a reply to message #405504] |
Thu, 28 May 2009 02:54   |
 |
Littlefoot
Messages: 21826 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
5373484 = 31.12.9999 (dd.mm.yyyy)
Dates larger than the above seem to be out of limits; how would we insert, for example, 28.05.10020 into a DATE column? I don't know how to do that (honestly, didn't spend any time to find possible solution):SQL> create table test (col date);
Table created.
SQL> insert into test (col) values (to_date('28.05.10020', 'dd.mm.yyyyy'));
insert into test (col) values (to_date('28.05.10020', 'dd.mm.yyyyy'))
*
ERROR at line 1:
ORA-01812: year may only be specified once
SQL> insert into test (col) values (to_date('28.05.10020', 'dd.mm.yyyy'));
insert into test (col) values (to_date('28.05.10020', 'dd.mm.yyyy'))
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string
SQL>
So, perhaps you might first check whether "Julian" date is larger than 5373484; if so, it is *probably* invalid (so no converting should take place anyway).
[EDIT]Hahahah, LOL!!! Sorry, I didn't realize that it was about how to spell numbers ... me, stupid, thought that it was about Julian dates ... sorry again.
[Updated on: Thu, 28 May 2009 02:56] Report message to a moderator
|
|
|
|
| Re: Number to words ( using JSP giving error) [message #405512 is a reply to message #405511] |
Thu, 28 May 2009 02:55   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
| Littlefoot wrote on Thu, 28 May 2009 09:54 |
So, perhaps you might first check whether "Julian" date is larger than 5373484; if so, it is *probably* invalid (so no converting should take place anyway).
|
This whole trick, in the end, has nothing to do with dates. It is just a way to spell numbers.
So, even though this number might translate to an invalid date, it does not mean that the input itself was invalid.
|
|
|
|
|
|
|
|
| Re: Number to words ( using JSP giving error) [message #405527 is a reply to message #405504] |
Thu, 28 May 2009 04:38   |
|
|
Hi All,
Thanks for those wonderful suggestions/tips. I got the desired results,
AskTom Function to spell number
CREATE OR REPLACE FUNCTION spell_number (p_number IN NUMBER)
RETURN VARCHAR2
AS
TYPE myarray IS TABLE OF VARCHAR2 (255);
l_str myarray
:= myarray ('',
' thousand ',
' million ',
' billion ',
' trillion ',
' quadrillion ',
' quintillion ',
' sextillion ',
' septillion ',
' octillion ',
' nonillion ',
' decillion ',
' undecillion ',
' duodecillion '
);
l_num VARCHAR2 (50) DEFAULT TRUNC (p_number);
l_return VARCHAR2 (4000);
BEGIN
FOR i IN 1 .. l_str.COUNT
LOOP
EXIT WHEN l_num IS NULL;
IF (SUBSTR (l_num, LENGTH (l_num) - 2, 3) <> 0)
THEN
l_return :=
TO_CHAR (TO_DATE (SUBSTR (l_num, LENGTH (l_num) - 2, 3), 'J'),
'Jsp'
)
|| l_str (i)
|| l_return;
END IF;
l_num := SUBSTR (l_num, 1, LENGTH (l_num) - 3);
END LOOP;
RETURN l_return;
END;
/
Execution & Results
select spell_number(6929104) from dual
Output : Six million Nine Hundred Twenty-Nine thousand One Hundred Four
Regards,
Ashoka BL
|
|
|
|
| Re: Number to words ( using JSP giving error) [message #407257 is a reply to message #405527] |
Tue, 09 June 2009 05:40  |
 |
ramoradba
Messages: 2457 Registered: January 2009 Location: AndhraPradesh,Hyderabad,I...
|
Senior Member |
|
|
you may find here also
http://www.orafaq.com/node/1448
for indian rupee or modify Tom`s code to get decimal value (cent or paise)
Why because
ind> select spell_number(6929104.99) from dual;
SPELL_NUMBER(6929104.99)
--------------------------------------------------------------------------------
six million nine hundred twenty-nine thousand one hundred four
1 row selected.
ind> select f_words(6929104.99) from dual;
F_WORDS(6929104.99)
--------------------------------------------------------------------------------
Sixty-Nine Lakh Twenty-Nine Thousand One Hundred Four and Paise Ninety-Nine
1 row selected.
Regards
Sriram.
|
|
|
|