Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

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.

††
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracleģ is the registered trademark of Oracle Corporation. 

Hit Counter