PL/SQL base conversion without functions

Question

Is there any way to convert decimal to binary, or binary to decimal, in Oracle 10g without having to first define a function? I have limited database access (SELECT only) and all the solutions for this I've found online seem to involve CREATE FUNCTION, which does not work for me.

Answer 1

Can you execute PL/SQL in an SQLPlus script like this:

declare
    procedure bin_2_dec(/*some parameters here*/) is
    begin
        /*do computation and print result*/
    end;
begin    
    bin_2_dec('11110000');
end;
/

I'm not sure but I don't think the function will be created permanently in the database, I think it will only exist temporarily for the duration of the script so this might work. It's worth a shot, right? ;)

Or if that doesn't work, you could SELECT ... from dual to convert, though that will probably be awkward and will only work if you know the number of digits - maybe (I'll try to throw it together if I can get a few minutes, and if it's possible).

Answer 2

If hexadecimal is good enough, then TO_CHAR and TO_NUMBER can work:

SQL> select to_char(31, '0x') from dual;

TO_
---
 1f

SQL> select to_number('1f', '0x') from dual;

TO_NUMBER('1F','0X')
--------------------
                  31

You may be able to use the RAWTOHEX() and HEXTORAW() functions to make the hex to binary transition as well.

Answer 3

Frank Zhou who specializes in gnarly SQL puzzlers has devised a pure SQL solution for this problem. You can find it on his OraQA site. But be warned: it is really gnarly.

Answer 4

A crude, but straight-forward solution for decimal-to-binary:

SELECT REPLACE
       (REPLACE
        (REPLACE
         (REPLACE
          (REPLACE
           (REPLACE
            (REPLACE
             (REPLACE
              (REPLACE
               (REPLACE
                (REPLACE
                 (REPLACE
                  (REPLACE
                   (REPLACE
                    (REPLACE
                     (REPLACE
                      (TO_CHAR (100,'FMxxx'),
                       '0','0000'),
                      '1','0001'),
                     '2','0010'),
                    '3','0011'),
                   '4','0100'),   
                  '5','0101'),
                 '6','0110'),
                '7','0111'),
               '8','1000'),
              '9','1001'),
             'A','1010'),
            'B','1011'),
           'C','1100'),
          'D','1101'),
         'E','1110'),
        'F','1111')
  FROM DUAL;

Binary-to-decimal would be trickier. You might be able to use connect by to split the string into 4-character segments, convert them in a similar fashion, then concatenate them back together (a second connect by using SYS_CONNECT_BY_PATH?), but that's a little too tedious for me to work out tonight.

---

On second thought, here's the Binary-to-decimal solution (I'm a sucker for connect by problems):

SELECT     TO_NUMBER(
               REPLACE (
                   SYS_CONNECT_BY_PATH (octet, '!'), 
                   '!', ''),
               'xxxxxx')
      FROM (SELECT     CASE SUBSTR
                                (LPAD (a,
                                       CEIL (LENGTH(a)/4)*4, '0'),
                                 (LEVEL-1)*4+1, 4)
                          WHEN '0000'
                             THEN '0'
                          WHEN '0001'
                             THEN '1'
                          WHEN '0010'
                             THEN '2'
                          WHEN '0011'
                             THEN '3'
                          WHEN '0100'
                             THEN '4'
                          WHEN '0101'
                             THEN '5'
                          WHEN '0110'
                             THEN '6'
                          WHEN '0111'
                             THEN '7'
                          WHEN '1000'
                             THEN '8'
                          WHEN '1001'
                             THEN '9'
                          WHEN '1010'
                             THEN 'A'
                          WHEN '1011'
                             THEN 'B'
                          WHEN '1100'
                             THEN 'C'
                          WHEN '1101'
                             THEN 'D'
                          WHEN '1110'
                             THEN 'E'
                          WHEN '1111'
                             THEN 'F'
                       END AS octet,
                       LEVEL AS seq,
                       CEIL (LENGTH(a)/4) AS max_level
                  FROM (SELECT '101010101010101010' AS a
                          FROM DUAL)
            CONNECT BY LEVEL <= CEIL(LENGTH(a)/4))
     WHERE LEVEL = max_level
CONNECT BY PRIOR seq = seq-1

This solution only works for one row at a time as currently written. To make it work with multiple rows, you'd need to add some sort of unique identifier to the outermost connect by.