Converting integer value from a db column to text

Question

I have a requirement in db.

1).Table ABC, column: check_amount number number(18,4). This basically contains check amount for eg. 3000.50 to be paid to an employee.

Now a cheque is issued and that check contains this check_amount in number as well as in text form.for eg.check will have:

pay to <emplyee_name> ****$3000.50**** ****THREE THOUSAND DOLLARS AND FIFTY CENTS****

I have to generate this text using DB column value and display that on check.

Can anybody help me out, how can i achieve this in oracle 11g ?

Hint:I have heard of Julien format, but that is not working. Any suggestions is greatly appreciated.

From Nalin

Answer 1

Since Julian format works only for whole numbers, you can separate the decimal parts and then apply the Julian format trick to the separated numbers. Here's a simple demo.

DECLARE
   x   NUMBER (8, 2) := 1253.5;
   y   NUMBER;
   z   NUMBER;
BEGIN
   y := FLOOR (x);
   z := 100 * (x - y);
   DBMS_OUTPUT.put_line (TO_CHAR (TO_DATE (y, 'j'), 'jsp'));

   IF (z > 0)
   THEN
      DBMS_OUTPUT.put_line (TO_CHAR (TO_DATE (z, 'j'), 'jsp'));
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('err:' || SQLERRM);
END;

Answer 2

There is a limitation while using Julian dates ,It ranges from 1 to 5373484. That’s why if you put the values after 5373484, it will throw you an error as shown below:

ORA-01854: julian date must be between 1 and 5373484

To cater the above problem ,create a function ,and with little trick with j->jsp ,you can fetch the desired result.

CREATE OR REPLACE FUNCTION spell_number (p_number IN NUMBER)
   RETURN VARCHAR2
AS
   TYPE myArray IS TABLE OF VARCHAR2 (255);
   v_decimal     PLS_INTEGER;

   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;

   v_decimal := 100* (p_number -TRUNC(p_number)) ;

   IF v_decimal>0 THEN 

       RETURN l_return ||' Dollars AND '||TO_CHAR (TO_DATE (v_decimal, 'j'), 'jsp')|| ' Cents';        


  ELSE

   RETURN l_return ||' Dollars' ;
  END IF;

END;
/

select  spell_number(122344343444444.23) from dual;

Output:

One Hundred Twenty-Two trillion Three Hundred Forty-Four billion Three Hundred Forty-Three million Four Hundred Forty-Four thousand Four Hundred Forty-Four Dollars AND twenty-three Cents Blog Link