 |
|
Special
Functions Used to Convert Numbers
Oracle Tips by
Burleson
|
The following functions aren’t provided by Oracle, but I’ve chosen
to include them because they are useful in certain situations or are
cleverly written examples of the types of work that a function can do.
Converting
Numbers Between Bases
Projects that involve the conversion of data from legacy systems
may have to deal with numerical data in a non-decimal base. The stored
functions described in this section were designed to handle these
conversions.
Author’s
Note: The following
functions were contributed by Shawn Ramsey of AT&T Wireless Services
in Seattle,
Washington. Many thanks to Shawn for
allowing me to include these functions here.
Converting Base10 Numbers to Another Base
The Decimal_2_Any_Base() function converts numbers from
base10 to any base between base2 and base36 (inclusive). Listing A.1
shows the complete source code for the function.
Listing A.1 The Decimal_2_Any_Base() function.
FUNCTION Decimal_2_Any_Base
(p_DecNo IN integer,
p_Base IN integer)
RETURN varchar2 IS
-- *****************************************************************
-- DESCRIPTION: Takes a decimal number and converts the base to
-- any base from 2 to 36
--
-- Parameters: p_DecNo = Decimal Number to convert.
-- p_Base = Base to convert the number to.
--
-- Returns: Character string of the number converted to the
-- desired base. NULL if invalid conversion or
-- a conversion error occurs.
--
-- AUTHOR: Shawn M. Ramsey
--
-- REVISION HISTORY
-- Date Reviser Change
-- ----------------------------------------------------------------
-- 28 JAN 1997 S. Ramsey Function creation.
--
-- *****************************************************************
---- Characters for conversion
v_AnyNo varchar2(36) := '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
---- Variables for conversion
n_DecNo integer := p_DecNo;
n_Base integer := p_Base;
v_Return varchar2 (100) := NULL;
n_Cnt integer := 0;
n_Val integer := 0;
BEGIN
---- Check Base Number and Number to convert
IF (NOT n_Base BETWEEN 2 AND 36) OR (n_DecNo < 0) THEN
RETURN NULL;
ELSIF (n_DecNo = 0) THEN
RETURN '0';
END IF;
---- Initialize Variables
v_Return := '';
n_Cnt := 0 ;
---- Loop and convert
WHILE (n_DecNo <> 0) LOOP
n_Val := mod (n_DecNo,
(n_Base**(n_Cnt+1)))/(n_Base**n_cnt);
n_DecNo := n_DecNo - (n_Val * (n_Base**n_Cnt));
v_Return := substr (v_AnyNo, n_Val+1, 1) || v_Return;
n_Cnt := n_Cnt + 1;
END LOOP;
RETURN v_Return;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;
Converting Numbers Of Odd Bases To Base10
The Any_2_Base10() function can be used to convert a number
in any base between base2 and base36 to its base10 equivalent. Listing
A.2 shows the complete source code for the function.
Listing A.2 The Any_2_Base10() function.
FUNCTION ANY_2_Base10()
(p_Conv IN varchar2,
p_Base IN integer)
RETURN varchar2 IS
-- *****************************************************************
-- DESCRIPTION: Takes a number in any base from 2 to 36 and
-- converts it to an integer number.
--
-- Parameters: p_Conv = Number to convert.
-- p_Base = Base to convert the number from.
--
-- Returns: Number converted to the decimal. NULL if invalid
-- conversion or a conversion error occurs.
--
-- AUTHOR: Shawn M. Ramsey
--
-- REVISION HISTORY
-- Date Reviser Change
-- ----------------------------------------------------------------
-- 28 JAN 1997 S. Ramsey Function creation.
--
-- *****************************************************************
---- Characters for conversion
v_AnyNo varchar2 (36) := '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
---- Exception
e_BadData exception;
---- Variables for conversion
v_Conv varchar2 (200) := upper (p_Conv);
v_ValChar varchar2 (200) := NULL;
n_Base integer := p_Base;
n_Return integer := NULL;
n_Cnt integer := 0;
n_Val integer := 0;
BEGIN
---- Check Base Number and Number to convert
IF (NOT n_Base BETWEEN 2 AND 36) OR (v_Conv IS NULL) THEN
RETURN NULL;
END IF;
---- Initialize Variables
n_Return := 0;
n_Cnt := 0;
v_ValChar := substr (v_AnyNo, 1, n_Base);
---- Validate String
FOR n_Cnt IN 1..length (v_Conv) LOOP
IF (v_ValChar NOT LIKE '%' ||
substr(v_Conv, n_Cnt, 1) || '%') THEN
RAISE e_BadData;
END IF;
END LOOP;
---- Loop and convert
FOR n_Cnt IN 1..length (v_Conv) LOOP
IF (v_ValChar NOT LIKE '%' ||
substr (v_Conv, n_Cnt, 1) || '%') THEN
RAISE e_BadData;
END IF;
END LOOP;
---- Convert Number
FOR n_Cnt IN REVERSE 1..length (v_Conv) LOOP
n_Val := instr (v_ValChar, substr (v_Conv, n_Cnt, 1)) - 1;
n_Return := n_Return + (n_Val *
(n_Base**(length (v_Conv) - n_Cnt)));
END LOOP;
RETURN n_Return;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;
Is_Number()
The Is_Number() function is a relatively simple function
that depends on an exception being raised to work properly. A function
like this can be useful when data occasionally contains characters
mixed with numerals. Listing A.3 gives a complete listing for the
function.
Listing A.3 The Is_Number() function.
FUNCTION Is_Number (vValue IN number) RETURN boolean
IS
nValue number;
BEGIN
nValue := to_number (vValue);
RETURN TRUE;
EXCEPTION
WHEN INVALID_NUMBER THEN
RETURN FALSE;
END Is_Number;
Summary
This has been a discussion of the most commonly used SQL and PL/SQL
functions. There are several more functions that haven’t been
discussed, but these functions aren’t commonly encountered. If you
encounter a call to a function that you don’t recognize and you can’t
locate the source code, it might be a function that wasn’t discussed
here. Your best bet is to check for the function in the PL/SQL
User’s Guide and Reference provided by Oracle.
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor. |
