Appendix C.3 Foreign DB: ORACLE

Tables C-5 to C-7 show data type correspondence (foreign DB: ORACLE).

Table C-5 Data type correspondence (foreign DB: ORACLE) (1/3)

Data type of ORACLE
(Foreign DB)
Data type of HiRDB
SIINTDEC(m,n)
1 [Figure] m [Figure] 29, 0 [Figure] n [Figure] m
SFLT or REAL1FLT or DP1CH(n)
1 [Figure] n [Figure] 255
NUMBER(p,s)
p: Precision
s: Scaling
s = 0 or omittedp = * or omitted9M2R3M6, 7

MM
s = 0, omitted, or -28 [Figure] s[Figure] -11 [Figure] p - s[Figure] 4R4M4M6, 7

MM
5 [Figure] p - s[Figure] 9

R5M6, 7

MM
10 [Figure] p - s[Figure] 29

R6, 7

MM
30 [Figure] p - s[Figure] 38

MM
1 [Figure] s[Figure] 291 [Figure] p[Figure] 29

R7, 8

MM
p[Figure] 30

MM
s[Figure] 30 or s[Figure] - 29

M

M
CHAR(n)1 [Figure] n[Figure] 255

R1, 6

256 [Figure] n[Figure] 2,000

VARCHAR2(n)1 [Figure] n[Figure] 255

256 [Figure] n[Figure] 4,000

NCHAR(n) (2-byte fixed-width national character set)1 [Figure] n[Figure] 127

128 [Figure] n[Figure] 1,000

NCHAR(n) (Variable-width national character set)1 [Figure] n[Figure] 255

256 [Figure] n[Figure] 2,000

NVARCHAR2(n) (2-byte fixed-width national character set)1 [Figure] n[Figure] 127

128 [Figure] n[Figure] 2,000

NVARCHAR2(n) (Variable-width national character set)1 [Figure] n[Figure] 255

256 [Figure] n[Figure] 4,000

LONG(n)1 [Figure] n[Figure] 255

256 [Figure] n[Figure] 32,000

32,001 [Figure] n[Figure] 2,000,000,000

DATE

D4, 6
RAW(n)1 [Figure] n[Figure] 255

256 [Figure] n[Figure] 2,000

RAW(n)1 [Figure] n[Figure] 255

256 [Figure] n[Figure] 2,000

LONG RAW(n)1 [Figure] n[Figure] 255

256 [Figure] n[Figure] 32,000

32,001 [Figure] n[Figure] 2,000,000,000

ROWID

MLSLABEL

CLOB(n)1 [Figure] n[Figure] 1,999,999,999

2,000,000,000 [Figure] n[Figure] 4,000,000,000

NCLOB(n)1 [Figure] n[Figure] 1,999,999,999

2,000,000,000 [Figure] n[Figure] 4,000,000,000

BLOB(n)1 [Figure] n[Figure] 1,999,999,999

2,000,000,000 [Figure] n[Figure] 4,000,000,000

Legend:
R: Recommended data type
M: Mappable data type
D: This is a mappable data type if Y is specified in the pd_hb_use_describe operand of the foreign server information definition.
Blank: Not mappable data type
SI: SMALLINT
INT: INTEGER
DEC: DECIMAL
SFLT: SMALLFLT
FLT: FLOAT
DP: DOUBLE PRECISION
CH: CHAR
1 The precision of values that can be represented may be reduced. The scope and precision of values on HiRDB are hardware-dependent.
2 The allowable range is limited to between -32,768 and 32,767.
3 The allowable range is limited to between -2,147,483,648 and 2,147,483,647.
4 The allowable range is limited to between -9,999 and 9,999.
5 The allowable range is limited to between -999,999,999 and 999,999,999.
6 If s < 0, let DECIMAL(m,n) be DECIMAL(p - s,0). In this case, the lower (-s) digit is 0. If s = 0 or the operand is omitted, let DECIMAL(m,n) be DECIMAL(p,0).
Example: If NUMBER(4,-3)=nnnn000, it will be DECIMAL(7,0) under HiRDB.
7 If the number of digits in the integer part is less than the data type of HiRDB, rows cannot be acquired from the foreign server.
If the number of digits in the fractional part is less than the data type of HiRDB, data is acquired by truncating any excess fractional part to match the number of digits in the fractional part of the data type of HiRDB.
If the number of digits in the integer or fractional part is larger than the data type of HiRDB, the expanded part is zero-filled to match the number of digits in the data type of HiRDB, and data is acquired.
8 If p < s, let DECIMAL(m,n) be DECIMAL(s,s). In this case, the lower (s - p) digit will be 0. If p[Figure] s, let DECIMAL(m,n) be DECIMAL(p,s).
Example: If NUMBER(4,5)=0.01234, it is DECIMAL(5,5) under HiRDB.
9 The data type of HiRDB to be mapped should be determined depending on the intended application. When mapping to the DECIMAL type, specify the precision (m) and the scaling (n) for the foreign table definition according to the data precision (p) and the scaling (s) used on the foreign server.

Table C-6 Data type correspondence (foreign DB: ORACLE) (2/3)

Data type of ORACLE
(Foreign DB)
Data type of HiRDB
VCH (n)
1 [Figure] n [Figure] 255
MCH (n)
1 [Figure] n [Figure] 255
MVC (n)
1 [Figure] n [Figure] 255
NCH (n)
1 [Figure] n [Figure] 127
NVC (n)
1 [Figure] n [Figure] 127
DATE
NUMBER(p,s)
p: Precision
s: Scaling
s = 0 or omittedp = * or omitted

s = 0, omitted, or -28 [Figure] s[Figure] -11 [Figure] p - s[Figure] 4

5 [Figure] p - s[Figure] 9

10 [Figure] p - s[Figure] 29

30 [Figure] p - s[Figure] 38

1 [Figure] s[Figure] 291 [Figure] p[Figure] 29

p[Figure] 30

s[Figure] 30 or s[Figure] -29

CHAR(n)1 [Figure] n[Figure] 255

R2, 5

256 [Figure] n[Figure] 2,000

VARCHAR2(n)1 [Figure] n[Figure] 255R1, 5

R2, 5

256 [Figure] n[Figure] 4,000

NCHAR(n) (2-byte fixed-width national character set)1 [Figure] n[Figure] 127

R5

128 [Figure] n[Figure] 1,000

NCHAR(n) (Variable-width national character set)1 [Figure] n[Figure] 255

R3, 5

256 [Figure] n[Figure] 2,000

NVARCHAR2(n) (2-byte fixed-width national character set)1 [Figure] n[Figure] 127

R5
128 [Figure] n[Figure] 2,000

NVARCHAR2(n) (Variable-width national character set)1 [Figure] n[Figure] 255

R5
256 [Figure] n[Figure] 4,000

LONG(n)1 [Figure] n[Figure] 255

R1, 4, 5

R2, 4, 5
256 [Figure] n[Figure] 32,000

32,001 [Figure] n[Figure] 2,000,000,000

DATE

R1
RAW(n)1 [Figure] n[Figure] 255

256 [Figure] n[Figure] 2,000

LONG RAW(n)1 [Figure] n[Figure] 255

256 [Figure] n[Figure] 32,000

32,001 [Figure] n[Figure] 2,000,000,000

ROWID

MLSLABEL

CLOB(n)1 [Figure] n[Figure] 1,999,999,999

2,000,000,000 [Figure] n[Figure] 4,000,000,000

NCLOB(n)1 [Figure] n[Figure] 1,999,999,999

2,000,000,000 [Figure] n[Figure] 4,000,000,000

BLOB(n)1 [Figure] n[Figure] 1,999,999,999

2,000,000,000 [Figure] n[Figure] 4,000,000,000

Legend:
R: Recommended data type
Blank: Non-mappable data type
VCH: VARCHAR
MCH: MCHAR
MVC: MVARCHAR
NCH: NCHAR
NVC: NVARCHAR
Note
If a character type of 255 bytes is defined for columns with 256 bytes or greater on the foreign server, only up to 255 bytes of data can be acquired.
1 Data not containing double-byte characters is treated as the recommended data type.
2 Data containing double-byte characters is treated as the recommended data type.
3 The definition data length of columns in the foreign table should be n[Figure] 2.
4 Specifying an applicable column in any of the following locations may result in an error on the foreign DB:
  • GROUP BY clause
  • ORDER BY clause
  • DISTINCT operator
  • Functions
  • Expressions
  • Conditions
5 If there is no specification, make the definition length of the data type of the foreign server equal to that of HiRDB.
If the definition length (length = n bytes, n[Figure] 255) of the data type of HiRDB is less than the actual length of the data type of the foreign server, only the first n bytes are acquired.
If the definition length of the data type of HiRDB is greater, the data is inserted left-justified and any excess length is blank-filled (double-byte spaces for national character strings, single-byte spaces for character strings or mixed character strings), provided that HiRDB is of a fixed-length data type.
If HiRDB is of a variable-length data type, the data is acquired "as is."

Table C-7 Data type correspondence (foreign DB: ORACLE) (3/3)

Data type of ORACLE
(Foreign DB)
Data type of HiRDB
TIMETSTAMP(p)
p = 0, 2, 4, or 6
INTERVAL YEAR TO DAYINTERVAL HOUR TO SECONDBIN (n)
1 [Figure] n [Figure] 255
NUMBER(p,s)
p: Precision
s: Scaling
s = 0 or omittedp = * or omitted

s = 0, omitted, or -28 [Figure] s[Figure] -11 [Figure] p - s[Figure] 4

5 [Figure] p - s[Figure] 9

10 [Figure] p - s[Figure] 29

30 [Figure] p - s[Figure] 38

1 [Figure] s[Figure] 291 [Figure] p[Figure] 29

p[Figure] 30

s[Figure] 30 or s[Figure] -29

CHAR(n)1 [Figure] n[Figure] 255

256 [Figure] n[Figure] 2,000

VARCHAR2(n)1 [Figure] n[Figure] 255

256 [Figure] n[Figure] 4,000

NCHAR(n) (2-byte fixed-width national character set)1 [Figure] n[Figure] 127

128 [Figure] n[Figure] 1,000

NCHAR(n) (Variable-width national character set)1 [Figure] n[Figure] 255

256 [Figure] n[Figure] 2,000

NVARCHAR2(n) (2-byte fixed-width national character set)1 [Figure] n[Figure] 127

128 [Figure] n[Figure] 2,000

NVARCHAR2(n) (Variable-width national character set)1 [Figure] n[Figure] 255

256 [Figure] n[Figure] 4,000

LONG(n)1 [Figure] n[Figure] 255

256 [Figure] n[Figure] 32,000

32,001 [Figure] n[Figure] 2,000,000,000

DATE

R2

RAW(n)1 [Figure] n[Figure] 255

256 [Figure] n[Figure] 2,000

LONG RAW(n)1 [Figure] n[Figure] 255

R3, 4
256 [Figure] n[Figure] 32,000

32,001 [Figure] n[Figure] 2,000,000,000

ROWID

MLSLABEL

CLOB(n)1 [Figure] n[Figure] 1,999,999,999

2,000,000,000 [Figure] n[Figure] 4,000,000,000

NCLOB(n)1 [Figure] n[Figure] 1,999,999,999

2,000,000,000 [Figure] n[Figure] 4,000,000,000

BLOB(n)1 [Figure] n[Figure] 1,999,999,999

2,000,000,000 [Figure] n[Figure] 4,000,000,000

Legend:
R: Recommended data type
Blank: Not mappable data type
TSTAMP: TIMESTAMP
BIN: BINARY
1 Hour, minute, and second are truncated.
2 When mapping to the TIMESTAMP type, either specify 0 in the precision p or omit this operand.
3 When mapping to the BINARY type, specify a value greater than or equal to the actual length (in bytes) of the longest data element that is stored in ORACLE.
4 If there is no specification, make the definition length of the data type of the foreign server equal to that of HiRDB.
If the definition length (length = n bytes, n[Figure] 255) of the data type of HiRDB is less than the actual length of the data type of the foreign server, only the first n bytes are acquired.
If the definition length of the data type of HiRDB is greater, the data is inserted left-justified and any excess length is blank-filled (double-byte spaces for national character strings, single-byte spaces for character strings or mixed character strings), provided that HiRDB is of a fixed-length data type.
If HiRDB is of a variable-length data type, the data is acquired "as is."