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
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 ,
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
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
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>
Hex/Octal/binary/deca conversions using SQL
Frank Zhou, March 03, 2008 - 3:42 pm UTC
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.
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
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