General data types|V4.0.0|OceanBase Database| docs|Distributed Database

General data types

Last Updated：2023-07-24 09:52:12 Updated

In OceanBase Database in Oracle mode, general data types include character, numeric, datetime, interval, RAW, and LOB. This topic describes these data types.

Character data types

Character data types: CHAR, NCHAR, NVARCHAR2, VARCHAR2, and VARCHAR.

Data type	Length type	Usage notes	Length
CHAR(size [BYTE	CHAR])	Fixed	This data type features high index efficiency. You can use `trim` to remove excess blanks in a program.	The value range of the `size` parameter is [1,2000]. The default and minimum values are 1. The storage size is `size` characters.
NCHAR[(size)]	Fixed	This data type uses the UNICODE character set. Each character requires two bytes of storage.	The value range of the `size` parameter is [1,2000]. The default and minimum values are 1. The storage size is `size` characters.
NVARCHAR2(size)	Variable	This data type uses the UNICODE character set. Each character requires two bytes of storage.	The value range of the `size` parameter is [1,32767]. The storage size is `size` characters.
VARCHAR2(size [BYTE	CHAR])	Variable	This data type uses the UNICODE character set. Each character requires two bytes of storage.	The value range of the `size` parameter is [1,32767]. The storage size is the actual length of the bytes or characters entered, and the maximum length is `size` bytes or characters.
VARCHAR(size [BYTE	CHAR])	Variable	In OceanBase Database, `VARCHAR` and `VARCHAR2` are the same.	The value range of the `size` parameter is [1,32767]. The storage size is the actual length of the bytes entered, and the maximum length is `size` bytes.

Numeric data types

Numeric data types: NUMBER, FLOAT, BINARY_FLOAT, and BINARY_DOUBLE

Data type	Fixed/Variable length	Length (bytes)	Usage notes
NUMBER(p,s)	Variable	[4,40]	The variable-length fixed-point numbers with decimal precision or floating-point numbers. When it is the data type of floating-point numbers, the `NUMBER` data type does not include the `p` and `s` parameters.
FLOAT(p)	Variable	[4,40]	The sub-type of the `NUMBER` data type. Binary precision range: [1,126]. `FLOAT` does not indicate floating-point numbers.
BINARY_FLOAT	Fixed	4	The floating-point numbers with binary precision. It is the data type of the floating-point numbers with 32-place single precision.
BINARY_DOUBLE	Fixed	8	The floating-point numbers with binary precision. It is the data type of the floating-point numbers with 64-place dual precision.

Datetime and interval data types

Date and time data types

The datetime data types store date and time information. The following table lists datetime data types supported by OceanBase Database.

Data types	Description
DATE	The date and time, which is accurate to seconds and contains no time zone.
TIMESTAMP	It is an extended type of the `DATE` data type, which is accurate to nanoseconds and contains no time zone.
TIMESTAMP WITH TIME ZONE	It is an extended type of the `DATE` data type, which is accurate to nanoseconds and contains the time zone.
TIMESTAMP WITH LOCAL TIME ZONE	The `TIMESTAMP` with local time zone information, which stores database time zone.

Interval data types

The interval data types store a period, unlike the datetime data types that store a specific point in time. The interval data types store the difference between two datetime values. The following table lists the interval data types.

Data type	Note
INTERVAL YEAR TO MONTH	The time interval in years and months.
INTERVAL DAY TO SECOND	The time interval in days, hours, minutes, and seconds.

Datetime and interval data consist of fields, and the values of these fields decide the value of the data types.

Field	Value range of datetime	Value range of interval
`YEAR`	[-4712,9999], excluding year 0.	Any positive or negative integer.
`MONTH`	[01,12].	[0,11]
`DAY`	[01,31]. The value range is determined by the values of `MONTH` and `YEAR` and complies with the rule of the NLS calendar.	Any positive or negative integer.
`HOUR`	[00,23]	[0,23]
`MINUTE`	[00,59]	[0,59]
`SECOND`	[00,59.9(n)], where `(n)` is the decimal accuracy of the time in seconds. `9(n)` does not apply to `DATE`.	[0,59.9(n)], where `(n)` is the decimal accuracy of the interval in seconds.
`TIMEZONE_HOUR`	[-12,14]. This range applies to daylight saving time and does not apply to `DATE` or `TIMESTAMP`.	N/A
`TIMEZONE_MINUTE`	[00,59]. This range does not apply to `DATE` or `TIMESTAMP`.	N/A
`TIMEZONE_REGION`	You can query the `TZNAME` column of the `V$TIMEZONE_NAMES` data dictionary view to get the value range. This range does not apply to `DATE` or `TIMESTAMP`.	N/A
`TIMEZONE_ABBR`	You can query the `TZABBREV` column of the `V$TIMEZONE_NAMES` data dictionary view to get the value range. This range does not apply to `DATE` or `TIMESTAMP`.	N/A

RAW data type

The RAW data type is a length-variable data type. The binary data flows can be transmitted between databases with different character sets and between database servers and clients with different character sets, without character set conversion.

Type	Value range	Description
RAW	If you use this parameter to specify the length of a table column in a database, a maximum of 2,000 bytes can be stored in the column. If you use this parameter to specify the length of a PL variable, a maximum of 32,767 bytes can be stored in the variable.	The RAW data type stores binary data or strings such as images or audio files.

Rowid data types

Rowid data types: Rowid and URowid

Type	Description
Rowid	You can query the `ROWID` pseudocolumn to examine a `ROWID`. A value in this pseudocolumn is a string representing the address of a row. The data type of these strings is `ROWID`. You can also create a table that has a column of the `ROWID` type. A `ROWID` is converted from a Base64-encoded string.
URowid	Some tables have rows with nonphysical or nonpermanent addresses or addresses that are not generated by OceanBase Database. For example, row addresses in an index organization table are stored in the index leaves and can be moved. An index organization table has logical `UROWIDs` and stores them`` in the `ROWID` pseudocolumn.

User-defined data types

User-defined data types: object, array, and nested table

Type	Description
Object Type	An object type is an abstraction of a real-world entity, such as a purchase order, that application programs deal with. An object type is a schema object that consists of three kinds of components: A name, which uniquely identifies the object type within the schema. Attributes, which are built-in types or other custom types. Attributes model the structure and state of the real-world entity. Methods, which are functions or procedures written in PL/SQL and stored in the database. A method can be used by an application to execute the real-world entity.
Arrays	An array is an ordered set of data elements. All elements of a specified array are of the same datatype. Each element has an index, which is a number corresponding to the position of the element in the array. The number of elements in the array indicates the array size. OceanBase Database allows arrays to be of variable sizes. That is why they are called VARRAYs. You must specify the maximum size of an array when you declare the array. Declaring an array does not allocate space. It defines a data type that you can use as: An object type attribute. A PL/SQL variable, parameter, or function return type.
Nested tables	A nested table is an unordered set of data elements. Both built-in data types and custom data types can be used as elements in a nested table. A nested table has a single column. If the nested table is an object type, you can take it as a multicolumn table, with a column for each attribute of the object type. A table type definition does not allocate space. It defines a type that you can use as: An object type attribute. A PL/SQL variable, parameter, or function return type.

Any type

OceanBase provides SQL-based interfaces to define new types in case the built-in types and types supported by ANSI cannot meet your requirements. You can implement these types in C/C++, Java, or PL/SQL.

Type	Description
ANYTYPE	This type contains the description of any named SQL types or unnamed transient types.
ANYDATA	This type contains instances of the specified type, data, and a description of the type. These values can be SQL built-in types or custom types.

Examples

Example 1

The following examples verify the general data types such as character, numeric, and date types in OceanBase Database.

Create a table named t_data_types.

CREATE TABLE t_data_types(
  id number NOT NULL PRIMARY KEY,
  c_char char(100),
  c_varchar varchar2(100),
  c_nchar nchar(100),
  c_nvarchar nvarchar2(100),
  c_numeric numeric(10, 2),
  c_int int,
  c_float float(2),
  c_binary_float binary_float,
  c_binary_double binary_double,
  c_raw raw(2000),
  c_blob blob,
  c_clob clob,
  c_date date,
  c_timestamp timestamp,
  c_timestamp2 timestamp WITH time ZONE,
  c_timestamp3 timestamp WITH LOCAL time zone,
  c_interval_year INTERVAL YEAR TO MONTH,
  c_interval_day INTERVAL DAY TO SECOND
);

Insert data into the All types table.

INSERT INTO
  t_data_types
VALUES
  (
    1,
    'BB',
    'CC',
    'DD',
    5,
    6,
    7.0,
    8.0,
    9.0,
    10.0,
    utl_raw.cast_to_raw('17'),
    to_blob('18'),
    to_clob('19'),
    date '1970-11-11',
    TO_TIMESTAMP('1970-12-12 12:12:12', 'YYYY-MM-DD HH24:MI:SS'),
    TIMESTAMP '1970-02-13 13:13:13.123',
    TIMESTAMP '1970-02-14 14:14:14.123',
    INTERVAL '12-3' YEAR TO MONTH,
    INTERVAL '16 16:16:16' DAY TO SECOND
  );

Query data in the All types table.

obclient> select * from t_data_types;
+----+------------------------------------------------------------------------------------------------------+-----------+--------------------------------------------------------------------------------------------------------+------------+-----------+-------+---------+----------------+-----------------+-------+--------------+--------+-----------+------------------------------+-------------------------------------+------------------------------+-----------------+---------------------+
| ID | C_CHAR                                                                                               | C_VARCHAR | C_NCHAR                                                                                                | C_NVARCHAR | C_NUMERIC | C_INT | C_FLOAT | C_BINARY_FLOAT | C_BINARY_DOUBLE | C_RAW | C_BLOB       | C_CLOB | C_DATE    | C_TIMESTAMP                  | C_TIMESTAMP2                        | C_TIMESTAMP3                 | C_INTERVAL_YEAR | C_INTERVAL_DAY      |
+----+------------------------------------------------------------------------------------------------------+-----------+--------------------------------------------------------------------------------------------------------+------------+-----------+-------+---------+----------------+-----------------+-------+--------------+--------+-----------+------------------------------+-------------------------------------+------------------------------+-----------------+---------------------+
|  1 | BB                                                                                                   | CC        | DD                                                                                                     | 5          |         6 |     7 |       8 |       9.0E+000 |        1.0E+001 | 3137  | 18           | 19     | 11-NOV-70 | 12-DEC-70 12.12.12.000000 PM | 13-FEB-70 01.13.13.123000 PM +08:00 | 14-FEB-70 02.14.14.123000 PM | +12-03          | +16 16:16:16.000000 |
+----+------------------------------------------------------------------------------------------------------+-----------+--------------------------------------------------------------------------------------------------------+------------+-----------+-------+---------+----------------+-----------------+-------+--------------+--------+-----------+------------------------------+-------------------------------------+------------------------------+-----------------+---------------------+
1 row in set

Example 2

The following example verifies a custom object type in ODC and OceanBase Database.

In the SQL window of ODC, create a table named data_type.
Insert data into the data_type table.
Create a custom type named ob_var.
Create a stored procedure and call the custom type ob_var.

Run the custom data type.

Note

You can also run the preceding code in the CLI.

create table data_type (id number(10),name varchar2(50),age int,address varchar2(50),salary float);//

insert into data_type values(1,'baba',20,'hangzhou',3000.00);//

create or replace type ob_var as object(
  var varchar2(10),
  var1 varchar(10)
);//

create or replace procedure p_datatype is
begin
  declare
    rec data_type%rowtype;
    v_age rec.age%type;
    var varchar2(50);
    v_name var%type;
    v_salary data_type.salary%type;

    type salary is table of number index by varchar2(20);
    arr salary;
    v_arr arr%type;

    CURSOR c2 IS SELECT name, age FROM data_type;
    c_row c2%rowtype;
    v_rec c_row%type;

    ob ob_var;
    v_obj ob%type;
  begin
    v_name := 'ali ';
    v_age := 30;
    v_salary := 2000;
    dbms_output.put_line('Referenced item: variable, record, and table column name: ' || v_name  || ' * ' || v_age || ' * ' || v_salary);

    v_arr('James') := 78000;
    dbms_output.put_line('Referenced item: name of collection variable ' || v_arr.FIRST);

    open c2;
    fetch c2 into v_rec;
    dbms_output.put_line('Referenced item: name of cursor variable: ' || v_rec.name || ' * ' || v_rec.age);
    close c2;

    v_obj:=ob_var('test','object');
    dbms_output.put_line('Referenced item: name of the object instance: ' || v_obj.var || ' * ' || v_obj.var1);
  end;
end;
//

set serveroutput on;

begin
  p_datatype;
end;
//