Bond & Pollard | Oracle EBS Consultants

PL/SQL Fun with Numbers

In this blog we look at the PL/SQL package util_numeric.

Click on the following links to view the PL/SQL source code.

util_numeric package spec

util_numeric package body

View the package specification for instructions on how to call the following functions:

UTIL_NUMERIC Functions
dectobase	Returns a string containing the base value of the specified number, e.g. '11' for 3 in base 2
basetodec	Returns a number representing the decimal value of the specified base number, e.g. 3 for '11' in base 2.
dectohex	Returns a string containing the hexadecimal value.
hextodec	Returns the decimal value for the hexadecimal specified.
factorial	Returns the factorial of the given number.
factorialr	Returns the factorial of the given number, using recursion.
sort_numbers	Returns a string containing the sorted list of numbers.
num_to_alphanumeric	Returns a string containing the alphanumeric code, e.g. 1='A', 2='B', 27='AA', 28='AB'. This is how columns are labelled in Excel.
dectoalpha	Returns a string containing the alphanumeric code, e.g. 1='A', 2='B', 27='AA', 28='AB'.
alphatodec	Returns the decimal integer value of the specified alphanumeric code, e.g. 28 for 'AB'.
remove_duplicates_list	Returns a string of comma separated numbers without duplicate values.
reverse_list	Reverses the sequence of numbers in a comma separated list.

UTIL_NUMERIC Functions

Function Name

Description

dectobase

Returns a string containing the base value of the specified number, e.g. '11' for 3 in base 2

basetodec

Returns a number representing the decimal value of the specified base number, e.g. 3 for '11' in base 2.

dectohex

Returns a string containing the hexadecimal value.

hextodec

Returns the decimal value for the hexadecimal specified.

factorial

Returns the factorial of the given number.

factorialr

Returns the factorial of the given number, using recursion.

sort_numbers

Returns a string containing the sorted list of numbers.

num_to_alphanumeric

Returns a string containing the alphanumeric code, e.g. 1='A', 2='B', 27='AA', 28='AB'. This is how columns are labelled in Excel.

dectoalpha

Returns a string containing the alphanumeric code, e.g. 1='A', 2='B', 27='AA', 28='AB'.

alphatodec

Returns the decimal integer value of the specified alphanumeric code, e.g. 28 for 'AB'.

remove_duplicates_list

Returns a string of comma separated numbers without duplicate values.

reverse_list

Reverses the sequence of numbers in a comma separated list.

num_to_alphanumeric

The function num_to_alphanumeric converts integers to alphanumeric codes, similarly to how Excel labels columns. For example: 1=A, 2=B, 26=Z, 27=AA, 28=AB, 52=AZ, 53=BA etc.
Here’s how the function works:

We start with the input number.
In each iteration, we calculate the remainder after dividing by 26 (the number of letters in the alphabet).
We convert the remainder to the corresponding letter (‘A’ for 1, ‘B’ for 2, and so on).
We prepend the letter to the result string.
We update the input number by subtracting the remainder and dividing by 26.
Repeat until the input number becomes zero.

Now you can use this function to convert numbers to the desired alphanumeric code. For example:
NUM_TO_ALPHANUMERIC(1) returns 'A'.
NUM_TO_ALPHANUMERIC(27) returns 'AA'.
NUM_TO_ALPHANUMERIC(52) returns 'AZ'.
NUM_TO_ALPHANUMERIC(53) returns 'BA'.