Converting to millions and more

I was inserting millions of records and wanted to check the total number of records in millions.

Here is the SQL to convert the total number of records to millions and more.

Easiest solution is select to_char(to_date(&number,'j'),'jsp') from dual;


But if the number  is more than 5373484, this method fails with below error.


Enter value for number: 9670000
old   1: select to_char(to_date(&number,'j'),'jsp') from dual
new   1: select to_char(to_date(9670000,'j'),'jsp') from dual
select to_char(to_date(9670000,'j'),'jsp') from dual
                       *
ERROR at line 1:
ORA-01854: julian date must be between 1 and 5373484


The other option was to write a function and with little trick with j->jsp ,you can fetch the desired result.
Credits to http://viralpatel.net/blogs/convert-number-into-words-oracle-sql-query/

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;

/







The solution was great :-




select spell_number(count(1)), count(1) from table_name where N_CAMPAIGN_ID=128; COUNT(1) ---------- Fifteen million Eight Hundred Sixty thousand 15860000