oracle 如何在oracle表中插入Hex数据？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17080589/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to insert Hex data into the oracle table?
提问by fen1ksss
I have the following table:
我有下表：
create table o_newstdata1 (
field1 raw(8)
);
the following input.data file:
以下 input.data 文件：
0x12345678
0x1234
0x12
how can I insert such a data into my o_newstdata1 table?
如何将这样的数据插入到我的 o_newstdata1 表中？
I've tried to load it as a varchar data:
我试图将它加载为 varchar 数据：
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?
但它不起作用，有什么建议吗？
回答by wolφi
There are possibly two sources of errors:
可能有两个错误来源：
- Loading raw data with SQL*Loader
- Querying the raw data
- 使用 SQL*Loader 加载原始数据
- 查询原始数据
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 RAWTO VARCHAR2, version 2 explicit conversion:
您不需要转换为原始格式，您可以直接使用 Oracle 的十六进制格式。版本 1 使用从RAWTO 的隐式转换VARCHAR2，版本 2 显式转换：
SELECT * FROM o_newstdata1 WHERE field1 = '12AB';
SELECT * FROM o_newstdata1 WHERE rawtohex(field1)='12AB';
回答by David Aldridge
Should the datatype not be specified as RAW?
不应该将数据类型指定为 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.
当原始二进制数据“按原样”加载到 RAW 数据库列中时，Oracle 数据库不会对其进行转换。如果将其加载到 CHAR 列中，则 Oracle 数据库会将其转换为十六进制。它不能加载到日期或数字列中。
该字段的长度是控制文件中指定的字节数。此长度仅受数据库中目标列的长度和内存资源的限制。长度始终以字节为单位，即使数据文件使用字符长度语义也是如此。RAW 数据字段不能被分隔。
回答by Beryllium
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 representa number (a notation). So I would just store it as a numeric, and put a viewon top of it, so you can see/query hex values directly.
十六进制只是表示数字（符号）的一种方式。所以我会把它存储为一个数字，并在它上面放一个视图，这样你就可以直接查看/查询十六进制值。
This way you could even add or compare these values in the database.
这样您甚至可以在数据库中添加或比较这些值。
