Data type mappings between OpenText Analytics Database and Oracle

Oracle uses proprietary data types for all main data types, such as VARCHAR, INTEGER, FLOAT, DATE.

Oracle uses proprietary data types for all main data types, such as VARCHAR, INTEGER, FLOAT, DATE. Before migrating a database from Oracle to OpenText™ Analytics Database, first convert the schema to minimize errors and time spent fixing erroneous data issues.

The following table compares the behavior of Oracle data types to OpenText™ Analytics Database data types.

Oracle OpenText™ Analytics Database Notes

NUMBER

(no explicit precision)

 INTEGER

In Oracle, the NUMBER data type with no explicit precision stores each number N as an integer M, together with a scale S. The scale can range from -84 to 127, while the precision of M is limited to 38 digits. Thus:

N = M * 10^S

When precision is specified, precision/scale applies to all entries in the column. If omitted, the scale defaults to 0.

For the common case—Oracle NUMBER with no explicit precision used to store only integer values—the database INTEGER data type is the most appropriate and the fastest equivalent data type. However, INTEGER is limited to a little less than 19 digits, with a scale of 0: [-9223372036854775807, +9223372036854775807].

NUMERIC

If an Oracle column contains integer values outside of the range [-9223372036854775807, +9223372036854775807], then use the database data type NUMERIC(p,0) where p is the maximum number of digits required to represent values of the source data.

If the data is exact with fractional places—such as dollars, OpenText recommends that you use NUMERIC(p,s) where p is the precision (total number of digits) and s is the maximum scale (number of decimal places).

The database conforms to standard SQL, which requires that p ≥ s and s ≥ 0. The NUMERIC data type is most effective for p=18 and increasingly expensive for p=37, 58, 67, etc., where p ≤ 1024.

FLOAT Even though no explicit scale is specified for an Oracle NUMBER column, Oracle allows non-integer values, each with its own scale. If the data stored in the column is approximate, OpenText recommends that you use the FLOAT data type, which is standard IEEE floating point, like ORACLE BINARY_DOUBLE.

NUMBER(P,0)

P ≤ 18

 INTEGER For Oracle NUMBER data types with 0 scale and a precision less than or equal to 18, use the OpenText™ Analytics Database INTEGER data type.

NUMBER(P,0)

P > 18

 NUMERIC(p,0) In the rare case where a Oracle column specifies precision greater than 18, use the OpenText™ Analytics Database data type NUMERIC(p, 0), where p = P.

NUMBER(P,S)

All cases other than above

 NUMERIC(p,s)
  • When P ≥ S and S ≥ 0, use p = P and s = S, unless the data allows reducing P or using FLOAT as discussed above.
  • If S > P, use p = S, s = S.
  • If S < 0, use p = P – S, s = 0.
FLOAT
NUMERIC(P,S) Rarely used in Oracle, see notes for Oracle NUMBER.
DECIMAL(P,S) Synonym for Oracle NUMERIC.
BINARY_FLOAT FLOAT Same as FLOAT(53) or DOUBLE PRECISION
BINARY_DOUBLE FLOAT Same as FLOAT(53) or DOUBLE PRECISION
RAW VARBINARY

Maximum sizes compared:

  • Oracle RAW data type: 2000 bytes

  • OpenText™ Analytics Database VARBINARY: 65000 bytes

LONG RAW LONG VARBINARY

Maximum sizes compared:

  • Oracle’s LONG RAW is 2GB

  • OpenText™ Analytics Database LONG VARBINARY is 32M bytes/octets (~30MB)

CHAR(n) CHAR(n)

Maximum sizes compared:

  • Oracle CHAR: 2000 bytes

  • OpenText™ Analytics Database CHAR : 65000 bytes

NCHAR(n) CHAR(*n**3) OpenText™ Analytics Database supports national characters with CHAR(n) as variable-length UTF8-encoded UNICODE character string. UTF-8 represents ASCII in 1 byte, most European characters in 2 bytes, and most oriental and Middle Eastern characters in 3 bytes.
VARCHAR2(n) VARCHAR(n)

Maximum sizes compared:

  • Oracle VARCHAR2: 4000 bytes

  • OpenText™ Analytics Database VARCHAR: 65000 bytes

NVARCHAR2(n) VARCHAR(*n**3) See notes for NCHAR.
DATE TIMESTAMP Oracle’s DATE is different from the SQL standard DATE data type implemented by OpenText™ Analytics Database. Oracle’s DATE includes the time (no fractional seconds), while OpenText™ Analytics Database DATE data types include only date as per the SQL standard.
DATE
TIMESTAMP TIMESTAMP TIMESTAMP defaults to six places—that is, to microseconds.
TIMESTAMP WITH TIME ZONE TIMESTAMP WITH TIME ZONE TIME ZONE defaults to the currently SET or system time zone.
INTERVAL YEAR TO MONTH INTERVAL YEAR TO MONTH As per the SQL standard, you can qualify OpenText™ Analytics Database INTERVAL data types with the YEAR TO MONTH subtype.
INTERVAL DAY TO SECOND INTERVAL DAY TO SECOND The default subtype for OpenText™ Analytics Database INTERVAL data types is DAY TO SECOND.
CLOB LONG VARCHAR

You can store a CLOB (character large object) or BLOB (binary large object) value in a table or in an external location. The maximum size of a CLOB or BLOB is 128 TB.

You can store OpenText™ Analytics Database LONG data types only in LONG VARCHAR and LONG VARBINARY columns. The maximum size of LONG data types is 32M bytes.

BLOB LONG VARBINARY
LONG LONG VARCHAR

Oracle recommends using CLOB and BLOB data types instead of LONG and LONG RAW data types.

An Oracle table can contain only one LONG column, The maximum size of a LONG or LONG RAW data type is 2 GB.

LONG RAW LONG VARBINARY