Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Guoqiang.

Asked: December 25, 2002 - 11:14 am UTC

Last updated: October 20, 2015 - 1:22 am UTC

Version: 8.1.7

Viewed 50K+ times! This question is

You Asked

Tom,

Does Oracle have a function or easy way to convert hex to decimal or decimal to Hex ?
Thanks !

Guoqiang.

and Tom said...

select to_number( 'AA', 'xx' ) from dual;

hex to decimal

select to_char( 111, 'xxxx' ) from dual

decimal to hex

Rating

  (15 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

More

A reader, December 27, 2002 - 10:35 am UTC

Hi,

What about all Oct, Bin, hex and dec functions and
convert to each other? or where is the docu should we read.

Thanks


Tom Kyte
December 27, 2002 - 11:28 am UTC

more more more.... I hear that all of the time....

</code> http://asktom.oracle.com/~tkyte/hexdec/index.html <code>






Excellent

A reader, December 27, 2002 - 2:15 pm UTC


Thanks a lot!

Guoqiang, December 27, 2002 - 10:38 pm UTC

Oh,Oh,Oh,Tom,

So easy.

And,Does have the same way to covert decimal to bin or oct?

Guoqiang, December 27, 2002 - 10:43 pm UTC

Sorry for not refresh to see others response.

Thanks!

conversion to binary and octal

Laurent Schneider, August 02, 2004 - 10:59 am UTC

yes I can do it without pl/sql !

idea is to use the to_char for hex, then replace to binary, then to oct :-)

note that I did use one "regexp_replace" together with a lpad with 0 to make an entire number of groups of 3 bits before conversion to octal. Any pre-10g user will have to use a substr + concatenations.

Ok, here it is

select
to_char(empno, 'FM9999') DEC,
to_char(empno, 'FMXXXX') HEX,
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(to_char(empno, 'FMXXXX'),
'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') BIN,
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
regexp_replace(
lpad(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(to_char(empno, 'FMXXXX'),
'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'), -- BIN
CEIL(length(to_char(empno, 'FMXXXX'))*4/3)*3,
'0'
), '(...)', '\1-'),
'000-','0'),
'001-','1'),
'010-','2'),
'011-','3'),
'100-','4'),
'101-','5'),
'110-','6'),
'111-','7') OCT
from emp;



DEC HEX BIN OCT
----- ----- ---------------- ------
7369 1CC9 0001110011001001 016311
7499 1D4B 0001110101001011 016513
7521 1D61 0001110101100001 016541
7566 1D8E 0001110110001110 016616
7654 1DE6 0001110111100110 016746
7698 1E12 0001111000010010 017022
7782 1E66 0001111001100110 017146
7788 1E6C 0001111001101100 017154
7839 1E9F 0001111010011111 017237
7844 1EA4 0001111010100100 017244
7876 1EC4 0001111011000100 017304
7900 1EDC 0001111011011100 017334
7902 1EDE 0001111011011110 017336
7934 1EFE 0001111011111110 017376


I am sure you have all been looking for a base3 conversion in plain SQL

Laurent Schneider, August 10, 2004 - 6:53 am UTC

col b3 for 999999999999999999

select
x,
( select sum(power(10,rownum-1)*
trunc(mod(x,power(3,rownum))/power(3,rownum-1)))
from ( select null from dual group by cube(1,2,3,4,5,6,7))
where rownum < ln(x)/ln(3)+1 ) B3
from (select 123456789 x from dual);

X B3
---------- -------------------
123456789 22121022020212200


and the reverse function : oct 1234567 in decimal

Laurent Schneider, August 10, 2004 - 9:52 am UTC

select sum(power(8,rownum-1)*substr(1234567,length(1234567)
-rownum+1,1)) "1234567" from all_objects where rownum <=
length(1234567)

1234567
----------
342391

Loading Hex data using sqlldr

Reader, October 12, 2004 - 11:57 pm UTC

I have a datafile from a legacy system which has data in hex.I want to load it into an Oracle table using sqlldr .
How can this be done ?


the structure on the legacy is
a char(2), b integer(4) ,c integer(4) ,d integer(4), e integer(4)

The table in oracle created by me looks like
a char(2),
b number,
c number ,
d number,
e number .

My control file which is given below does not work.

LOAD DATA
infile "test.dat" "fix 18"
badfile "test.bad"
discardfile "test.dis"
APPEND
INTO TABLE test
( A POSITION(1:2) char(2),
B POSITION(3:6) integer ,
C position(7:10) integer ,
D position(11:14) integer ,
E position(15:18) integer )


what should the control file look like ?

Thanks ,




Tom Kyte
October 13, 2004 - 8:09 am UTC

hex is 012...9ABCDEF

hex is a string. do you have a string in this file, or 4 byte binary integers.

binary integers, in 4bytes (or 2bytes) is not "hex"

0A

is hex.

Loading Hex data using sqlldr

reader, October 13, 2004 - 2:38 pm UTC

Thanks for the response. In the legacy file , the first field is a string(2 bytes) and the next 4 flds are 4 byte , binary Integers.
What is the control file supposed to look like, in this case ?


Thanks

Tom Kyte
October 13, 2004 - 3:20 pm UTC

ops$tkyte@ORA9IR2> !cat test.c
#include "stdio.h"
 
void main()
{
char * s = "abcd";
int    d = 42;
FILE   * output;
 
        output = fopen( "test.dat", "w" );
        if (output == NULL)
        {
                printf( "output null\n" );
                exit(1);
        }
        fwrite( s, 4, 1, output );
        fwrite( &d, 4, 1, output);
        fclose(output);
}
 
<b>that creates a file with a 4 byte string and a 4 byte integer...</b>

ops$tkyte@ORA9IR2> !./test
 

ops$tkyte@ORA9IR2> desc t;
 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 X                                                 VARCHAR2(4)
 Y                                                 NUMBER
 
ops$tkyte@ORA9IR2> !cat t.ctl
LOAD DATA
INFILE test.dat
INTO TABLE t
append
(x position(1:4),
 y position(5:8) integer
)
 
ops$tkyte@ORA9IR2> !sqlldr / t
 
SQL*Loader: Release 9.2.0.5.0 - Production on Wed Oct 13 14:57:28 2004
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
Commit point reached - logical record count 1
 
ops$tkyte@ORA9IR2> select * from t;
 
X             Y
---- ----------
abcd         42
 

<b>that is all you need -- you look "done"</b>
 

Binary Integer

Reader, October 14, 2004 - 12:22 am UTC

Thanks a lot, I was able to load the above file successfully.
But now I have to deal with a few more datatypes.
In the legacy file, I am geting the following types:
1) Single byte Integer
2) 2-Byte Integers
3) Double Precision Floating Points

In my control file , I have used the following formats for the above 3 flds respectively

A position(1:1) integer ,
B position(2:3) integer ,
C position(4:11) decimal


I get the following warnings and errors

SQL*Loader-307: Warning: conflicting lengths 1 and 4 specified for column A table TEST2
SQL*Loader-307: Warning: conflicting lengths 2 and 4 specified for column B table TEST2

Record 1: Rejected - Error on table TEST2, column C.
Invalid zoned decimal nibble.

In my Table, I have used the following datatypes
Col A Integer
Col B Integer
Col C Number

Appreciate your help.

Thanks



Tom Kyte
October 14, 2004 - 9:34 am UTC

<b>sqlldr is fully documented in the UTILITIES guide -- the datatypes, how to use them and all is there...</b>


ops$tkyte@ORA9IR2> set linesize 70
ops$tkyte@ORA9IR2> desc t
 Name                                Null?    Type
 ----------------------------------- -------- ------------------------
 A                                            NUMBER
 B                                            NUMBER
 C                                            NUMBER
 
ops$tkyte@ORA9IR2> !cat test.c
#include "stdio.h"
 
void main()
{
FILE * output;
char   a = 42;
short  b = 55;
double c = 123.456;
 
        output = fopen("test.dat","w");
        fwrite( &a, sizeof(a), 1, output );
        fwrite( &b, sizeof(b), 1, output );
        fwrite( &c, sizeof(c), 1, output );
        fclose(output);
}
 
ops$tkyte@ORA9IR2> !cat t.ctl
LOAD DATA
INFILE test.dat
INTO TABLE t
replace
(
a  position(1:1) integer(1),
b  position(2:3) integer(2),
c  position(4:11) double
)
 
ops$tkyte@ORA9IR2> !sqlldr / t
 
SQL*Loader: Release 9.2.0.4.0 - Production on Thu Oct 14 08:12:40 2004
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
Commit point reached - logical record count 1
 
ops$tkyte@ORA9IR2> select * from t;
 
         A          B          C
---------- ---------- ----------
        42         55    123.456
 
ops$tkyte@ORA9IR2>
 

Block_id from hex address

Swapnil kambli, January 16, 2012 - 6:10 am UTC

Hi Tom,

I did below to find Hex address of the block:-

select dbms_utility.make_data_block_address (4,187) from dual;
DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS(4,187)
-------------------------------------------
16777403



select to_char( 16777403, 'xxxxxxxx' ) from dual;
TO_CHAR(1
---------
10000bb

Now I want to do reverse of this to find file_id and block_id.How can I achieve this.

I am doing this to get block id from a header block dump.

Thanks in advance.
Tom Kyte
January 17, 2012 - 2:23 pm UTC

ops$tkyte%ORA11GR2> select mdba,
  2         hmdba,
  3         dbms_utility.data_block_address_block( to_number(hmdba, 'xxxxxxxxxxx') ) block,
  4             dbms_utility.data_block_address_file( to_number(hmdba,'xxxxxxxxxxx')  ) "FILE"
  5    from (
  6  select mdba, to_char( mdba, 'xxxxxxxxxx' ) hmdba
  7    from (
  8  select dbms_utility.make_data_block_address (4,187) mdba
  9    from dual
 10         )
 11         )
 12  /

      MDBA HMDBA            BLOCK       FILE
---------- ----------- ---------- ----------
  16777403     10000bb        187          4


Swapnil kambli, January 16, 2012 - 8:00 am UTC

Got it!!!

select to_char( 16777403, 'xxxxxxxx' ) from dual;

SELECT DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(16777403),
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(16777403)
FROM dual;

Still Available?

Earl Lewis, October 19, 2015 - 7:56 pm UTC

Is the article/code that is linked to here https://asktom.oracle.com/~tkyte/hexdec/index.html still available? And I suppose more important, is it still relevant given the latest releases of Oracle DB?
Chris Saxon
October 20, 2015 - 1:22 am UTC

Super easy - mark of a genius

MK, December 09, 2015 - 4:23 pm UTC

Thanks very much, went through a number of different conversions but yours is the only one that is working + it's very simple