How to insert Hex data into the oracle table?

Question

I have the following table:

create table o_newstdata1 (
field1 raw(8)
);

the following input.data file:

0x12345678
0x1234
0x12

how can I insert such a data into my o_newstdata1 table?

I've tried to load it as a varchar data:

load data
INFILE '/export/home/mine/input.data'
replace
into table o_newstdata1
trailing nullcols
(
field1 varchar (8)
)

And queried something like this:

select field1 from o_newstdata1 where utl_raw.cast_to_raw(field1) like '0x1234';

but it doesn't work, any suggestions?

Solution 1

There are possibly two sources of errors:

1. Loading raw data with SQL*Loader
2. Querying the raw data

To check if the querying part works, use something like:

CREATE TABLE o_newstdata1 (field1 RAW(8)); 
INSERT INTO o_newstdata1(field1) VALUES ('12AB');
INSERT INTO o_newstdata1(field1) VALUES ('34EF');

You don't need to cast to a raw, you can use Oracle's hex format directly. Version 1 uses implicit conversion from RAW TO VARCHAR2, version 2 explicit conversion:

SELECT * FROM o_newstdata1 WHERE field1 = '12AB';
SELECT * FROM o_newstdata1 WHERE rawtohex(field1)='12AB'; 

Solution 2

If your data is not really binary/raw (as indicated by the size of the column), but just a number, you could store it as a number.

Hex is just one way to represent a number (a notation). So I would just store it as a numeric, and put a view on top of it, so you can see/query hex values directly.

This way you could even add or compare these values in the database.

Solution 3

Should the datatype not be specified as RAW?

From the docs:

When raw, binary data is loaded "as is" into a RAW database column, it is not converted by the Oracle database. If it is loaded into a CHAR column, then the Oracle database converts it to hexadecimal. It cannot be loaded into a DATE or number column.

The length of this field is the number of bytes specified in the control file. This length is limited only by the length of the target column in the database and by memory resources. The length is always in bytes, even if character-length semantics are used for the data file. RAW data fields cannot be delimited.

Comments

[WiiMaxx]

which part doesn't work, what exceptions do you get?

[David Aldridge]

What did the SQL*Loader log file say?

[fen1ksss]

sqlfiddle.com/#!4/91610/3

[David Aldridge]

Your demo has nothing to do with SQL*Loader, and if you remove the predicate from the query you see that the rows were inserted successfully.

[wolφi]

Can you change the hex-format from 0x1234ABCD to 1234ABCD?

[fen1ksss]

how can I then query a data from that char column as hex?