This discussion is archived
9 Replies Latest reply: Jan 11, 2010 9:13 AM by 587383 RSS

Julian date error

610101 Newbie
Currently Being Moderated
I am getting error - 'julian date must be between 1 and 5373484' while executing the below given function
function CF_AMOUNT_TO_WORDSFormula return Char is
   CURSOR C1 IS
   SELECT GH_CURR_CODE
   FROM   OT_GR_HEAD
   WHERE  GH_COMP_CODE  = :M_COMP_CODE
   AND    GH_SYS_ID     = :P_SYS_ID;
   
   M_CURR_CODE                 OW_FS.CURR_CODE%TYPE;
   M_AMOUNT_TO_WORDS           OW_FS.ANNOTATION%TYPE;
   
begin
  IF C1%ISOPEN THEN
      CLOSE C1;
  END IF;
  
  OPEN C1;
    FETCH C1 INTO M_CURR_CODE;
  CLOSE C1;
  
  :P_CURR_CODE   := NVL(M_CURR_CODE,' ');
  
  P_AMOUNT_TO_WORDS (NVL(:CF_FINAL_NET_VALUE,0), M_CURR_CODE,
                 :P_LANG_CODE, M_AMOUNT_TO_WORDS);

RETURN INITCAP(NVL(M_AMOUNT_TO_WORDS,' '));                 
end;
The currency code is DOLLAR.

Please help to solve the problem


Yogesh
  • 1. Re: Julian date error
    730428 Guru
    Currently Being Moderated
    Probably the result of the formula column :CF_FINAL_NET_VALUE is above 5373484...

    The procedure you're using to convert a number to words uses a known trick:
    SQL> select to_char(to_date(12345,'J'),'JSP') from dual;
    
    TO_CHAR(TO_DATE(12345,'J'),'JSP')
    ----------------------------------------
    TWELVE THOUSAND THREE HUNDRED FORTY-FIVE
    It converts the number to a julian date and uses the JSP date format that conversts a julian date to the desidered string...

    The limit is that a julian data cannot be over 5373484!!


    Max
    [My Italian Oracle blog|http://oracleitalia.wordpress.com/2010/01/10/crittografia-in-plsql-utilizzando-dbms_crypto/]
  • 2. Re: Julian date error
    513949 Journeyer
    Currently Being Moderated
    Hi,

    Pls, post the complete error msg. specially the line number. Maybe the error occur in the called procedure P_AMOUNT_TO_WORDS. In this case, post the procedure.

    And take a look in this link, may it is helpful: [Ask Tom - Spelling Out a Number |http://www.oracle.com/technology/oramag/oracle/06-jul/o46asktom.html]

    []s,
    Miguel
  • 3. Re: Julian date error
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    That error is usually raised by TO_DATE. Where are you calling TO_DATE? From p_amount_to_words? If so, post the code of p_amount_to_words.
    Actually, post a complete script that people can run to re-create the entire problem: that includes the code of all functions used, and the actual call that creates an error now. What are the correct results from that error?

    As the error message implies, the trick you are uing for converting an integer to words only works for integers between 1 and 5373484.
    To handle larger numbers, you can convert the last 6 digits the way you are doing it now, but make separate calls to TO_CHAR to get the millions (if necessary), billions (if necessary), trillions (if necessary), ... and concatenate them all together.
  • 4. Re: Julian date error
    Hoek Guru
    Currently Being Moderated
    Dealing with numbers larger than 5373484 is explained here:
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1407603857650
  • 5. Re: Julian date error
    730428 Guru
    Currently Being Moderated
    To manage numbers up to 999,999,999 there's the following:
    SQL> select  to_char( to_timestamp( lpad(12345,9,'0'), 'FF9' ), 'FFSP' )
      2  from dual
      3  ;
    
    TO_CHAR(TO_TIMESTAMP(LPAD(12345,9,'0'),'FF9'),'FFSP')
    ---------------------------------------------------------------------------
    TWELVE THOUSAND THREE HUNDRED FORTY-FIVE
    Max
    [My Italian Oracle blog|http://oracleitalia.wordpress.com/2010/01/10/crittografia-in-plsql-utilizzando-dbms_crypto/]
  • 6. Re: Julian date error
    Hoek Guru
    Currently Being Moderated
    Hey, now that's cool, Max, thanks! :)
    Where did you get 'FFSP' from?
  • 7. Re: Julian date error
    730428 Guru
    Currently Being Moderated
    Where did you get 'FFSP' from?
    http://www.sqlsnippets.com/en/topic-12355.html

    Max
    [My Italian Oracle blog|http://oracleitalia.wordpress.com/2010/01/10/crittografia-in-plsql-utilizzando-dbms_crypto/]
  • 8. Re: Julian date error
    Hoek Guru
    Currently Being Moderated
    Thanks again, very interesting site.
  • 9. Re: Julian date error
    587383 Expert
    Currently Being Moderated
    Here's my symbolic

    * Helpful

    gold star Max.

    Great tip ;)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points