Number PL/SQL Library

Pre-Requisites:

None.

Code:

Paste this code into a .sql script and run it in SQL*PLUS by the owner of the target schema.

-- ------------------------------------------------------------------------- -- -- -- -- Title: lib_number.sql -- -- Author: Steve Roach -- -- -- -- Description: A set of library functions and procedures to support number -- -- manipulation. -- -- -- -- Functions: cardinal_num -- -- Return the cardinal string equivalent of the input -- -- number. -- -- -- -- Installation: To install for production, run this script as-is. This will -- -- install the package in the target database. -- -- To install for debug, edit the 'ALTER SESSION' statement, -- -- below, changing 'debug: FALSE' to 'debug: TRUE' before -- -- running the script. -- -- -- -- ------------------------------------------------------------------------- -- -- Set flags for running debug mode: ALTER SESSION SET PLSQL_CCFLAGS = 'debug: FALSE'; -- Package definition. CREATE OR REPLACE PACKAGE lib_number AS FUNCTION cardinal_num( p_number NUMBER) RETURN VARCHAR2; END lib_number; / SHOW ERRORS CREATE OR REPLACE PACKAGE BODY lib_number AS -- Return the cardinal string equivalent of the input -- number. FUNCTION cardinal_num( p_number NUMBER) RETURN VARCHAR2 IS l_cardinal VARCHAR2(4000); l_last_digit VARCHAR2(1); BEGIN $IF $$debug $THEN dbms_output.put_line('cardinal_num START'); $END l_cardinal := TO_CHAR(p_number); l_last_digit := SUBSTR(l_cardinal, LENGTH(l_cardinal - 1)); CASE WHEN l_last_digit = '1' THEN l_cardinal := l_cardinal || 'st'; WHEN l_last_digit = '2' THEN l_cardinal := l_cardinal || 'nd'; WHEN l_last_digit = '3' THEN l_cardinal := l_cardinal || 'rd'; ELSE l_cardinal := l_cardinal || 'th'; END CASE; $IF $$debug $THEN dbms_output.put_line('RETURN = ' || l_cardinal); dbms_output.put_line('cardinal_num END'); $END RETURN l_cardinal; END cardinal_num; END lib_number; / SHOW ERRORS