converting to different bases

Hi,
on a recent hunt ot convert 1 base to another, I found this 'GEM' on the oracle best routines website.

unfortunatly Mr, ploeg appears to be a bit of a sloppy programmer/documentor, ( as is oracle for not testing it)

1.in that BASE2BASE(33,10,2,8), which should convert a decimal to a binary number 8 long, actually returns 0011. 2. BASE2BASE(32,10,2,8) actually returns 000100000 (9 digits) infact the routine always returns 1 more than the length you want returned.

would anybody care to fix these 2 bugs or improve the following routine.

one of the bugs that returns 0011, is actually de to the line:  IF p_length < 1 OR l_valin = 1 THEN

since l_valin = 1 evaluates to true as soon as the 32 has been dealt with, exiting the code and failing to insert '000'

• Mr Ploeg's routine follows******

Converting a Number from Any Base to Any Base

        This code comes from Jack Ploeg, a developer with Logica in Groningen, The Netherlands.

        This function converts a number from any base to any base (both between 2 and 36). You call it with 3

        parameters: the value to be converted, the base of this value and te desired base for the returned value.

        Example:

        SQL> select base2base('65af',16,10) from dual;

        BASE2BASE('65AF',16,10)
        --------------------------------------
        026031

        SQL> select base2base('xyz',36,2) from dual;

        BASE2BASE('XYZ',36,2)
        --------------------------------------
        01010101111111011 

         


        Script: CREATE OR REPLACE FUNCTION base2base( p_valin IN
VARCHAR2


                                            , p_basein IN NUMBER


                                            , p_baseout IN NUMBER


                                            , p_length IN PLS_INTEGER


default null)
        RETURN VARCHAR2 is

        l_digitstr VARCHAR2(36) :=
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
        l_length   PLS_INTEGER;
        l_tmp      NUMBER;
        l_valin    NUMBER :=0;
        l_cvalin   VARCHAR2(200) := UPPER(p_valin);
        l_pos      PLS_INTEGER;

        BEGIN
           IF p_basein > 36
           OR p_baseout > 36
           OR p_basein < 2
           OR p_baseout < 2 then
              RETURN NULL;
           ELSE
              IF p_basein <> 10 THEN
                 -- convert to decimal, to enable standard calulation
functions
                 FOR t IN 1 .. LENGTH(l_cvalin) LOOP
                    l_valin := l_valin * p_basein;
                    l_pos := INSTR(l_digitstr, SUBSTR(l_cvalin,t,1)) -1;
                    IF l_pos >= p_basein THEN
                       RETURN NULL;
                    ELSE
                       l_valin := l_valin + l_pos;
                    END IF;
                 END LOOP;
              ELSE
                 l_valin := p_valin;
              END IF;
              IF p_length < 1 OR l_valin = 1 THEN
              -- this is the last digit to be converted
                 RETURN(SUBSTR(l_digitstr, l_valin + 1, 1));
              ELSE
                 -- calculate lengt needed if not passed as parameter
                 IF p_length IS NOT NULL THEN
                    l_length := p_length;
                 ELSE
                    l_length := CEIL(LOG(p_baseout, l_valin));
                 END IF;
                    -- compute first digit, pass remainder to calculate
rest of digits
                    l_tmp := POWER(p_baseout, l_length);
                    RETURN(SUBSTR(l_digitstr, FLOOR(l_valin / l_tmp) +
1, 1) ||base2base(MOD(l_valin,l_tmp), 10, p_baseout,l_length -1));
              end if;
           end if;
        end;