Home » SQL & PL/SQL » SQL & PL/SQL » Number to words ( using JSP giving error) (Oracle 11g,Win XP)
Number to words ( using JSP giving error) [message #405504] Thu, 28 May 2009 02:28 Go to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

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 #405507 is a reply to message #405504] Thu, 28 May 2009 02:43 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Search on AskTom, I remember this issue has been discussed there, including a solution.
Re: Number to words ( using JSP giving error) [message #405509 is a reply to message #405504] Thu, 28 May 2009 02:50 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member

The SEARCH option in this forum won't bite you Smile
http://www.orafaq.com/forum/m/43958/121748/?srch=Spelling+Numbers#msg_43958
Re: Number to words ( using JSP giving error) [message #405510 is a reply to message #405504] Thu, 28 May 2009 02:53 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
http://www.orafaq.com/forum/m/4210/140956/#msg_4210 This has been asked so many times here..
Re: Number to words ( using JSP giving error) [message #405511 is a reply to message #405504] Thu, 28 May 2009 02:54 Go to previous messageGo to next message
Littlefoot
Messages: 20896
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 Go to previous messageGo to next message
Frank
Messages: 7880
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 #405513 is a reply to message #405511] Thu, 28 May 2009 02:57 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Sir,You can not. If I am not wrong, Only one of the following year format codes may be specified in a date: YYYY, YYY, YY, Y.
Re: Number to words ( using JSP giving error) [message #405514 is a reply to message #405512] Thu, 28 May 2009 02:57 Go to previous messageGo to next message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Right, Frank; the fact is that I have completely missed the point ./fa/1606/0/
Re: Number to words ( using JSP giving error) [message #405527 is a reply to message #405504] Thu, 28 May 2009 04:38 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

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 Go to previous message
ramoradba
Messages: 2454
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.
Previous Topic: how much capacity to store data in oracle?
Next Topic: ORA-01045
Goto Forum:
  


Current Time: Tue Dec 06 04:41:29 CST 2016

Total time taken to generate the page: 0.17397 seconds