Oracle: Convert Currency Amount in Words Using PL/SQL

Oracle: Convert Currency Amount in Words Using PL/SQL

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.
  • PLSQL
  • 4 mins read

Below I am giving an example of Oracle stored function to convert currency amount in words using PL/SQL. For example, the amount of $123.45 would translate as One Hundred Twenty-Three Dollar and Forty-Five Cents. Also, you can change the currency in function, for instance, Rupees and Paise.

Oracle PL/SQL Function to Convert The Currency Amount in Words

The following Oracle PL/SQL stored function accepts a numeric argument and has no limitations. A number can be a decimal, integer, and negative number. The function amount_in_words has a function inside check_if_single, and check_if_single function has a n_spell function inside to convert currency amount in words. I was thinking to create a package instead of this function, but I thought that only a function would be easier to maintain.

CREATE OR REPLACE FUNCTION amount_in_words (i_amt IN NUMBER)
   RETURN VARCHAR2
IS
   n_dollar   NUMBER;
   n_cents    NUMBER;

   FUNCTION check_if_single (i_num IN NUMBER, currency IN VARCHAR2)
      RETURN VARCHAR2
   IS
      FUNCTION n_spell (i_num IN NUMBER)
         RETURN VARCHAR2
      AS
         TYPE w_Array IS TABLE OF VARCHAR2 (255);

         l_str w_array
               := w_array ('',
                           ' thousand ',
                           ' million ',
                           ' billion ',
                           ' trillion ',
                           ' quadrillion ',
                           ' quintillion ',
                           ' sextillion ',
                           ' septillion ',
                           ' octillion ',
                           ' nonillion ',
                           ' decillion ',
                           ' undecillion ',
                           ' duodecillion ');

         l_num           VARCHAR2 (50) DEFAULT TRUNC (i_num);
         l_is_negative   BOOLEAN := FALSE;
         l_return        VARCHAR2 (4000);
      BEGIN
         IF SIGN (i_num) = -1
         THEN
            l_is_negative := TRUE;
            l_num := TRUNC (ABS (i_num));
         END IF;

         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;

         IF NOT l_is_negative
         THEN
            RETURN INITCAP (l_return);
         ELSE
            RETURN 'Negative ' || INITCAP (l_return);
         END IF;
      END n_spell;
   BEGIN
      IF i_num = 1
      THEN
         RETURN 'One ' || currency;
      ELSE
         RETURN n_spell (i_num) || ' ' || currency;
      END IF;
   END check_if_single;
BEGIN
   IF i_amt IS NULL
   THEN
      RETURN '';
   END IF;

   n_dollar := TRUNC (i_amt);
   n_cents := (ABS (i_amt) - TRUNC (ABS (i_amt))) * 100;

   IF NVL (n_cents, 0) > 0
   THEN
      RETURN    check_if_single (n_dollar, 'Dollar')
             || ' and '
             || check_if_single (n_cents, 'Cents');
   ELSE
      RETURN check_if_single (n_dollar, 'Dollar');
   END IF;
END amount_in_words;
/

Test

SELECT amount_in_words (89378.58) FROM DUAL;

Output

Eighty-Nine Thousand Three Hundred Seventy-Eight Dollar and Fifty-Eight Cents

Test through a table

SELECT client_code,
       balance_amt,
       amount_in_words (balance_amt) balance_amount_in_words
  FROM account_balance;

Output

CLIENT_CODEBALANCE_AMTBALANCE_AMOUNT_IN_WORDS
8849978849.98Seventy-Eight Thousand Eight Hundred Forty-Nine Dollar and Ninety-Eight Cents
774937738829.15Seven Million Seven Hundred Thirty-Eight Thousand Eight Hundred Twenty-Nine Dollar and Fifteen Cents
8839999836662388.98Ninety-Nine Billion Eight Hundred Thirty-Six Million Six Hundred Sixty-Two Thousand Three Hundred Eighty-Eight Dollar and Ninety-Eight Cents
97737-88993.5Negative Eighty-Eight Thousand Nine Hundred Ninety-Three Dollar and Fifty Cents
88948998349Nine Hundred Ninety-Eight Thousand Three Hundred Forty-Nine Dollar

You can change the currency when calling the check_if_single function from amount_in_words function. For example, I changed to Rupees and Paise in the following part of the PL/SQL code:

 IF NVL (n_cents, 0) > 0
   THEN
      RETURN    check_if_single (n_dollar, 'Rupees')
             || ' and '
             || check_if_single (n_cents, 'Paise');
   ELSE
      RETURN check_if_single (n_dollar, 'Rupees');
   END IF;

Test after making the change

SELECT amount_in_words (7836.58) in_words FROM DUAL;

Output

Seven Thousand Eight Hundred Thirty-Six Rupees and Fifty-Eight Paise

Maybe you need to shift the word Rupees from end to starting position of the line depending on your currency format and which can be changed in the above function easily.

See also: