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 link view the PL/SQL source code.

PL/SQL Package: UTIL_NUMERIC

The package contains the following functions:

UTIL_NUMERIC Functions
binary_search_leftmost	p_target: Number to find ,p_list: String of comma separated numbers to be searched ,p_exact_match: Boolean. TRUE - return 0 if match not found.	Search list of numbers for a target value using a Binary Chop (Logarithmic) Search. Returns leftmost position of duplicate values.
binary_search_rightmost	p_target: Number to find ,p_list: String of comma separated numbers to be searched ,p_exact_match: Boolean. TRUE - return 0 if match not found.	Search list of numbers for a target value using a Binary Chop (Logarithmic) Search. Returns rightmost position of duplicate values.
binary_search_nearest	p_target: Number to find ,p_list: String of comma separated numbers to be searched	Returns position of value in array nearest to the target value.
binary_search_predecessor	p_target: Number to find ,p_list: String of comma separated numbers to be searched	Returns position of nearest lower value.
binary_search_successor	p_target: Number to find ,p_list: String of comma separated numbers to be searched	Returns position of nearest higher value.
binary_search_range	p_range_from: Start number to search ,p_range_to: End number to search ,p_list: String of comma separated numbers to be searched	Returns count of numbers in range.
binary_search_rank	p_target: Number to find ,p_list: String of comma separated numbers to be searched	Returns count of elements preceding target.
dectobase	p_number: Decimal integer to be converted ,p_base: Integer representing number base, e.g. 2 is Binary, 8 is octal, 16 is hexadecimal	Returns a string containing the base value of the specified number, e.g. '11' for 3 in base 2
basetodec	p_number: A string containing the base value to be converted to decimal. e.g. '10' is the binary string representing 2 in base 10. ,p_base: Integer representing number base, e.g. 2 is Binary, 8 is octal, 16 is hexadecimal	Returns a number representing the decimal value of the specified base number, e.g. 3 for '11' in base 2.
dectohex	p_number: Decimal integer to be converted to Hexadecimal.	Returns a string containing the hexadecimal value.
hextodec	p_number: Hexadecimal string to be converted to decimal.	Returns the decimal value for the hexadecimal specified.
factorial	p_number: Positive integer.	Returns the factorial of the given number.
factorialr	p_number: Positive integer.	Returns the factorial of the given number, using recursion.
sort_numbers	p_string: A string of numbers, separated by commas, to be sorted ,p_order: Sort sequence 'A' for ascending, all other values Descending.	Returns a string containing the sorted list of numbers.
num_to_alphanumeric	p_number: A positive integer to be converted.	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	p_number: A positive integer to be converted. ,p_range: Number between 1 and 26, representing range of alphabetic characters to use in code, e.g. 5 would use letters A to E.	Returns a string containing the alphanumeric code, e.g. 1='A', 2='B', 27='AA', 28='AB'.
alphatodec	p_code: A string containg the alphanumeric code, e.g. 'AA' for 27. ,p_range: Number between 1 and 26, representing range of alphabetic characters to use in code, e.g. 5 would use letters A to E.	Returns the decimal integer value of the specified alphanumeric code, e.g. 28 for 'AB'.

UTIL_NUMERIC Functions

Function Name

Arguments

Description

binary_search_leftmost

p_target: Number to find

,p_list: String of comma separated numbers to be searched

,p_exact_match: Boolean. TRUE - return 0 if match not found.

Search list of numbers for a target value using a Binary Chop (Logarithmic) Search. Returns leftmost position of duplicate values.

binary_search_rightmost

p_target: Number to find

,p_list: String of comma separated numbers to be searched

,p_exact_match: Boolean. TRUE - return 0 if match not found.

Search list of numbers for a target value using a Binary Chop (Logarithmic) Search. Returns rightmost position of duplicate values.

binary_search_nearest

p_target: Number to find

,p_list: String of comma separated numbers to be searched

Returns position of value in array nearest to the target value.

binary_search_predecessor

p_target: Number to find

,p_list: String of comma separated numbers to be searched

Returns position of nearest lower value.

binary_search_successor

p_target: Number to find

,p_list: String of comma separated numbers to be searched

Returns position of nearest higher value.

binary_search_range

p_range_from: Start number to search

,p_range_to: End number to search

,p_list: String of comma separated numbers to be searched

Returns count of numbers in range.

binary_search_rank

p_target: Number to find

,p_list: String of comma separated numbers to be searched

Returns count of elements preceding target.

dectobase

p_number: Decimal integer to be converted

,p_base: Integer representing number base, e.g. 2 is Binary, 8 is octal, 16 is hexadecimal

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

basetodec

p_number: A string containing the base value to be converted to decimal. e.g. '10' is the binary string representing 2 in base 10.

,p_base: Integer representing number base, e.g. 2 is Binary, 8 is octal, 16 is hexadecimal

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

dectohex

p_number: Decimal integer to be converted to Hexadecimal.

Returns a string containing the hexadecimal value.

hextodec

p_number: Hexadecimal string to be converted to decimal.

Returns the decimal value for the hexadecimal specified.

factorial

p_number: Positive integer.

Returns the factorial of the given number.

factorialr

p_number: Positive integer.

Returns the factorial of the given number, using recursion.

sort_numbers

p_string: A string of numbers, separated by commas, to be sorted

,p_order: Sort sequence 'A' for ascending, all other values Descending.

Returns a string containing the sorted list of numbers.

num_to_alphanumeric

p_number: A positive integer to be converted.

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

p_number: A positive integer to be converted.

,p_range: Number between 1 and 26, representing range of alphabetic characters to use in code, e.g. 5 would use letters A to E.

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

alphatodec

p_code: A string containg the alphanumeric code, e.g. 'AA' for 27.

,p_range: Number between 1 and 26, representing range of alphabetic characters to use in code, e.g. 5 would use letters A to E.

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

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'.