Recipe14.7.Converting Whole Numbers to Binary Using Oracle

Recipe 14.7. Converting Whole Numbers to Binary Using Oracle

Problem

You want to convert a whole number to its binary representation on an Oracle system. For example, you would like to return all the salaries in table EMP in binary as part of the following result set:

 ENAME        SAL SAL_BINARY ---------- ----- -------------------- SMITH        800 1100100000 ALLEN       1600 11001000000 WARD        1250 10011100010 JONES       2975 101110011111 MARTIN      1250 10011100010 BLAKE       2850 101100100010 CLARK       2450 100110010010 SCOTT       3000 101110111000 KING        5000 1001110001000 TURNER      1500 10111011100 ADAMS       1100 10001001100 JAMES        950 1110110110 FORD        3000 101110111000 MILLER      1300 10100010100 

Solution

This solution makes use of the MODEL clause, so you'll need to be running Oracle Database 10g or later for it to work. Because of MODEL's ability to iterate and provide array access to row values, it is a natural choice for this operation (assuming you are forced to solve the problem in SQL, as a stored function is more appropriate here). Like the rest of the solutions in this book, even if you don't find a practical application for this code, focus on the technique. It is useful to know that the MODEL clause can perform procedural tasks while still keeping SQL's set-based nature and power. So, even if you find yourself saying: "I'd never do this in SQL," that's fine. I'm in no way suggesting you should or shouldn't. I only remind you to focus on the technique, so you can apply it to whatever you consider a more "practical" application.

The following solution returns all ENAME and SAL from table EMP, while calling the MODEL clause in a scalar subquery (this way it serves as sort of a standalone function from table EMP that simply receives an input, processes it, and returns a value, much like a function would):

  1 select ename,  2        sal,  3        (  4        select bin  5          from dual  6         model  7         dimension by ( 0 attr )  8         measures ( sal num,  9                    cast(null as varchar2(30)) bin, 10                   '0123456789ABCDEF' hex 11                  ) 12         rules iterate (10000) until (num[0] <= 0) ( 13           bin[0] = substr(hex[cv()],mod(num[cv( )],2)+1,1)||bin[cv( )], 14           num[0] = trunc(num[cv( )]/2) 15         ) 16        ) sal_binary 17   from emp 

Discussion

I mentioned in the "Solution" section that this problem is most likely better solved via a stored function. Indeed, the idea for this recipe came from a function. As a matter of fact, this recipe is an adaptation of a function called TO_BASE, written by Tom Kyte of Oracle Corporation. Like other recipes in this book that you may decide not to use, even if you do not use this recipe it does a nice job of showing of some of the features of the MODEL clause such as iteration and array access of rows.

To make the explanation easier, I am going to focus on a slight variation of the subquery containing the MODEL clause. The code that follows is essentially the subquery from the solution, except that it's been hard-wired to return the value 2 in binary:

  select bin   from dual  model  dimension by ( 0 attr )  measures ( 2 num,             cast(null as varchar2(30)) bin,             '0123456789ABCDEF' hex           )  rules iterate (10000) until (num[0] <= 0) (    bin[0] = substr (hex[cv()],mod(num[cv( )],2)+1,1)||bin[cv( )],    num[0] = trunc(num[cv( )]/2)  ) BIN ---------- 10 

The following query outputs the values returned from one iteration of the RULES defined in the query above:

  select 2 start_val,        '0123456789ABCDEF' hex,        substr('0123456789ABCDEF',mod(2,2)+1,1) ||        cast(null as varchar2(30)) bin,        trunc(2/2) num   from dual START_VAL HEX              BIN        NUM --------- ---------------- ---------- ---         2 0123456789ABCDEF 0            1 

START_VAL represents the number you want to convert to binary, which in this case is 2. The value for BIN is the result of a substring operation on '0123456789ABCDEF' (HEX, in the original solution). The value for NUM is the test that will determine when you exit the loop.

As you can see from the preceding result set, the first time through the loop BIN is 0 and NUM is 1. Because NUM is not less than or equal to 0, another loop iteration occurs. The following SQL statement shows the results of the next iteration:

  select num start_val,        substr('0123456789ABCDEF',mod(1,2)+1,1) || bin bin,        trunc(1/2) num   from ( select 2 start_val,        '0123456789ABCDEF' hex,        substr('0123456789ABCDEF',mod(2,2)+1,1) ||        cast(null as varchar2(30)) bin,        trunc(2/2) num   from dual        ) START_VAL BIN        NUM --------- ---------- ---         1 10           0 

The next time through the loop, the result of the substring operation on HEX returns 1 and the prior value of BIN, 0, is appended to it. The test, NUM, is now 0, thus this is the last iteration and the return value "10" is the binary representation of the number 2. Once you're comfortable with what's going on, you can remove the iteration from the MODEL clause and step through it row by row to follow how the rules are applied to come to the final result set, as is shown below:

  select 2 orig_val, num, bin   from dual  model  dimension by ( 0 attr )  measures ( 2 num,             cast(null as varchar2(30)) bin,            '0123456789ABCDEF' hex           )  rules (    bin[0] = substr (hex[cv()],mod(num[cv( )],2)+1,1)||bin[cv( )],    num[0] = trunc(num[cv( )]/2),    bin[1] = substr (hex[0],mod(num[0],2)+1,1)||bin[0],    num[1] = trunc(num[0]/2)  ) ORIG_VAL NUM BIN -------- --- ---------        2   1 0        2   0 10