CREATE OR REPLACE PACKAGE spell_number_pkg AS
/*
Spell Number in Indonesia and English
*/
AMOUNT_EXCEEDS_LIMIT EXCEPTION;

function to_words(
p_amount number
) return varchar2;

function spell_number(
p_number in number
) return varchar2;
END spell_number_pkg;

CREATE OR REPLACE PACKAGE BODY spell_number_pkg
IS
/*
Spell Number in Indonesia and English
*/
–*******************************************************************
–* PRIVATE MEMBERS
TYPE varchar2_tbl_t IS TABLE OF VARCHAR2 (30);

FUNCTION to_hundredth_words (p_amount NUMBER)
RETURN VARCHAR2
IS
l_translate_groups   varchar2_tbl_t
:= varchar2_tbl_t (‘SATU’,
‘DUA’,
‘TIGA’,
‘EMPAT’,
‘LIMA’,
‘ENAM’,
‘TUJUH’,
‘DELAPAN’,
‘SEMBILAN’
);
l_tenth_groups       varchar2_tbl_t
:= varchar2_tbl_t (”, ‘PULUH’, ‘RATUS’);
l_char               VARCHAR2 (3);
l_length             NUMBER;
l_number             NUMBER;
l_words              VARCHAR2 (2000);
BEGIN
l_char := TO_CHAR (p_amount);
l_length := LENGTH (l_char);

FOR i IN 1 .. l_length
LOOP
l_number := TO_NUMBER (SUBSTR (l_char, l_length + 1 – i, 1));

IF (l_number != 0)
THEN
l_words :=
l_translate_groups (l_number)
|| RTRIM (‘ ‘ || l_tenth_groups (i))
|| RTRIM (‘ ‘ || l_words);
END IF;
END LOOP;

IF (l_words IS NOT NULL)
THEN
l_words := REPLACE (l_words, ‘SATU RATUS’, ‘SERATUS’);
l_words := REPLACE (l_words, ‘SATU PULUH SATU’, ‘SEBELAS’);
l_words := REPLACE (l_words, ‘SATU PULUH’, ‘SEPULUH’);
l_words := REPLACE (l_words, ‘SEPULUH DUA’, ‘DUA BELAS’);
l_words := REPLACE (l_words, ‘SEPULUH TIGA’, ‘TIGA BELAS’);
l_words := REPLACE (l_words, ‘SEPULUH EMPAT’, ‘EMPAT BELAS’);
l_words := REPLACE (l_words, ‘SEPULUH LIMA’, ‘LIMA BELAS’);
l_words := REPLACE (l_words, ‘SEPULUH ENAM’, ‘ENAM BELAS’);
l_words := REPLACE (l_words, ‘SEPULUH TUJUH’, ‘TUJUH BELAS’);
l_words := REPLACE (l_words, ‘SEPULUH DELAPAN’, ‘DELAPAN BELAS’);
l_words := REPLACE (l_words, ‘SEPULUH SEMBILAN’, ‘SEMBILAN BELAS’);
END IF;

RETURN l_words;
END to_hundredth_words;

–**********************************************************
–* PUBLIC MEMBERS
FUNCTION to_words (p_amount NUMBER)
RETURN VARCHAR2
IS
l_thousandth_groups   varchar2_tbl_t
:= varchar2_tbl_t (”, ‘RIBU’, ‘JUTA’, ‘MILIAR’, ‘TRILIUN’);
l_idx                 NUMBER          := 1;
l_amount              NUMBER;
l_fraction            NUMBER;
l_hundredth_words     VARCHAR2 (240);
l_words               VARCHAR2 (2000);
BEGIN
l_amount := TRUNC (ABS (p_amount));

IF (LENGTH (TO_CHAR (l_amount)) > 3 * l_thousandth_groups.COUNT)
THEN
RAISE amount_exceeds_limit;
END IF;

l_fraction := ABS (ROUND (p_amount, 2)) – l_amount;

LOOP
l_hundredth_words :=
to_hundredth_words (l_amount – TRUNC (l_amount, -3));

IF (l_hundredth_words IS NOT NULL)
THEN
l_words :=
l_hundredth_words
|| RTRIM (‘ ‘ || l_thousandth_groups (l_idx))
|| RTRIM (‘ ‘ || l_words);
END IF;

l_amount := TRUNC (l_amount, -3) / 1000;
EXIT WHEN l_amount = 0;
l_idx := l_idx + 1;
END LOOP;

IF (SUBSTR (l_words, 1, 9) = ‘SATU RIBU’)
THEN
l_words := ‘SERIBU’ || SUBSTR (l_words, 10);
END IF;

l_words := l_words;

IF (l_fraction != 0)
THEN
l_words :=
l_words || ‘ ‘ || to_hundredth_words (l_fraction * 100)
|| ‘ SEN’;
END IF;

RETURN l_words;
END to_words;

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 (TO_NUMBER (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 spell_number;
END spell_number_pkg;
/