9 Replies Latest reply: Jan 11, 2010 11:13 AM by fsitja RSS

    Julian date error

    610101
      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
          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
            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
              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
                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
                  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
                    Hey, now that's cool, Max, thanks! :)
                    Where did you get 'FFSP' from?
                    • 7. Re: Julian date error
                      730428
                      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
                        Thanks again, very interesting site.
                        • 9. Re: Julian date error
                          fsitja
                          Here's my symbolic

                          * Helpful

                          gold star Max.

                          Great tip ;)