在Oracle中查找数字子类型(smallint,int等) [英] Find numeric subtype (smallint, int, etc) in oracle
问题描述
我在oracle中的oracle中添加了一个带有字段的表-
I added a table in oracle in oracle with fields -
- smallint
- int
- bigint
但是它在内部将它们存储为decimal(22,0)
But it internally store them as decimal(22,0)
有什么方法可以获取单个子类型(例如smallint )而不是十进制.
Is there any way to get individual subtype (e.g. smallint) instead of decimal.
我的用例:
我正在使用sqoop将数据从oracle复制到蜂巢.我需要检查oracle表的列的元数据,并将自己的oracle写为配置单元数据类型映射.
I am using sqoop to copy data from oracle to hive. I need to check metadata of columns of oracle table and wrote own oracle to hive data type mapping.
但是我总是得到decimal(22,0)
,因此即使对于smallint,我也需要将所有这些列映射到hive表中的decimal(22,0)
.
But I am always getting decimal(22,0)
, so I need to map all these columns to decimal(22,0)
in hive table even for smallint.
推荐答案
数据类型SMALLINT
和INT
只是ANSI/ISO标准类型,在Oracle中为
The data type SMALLINT
and INT
are just ANSI/ISO standard types which, in Oracle, are aliases for NUMBER(38)
and the BIGINT
datatype does not exist.
如果要存储2字节,4字节和8字节的值,则可以结合使用大小适当的NUMBER
列和约束检查的组合,以确保数据在要求的范围内:
If you want to store 2-byte, 4-byte and 8-byte values then you can use a combination of appropriately sized NUMBER
columns and constraint checking to ensure the data is within the required bounds:
CREATE TABLE table_name (
small NUMBER( 5,0) CHECK ( small BETWEEN -POWER(2,15) AND +POWER(2,15)-1 ),
medium NUMBER(10,0) CHECK ( medium BETWEEN -POWER(2,31) AND +POWER(2,31)-1 ),
big NUMBER(19,0) CHECK ( big BETWEEN -POWER(2,63) AND +POWER(2,63)-1 )
);
INSERT INTO table_name VALUES ( -POWER(2,15), -POWER(2,31), -POWER(2,63) );
INSERT INTO table_name VALUES ( POWER(2,15)-1, POWER(2,31)-1, POWER(2,63)-1 );
然后:
SELECT * FROM table_name
输出:
SMALL MEDIUM BIG
------ ----------- --------------------
-32768 -2147483648 -9223372036854775808
32767 2147483647 9223372036854775807
这篇关于在Oracle中查找数字子类型(smallint,int等)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!