GBase 8s Oracle Compatibility: Data Types

Facing fierce international competition and an external environment with significantly rising uncertainties, it is imperative to accelerate the development of China's information technology industry and build a secure, independent IT application innovation system. As a leader in China’s Xinchuang database sector, GBase has introduced its OLTP database GBase 8s, which has been tested with users across finance, telecommunications, power, transportation, government, defense, and other industries, validating its stability and performance. How does GBase 8s achieve Oracle compatibility? Let’s start with data types.

1. Character

Oracle includes six character types: CHAR, NCHAR, VARCHAR, VARCHAR2, NVARCHAR2, and LONG. Details as follows:

1) CHAR

CHAR type: fixed-length string, padded with spaces to reach its maximum length. A CHAR column can store up to 2,000 bytes of information.

2) VARCHAR

VARCHAR is the standard SQL variable-length string. It can store an empty string.

3) VARCHAR2

VARCHAR2 is a variable-length string unique to Oracle. Unlike CHAR, it does not pad with spaces to the maximum length. A VARCHAR2 column can store up to 4,000 bytes of information.

4) NCHAR

NCHAR is a fixed-length string containing Unicode data. An NCHAR column can store up to 2,000 bytes. Its maximum length depends on the national character set.

5) NVARCHAR2

NVARCHAR2 is a variable-length string containing Unicode data. It can store up to 4,000 bytes.

6) LONG

LONG stores variable-length character data up to 2 GB (2 gigabytes, not 2 giga-characters). Like VARCHAR2 or CHAR, text stored in a LONG column undergoes character set conversion. Oracle recommends using CLOB instead of LONG in development; LONG support is retained only for backward compatibility. CLOB has far fewer restrictions than LONG.

GBase 8s is compatible with all Oracle character types except LONG and VARCHAR2. LONG can be replaced with CLOB, VARCHAR2 is recommended to be replaced with VARCHAR, and NVARCHAR2 with NVARCHAR. Additionally, GBase 8s supports the LVARCHAR type, a variable-length string with a default of 2048 bytes and a maximum of 32739 bytes.

Verification in GBase 8s:

● create table t_char(col char);

>>Success;

● create table t_varchar(col varchar);

>>Success;

● create table t_nchar(col nchar);

>>Success;

● create table t_lvarchar(col lvarchar);

>>Success;

● create table t_nvarchar2(col nvarchar2);

>>[-9628]：Type(nvarchar2) not found.

● create table t_varchar2(col varchar2);

>>[-9628]：Type(varchar2) not found.

● create table t_long(col long);

>>[-9628]：Type(long) not found.

2. Numeric

Oracle includes five numeric types: INT, NUMBER, FLOAT, BINARY_FLOAT, and BINARY_DOUBLE. Details as follows:

1) NUMBER

NUMBER(P,S) is the most common numeric type, requiring 1 to 22 bytes of storage.

P stands for precision, indicating the number of significant digits, up to a maximum of 38. S stands for scale, with a range of -84 to 127. A positive scale indicates the number of digits from the decimal point to the least significant digit; a negative scale indicates the number of digits from the most significant digit to the decimal point.

2) INT / INTEGER

INT is a subtype of NUMBER, equivalent to NUMBER(38,0), used to store integers. If a fractional value is inserted or updated, it will be rounded.

3) FLOAT

FLOAT is an ANSI data type and a subtype of NUMBER. It represents a floating-point number with 126 bits of binary precision and 38 decimal digits of precision.

4) BINARY_FLOAT

BINARY_FLOAT is a 32-bit, single-precision floating-point data type using binary precision. It supports at least 6 digits of precision and requires 5 bytes per value, including the length byte.

5) BINARY_DOUBLE

BINARY_DOUBLE is a 64-bit, double-precision floating-point data type using binary precision. Each value requires 9 bytes, including the length byte.

GBase 8s is compatible with all Oracle numeric types, except BINARY_FLOAT and BINARY_DOUBLE which should be replaced with DOUBLE PRECISION. Note that in GBase 8s, the NUMBER type appears as NUMERIC, with a precision limit of 32 significant digits and the number of decimal places cannot exceed the precision. Additionally, GBase 8s provides the MONEY type for currency values, supporting formatting with currency symbols.

Verification in GBase 8s:

● create table t_number(col numeric);

>>Success;

● create table t_int(col int);

>>Success;

● create table t_float(col float);

>>Success;

● create table t_money(col money);

>>Success;

● create table t_binary_float(col binary_float);

>>[-9628]：Type(binary_float) not found.

● create table t_binary_double(col binary_double);

>>[-9628]：Type(binary_double) not found.

3. Date and Time

Oracle includes four temporal types: DATE, TIMESTAMP, INTERVAL YEAR TO MONTH, and INTERVAL DAY TO SECOND. Details as follows:

1) DATE

DATE is the most commonly used data type for storing date and time information. Although date and time can be represented as character or numeric types, the DATE type has special associated attributes. Oracle stores the following information for each date value: century, year, month, day, hour, minute, and second. It generally occupies 7 bytes of storage.

2) TIMESTAMP

TIMESTAMP is a fixed-width date/time type of 7 or 12 bytes. It differs from DATE in that it can include fractional seconds, with up to 9 digits to the right of the decimal point.

3) INTERVAL YEAR TO MONTH

Stores a period of time in years and months.

4) INTERVAL DAY TO SECOND

Stores a period of time in days and seconds.

GBase 8s is fully compatible with Oracle date/time types. Note that Oracle’s DATE maps to GBase 8s’s DATETIME YEAR TO SECOND. The native DATE type in GBase 8s only supports year, month, and day by default, without time. Oracle’s TIMESTAMP(p) maps to DATETIME YEAR TO FRACTION(min(5,p)), supporting up to 5 fractional seconds.

Verification in GBase 8s:

● create table t_date(col datetime year to second);

>>Success;

● create table t_timestamp(col datetime year to fraction);

>>Success;

● create table t_interval_year_to_month(col interval year to month);

>>Success;

● create table t_interval_day_to_second(col interval day to second);

>>Success;

4. Large Objects

Oracle includes four large object types: BLOB, CLOB, NCLOB, and BFILE, each supporting up to 4 GB. Details as follows:

1) CLOB

Character Large Object, stores single-byte and multi-byte character data. Supports fixed-width and variable-width character sets. Commonly used for large text storage.

2) NCLOB

National Character Large Object, stores Unicode data. Supports fixed-width and variable-width character sets.

3) BLOB

Binary Large Object, stores unstructured binary data. It can be considered as a bit stream without character set semantics, typically used for images, audio, video, etc.

4) BFILE

External binary file, stored as an operating system file outside the database. It is read-only and treated as a binary file by the database.

GBase 8s is compatible with Oracle’s CLOB and BLOB types, supporting up to 4 TB each. NCLOB and BFILE are not currently supported; CLOB and BLOB are recommended as alternatives. Additionally, GBase 8s supports the BYTE and TEXT types, both with a maximum size of 2 GB.

Verification in GBase 8s:

● create table t_clob(col clob);

>>Success;

● create table t_blob(col blob);

>>Success;

● create table t_nclob(col nclob);

>>[-9628]：Type(nclob) not found.

● create table t_bfile(col bfile);

>>[-9628]：Type(bfile) not found.

5. Other Types

In addition to the common types above, Oracle also supports RAW, LONG RAW, ROWID, and UROWID types. Details as follows:

1) RAW

RAW is used for storing binary or character data. It is a variable-length binary data type, meaning no character set conversion is performed on the stored data. It can store up to 2000 bytes; BLOB is recommended as an alternative.

2) LONG RAW

LONG RAW can store up to 2 GB of raw binary data (without character set conversion). BLOB is recommended as an alternative.

3) ROWID

ROWID is a special column type known as a pseudocolumn. The ROWID pseudocolumn can be accessed like a regular column in a SQL SELECT statement. ROWID indicates the address of a row and is defined with the ROWID data type. Every row in an Oracle database has a pseudocolumn.

4) UROWID

UROWID is used for tables and represents a row’s primary key, generated based on the primary key. The difference between UROWID and ROWID is that UROWID can represent various ROWIDs and is safer to use. It is generally used by index-organized tables.

GBase 8s supports the ROWID pseudocolumn by default. RAW and LONG RAW types can be replaced with the BLOB type.

Verification in GBase 8s:

● create table t_raw(col raw);

>>[-9628]：Type(raw) not found.

● create table t_longraw(col long raw);

>>[-9628]：Type(long raw) not found.

● create table t_test(col int);

select rowid from t_test;

>>Success

● create table t_urowid(col urowid);

>>[-9628]：Type(urowid) not found.

In summary, GBase 8s provides comprehensive compatibility with common Oracle data types. For a few less common data types, minor code modifications or corresponding type substitutions are required.