CREATE OR REPLACE FUNCTION spel_out (a_number NUMBER)
RETURN CHAR IS
TYPE numtab IS TABLE OF VARCHAR2 (30)
INDEX BY BINARY_INTEGER;
number_chart numtab;
crore NUMBER;
lakh NUMBER;
thou NUMBER;
hund NUMBER;
doubl NUMBER;
sing NUMBER;
deci NUMBER;
text VARCHAR2 (500);
BEGIN
--The Table
number_chart (1) := 'One';
number_chart (2) := 'Two';
number_chart (3) := 'Three';
number_chart (4) := 'Four';
number_chart (5) := 'Five';
number_chart (6) := 'Six';
number_chart (7) := 'Seven';
number_chart (8) := 'Eight';
number_chart (9) := 'Nine';
number_chart (10) := 'Ten';
number_chart (11) := 'Eleven';
number_chart (12) := 'Twelve';
number_chart (13) := 'Thirteen';
number_chart (14) := 'Fourteen';
number_chart (15) := 'Fifteen';
number_chart (16) := 'Sixteen';
number_chart (17) := 'Seventeen';
number_chart (18) := 'Eighteen';
number_chart (19) := 'Nineteen';
number_chart (20) := 'Twenty';
number_chart (30) := 'Thirty';
number_chart (40) := 'Forty';
number_chart (50) := 'Fifty';
number_chart (60) := 'Sixty';
number_chart (70) := 'Seventy';
number_chart (80) := 'Eighty';
number_chart (90) := 'Ninety';
crore := FLOOR (a_number / 10000000);
lakh := FLOOR ((a_number - TRUNC (a_number, -7)) / 100000);
thou := FLOOR ((a_number - TRUNC (a_number, -5)) / 1000);
hund := FLOOR ((a_number - TRUNC (a_number, -3)) / 100);
doubl := TRUNC (a_number - TRUNC (a_number, -2), 0);
sing := TRUNC (a_number - TRUNC (a_number, -1), 0);
deci := (a_number - TRUNC (a_number, 0)) * 100;
IF crore <> 0 THEN
text := spel_out (crore) || ' ' || 'Crore ';
END IF;
IF lakh <> 0 THEN
IF (lakh <= 20) OR (lakh MOD 10 = 0) THEN
text := text || number_chart (lakh) || ' Lac ';
ELSE
text :=
text
|| number_chart (TRUNC (lakh, -1))
|| ' '
|| number_chart (TRUNC (lakh, 0) - TRUNC (lakh, -1))
|| ' Lac ';
END IF;
END IF;
IF thou <> 0 THEN
IF (thou <= 20) OR (thou MOD 10 = 0) THEN
text := text || number_chart (thou) || ' Thousand ';
ELSE
text :=
text
|| number_chart (TRUNC (thou, -1))
|| ' '
|| number_chart (TRUNC (thou, 0) - TRUNC (thou, -1))
|| ' Thousand ';
END IF;
END IF;
IF hund <> 0 THEN
IF (hund <= 20) OR (hund MOD 10 = 0) THEN
text := text || number_chart (hund) || ' Hundred ';
ELSE
text :=
text
|| number_chart (TRUNC (hund, -1))
|| ' '
|| number_chart (TRUNC (hund, 0) - TRUNC (hund, -1))
|| ' Hundred ';
END IF;
END IF;
IF doubl <> 0 THEN
IF (doubl <= 20) OR (doubl MOD 10 = 0) THEN
text := text || number_chart (doubl) || ' ';
ELSE
text :=
text
|| number_chart (TRUNC (doubl, -1))
|| ' '
|| number_chart (TRUNC (doubl, 0) - TRUNC (doubl, -1))
|| ' ';
END IF;
END IF;
IF deci <> 0 THEN
IF (deci <= 20) OR (deci MOD 10 = 0) THEN
text := text || 'and ' || number_chart (deci) || ' Paisa ';
ELSE
text :=
text
|| 'and '
|| number_chart (TRUNC (deci, -1))
|| ' '
|| number_chart (TRUNC (deci, 0) - TRUNC (deci, -1))
|| ' Paisa ';
END IF;
END IF;
IF text IS NOT NULL THEN
RETURN (text || 'Only');
ELSE
RETURN (text);
END IF;
END spel_out;
………………………….ANOTHER ONE
CREATE OR REPLACE FUNCTION spell_out (p_arg IN NUMBER)
RETURN VARCHAR2 IS
v_result VARCHAR2 (2000);
v_length NUMBER;
v_less_lakh VARCHAR2 (100);
v_crore VARCHAR2 (200);
v_length_crore NUMBER;
BEGIN
v_length := LENGTH (p_arg);
IF p_arg <= 99999 THEN
v_result := TO_CHAR (TO_DATE (p_arg, 'j'), 'Jsp') || 'Taka Only';
ELSIF v_length = 6 THEN
v_less_lakh :=
TO_CHAR (TO_DATE (SUBSTR (p_arg, 2), 'j'), 'Jsp')
|| ' Taka Only';
v_result :=
TO_CHAR (TO_DATE (SUBSTR (p_arg, 1, 1), 'j'), 'Jsp')
|| ' Lakh';
v_result := v_result || ' ' || v_less_lakh;
ELSIF v_length = 7 THEN
v_less_lakh :=
TO_CHAR (TO_DATE (SUBSTR (p_arg, 3), 'j'), 'Jsp')
|| ' Taka Only';
v_result :=
TO_CHAR (TO_DATE (SUBSTR (p_arg, 1, 2), 'j'), 'Jsp')
|| ' Lakh';
v_result := v_result || ' ' || v_less_lakh;
ELSIF v_length > 7 THEN
v_length_crore := v_length - 7;
v_crore :=
TO_CHAR (TO_DATE (SUBSTR (p_arg, 1, v_length_crore), 'j'), 'Jsp')
|| ' Crore';
v_result :=
TO_CHAR (TO_DATE (SUBSTR (p_arg, v_length_crore + 1, 2), 'j'),
'Jsp'
)
|| ' Lakh';
v_less_lakh :=
TO_CHAR (TO_DATE (SUBSTR (p_arg, v_length_crore + 3), 'j'), 'Jsp')
|| ' Taka Only';
v_result := v_crore || ' ' || v_result || ' ' || v_less_lakh;
END IF;
RETURN v_result;
END;
Halim is a Sr. Database Engineer/Data Architect (in Atlanta, USA) who is an Oracle certified (OCP) DBA, (OCP) Developer, Certified Cloud Architect Professional as well as OCI Autonomous DB specialist with extensive expertise in Database design, configuration, tuning, capacity planning, RAC, DG, Scripting, Python, PL/SQL etc. He achieved 16th position in worldwide first-ever PL/SQL Challenge cup playoff- http://plsql-challenge.blogspot.com/2010/07/winners-of-first-plsql-challenge.html
Subscribe to:
Post Comments (Atom)
My Blog List
-
-
Index Usage – 21 week ago
-
-
-
-
-
-
-
-
Oracle Cloud & Third party tools3 years ago
-
-
-
Moving Sideways7 years ago
-
Upcoming Events...10 years ago
-
No comments:
Post a Comment