General data types

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.

Numeric data types

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

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.

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.

Related fields and values

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

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.

Rowid data types

Rowid data types: Rowid and URowid

User-defined data types

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

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.

Examples

Example 1

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

1. 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
   );
   

2. 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
     );
   

3. 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.

1. In the SQL window of ODC, create a table named data_type.

   

2. Insert data into the data_type table.

   

3. Create a custom type named ob_var.

   

4. Create a stored procedure and call the custom type ob_var.

   

5. 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;
   //