SIMPLE SELECT

The syntax of SELECT is relatively complex. This section describes the syntax of SIMPLE SELECT.

Purpose

This statement is used to query data from a table or view.

Syntax

simple_select:
    SELECT [ hint_options ] [ DISTINCT | UNIQUE | ALL] select_expr_list
    FROM from_list
    [WHERE condition]
    [hierarchical_query_clause]
    [GROUP BY group_expression_list
        [{ROLLUP | CUBE | GROUPING SETS} group_expression_list]
        [HAVING condition]
     ]
    [ORDER BY order_expression_list]
    [FOR UPDATE [OF column] [ {NOWAIT | WAIT integer | SKIP LOCKED } ] ]
    [row_limiting_clause ]

select_expr_list:
    table_name.*
    | table_alias_name.*
    | expr [[AS] column_alias_name]
    | sequence_name.{ CURRVAL|NEXTVAL }@dblink_name

from_list:
    table_reference [, table_reference...]
    url_external_table_references

table_reference:
    simple_table
    | joined_table
    | pivot_clause
    | unpivot_clause
    | table_name@dblink_name

url_external_table_references:
     location_url | table_function

location_url:
  'file_path'
  (
  {FORMAT = (
      TYPE = 'CSV',
      LINE_DELIMITER = '<string>' | <expr>,
      FIELD_DELIMITER = '<string>' | <expr>,
      ESCAPE = '<character>' | <expr>,
      FIELD_OPTIONALLY_ENCLOSED_BY = '<character>' | <expr>,
      ENCODING = 'charset',
      NULL_IF = ('<string>' | <expr>, '<string>' | <expr> ...),
      SKIP_HEADER = <int>,
      SKIP_BLANK_LINES = { TRUE | FALSE },
      PARSE_HEADER = { TRUE | FALSE },
      TRIM_SPACE = { TRUE | FALSE },
      EMPTY_FIELD_AS_NULL = { TRUE | FALSE }
    )
   | FORMAT = ( TYPE = 'PARQUET' | 'ORC' )
  }
  [PATTERN = '<regex_pattern>']
  )

table_function:
  {
  FILES (
    LOCATION = {'file_path' | @location_name['/path']},
    {
      FORMAT = (
        TYPE = 'CSV',
        LINE_DELIMITER = '<string>' | <expr>,
        FIELD_DELIMITER = '<string>' | <expr>,
        ESCAPE = '<character>' | <expr>,
        FIELD_OPTIONALLY_ENCLOSED_BY = '<character>' | <expr>,
        ENCODING = 'charset',
        NULL_IF = ('<string>' | <expr>, '<string>' | <expr> ...),
        SKIP_HEADER = <int>,
        SKIP_BLANK_LINES = { TRUE | FALSE },
        PARSE_HEADER = { TRUE | FALSE },
        TRIM_SPACE = { TRUE | FALSE },
        EMPTY_FIELD_AS_NULL = { TRUE | FALSE }
      )
      | FORMAT = ( TYPE = 'PARQUET' | 'ORC' )
    },
    [PATTERN = '<regex_pattern>']
  )
  | SOURCE (
      TYPE = 'ODPS',
      ACCESSID = '<string>',
      ACCESSKEY = '<string>',
      ENDPOINT = '<string>',
      TUNNEL_ENDPOINT = '<string>',
      PROJECT_NAME = '<string>',
      SCHEMA_NAME = '<string>',
      TABLE_NAME = '<string>',
      QUOTA_NAME = '<string>',
      COMPRESSION_CODE = '<string>'
    )
  }

simple_table:
    (table_factor [partition_option])[table_alias_name]
    | (select_stmt)  table_alias_name
    | (table_reference_list)

joined_table:
    table_reference [INNER] JOIN simple_table [join_condition]
    | table_reference outer_join_type JOIN simple_table join_condition

partition_option:
    PARTITION (partition_name_list)

partition_name_list:
    partition_name [, partition_name...]

outer_join_type:
    {LEFT | RIGHT | FULL} [OUTER]

join_condition:
    ON expression

condition:
    expression

group_expression_list:
    group_expression [, group_expression...]

group_expression:
    expression [ASC | DESC]

order_expression_list:
    order_expression [, order_expression...]

order_expression:
    expression [ASC | DESC]

row_limiting_clause:
    [ OFFSET offset { ROW | ROWS } ]
    [ FETCH { FIRST | NEXT } [ { rowcount | percent PERCENT } ]
        { ROW | ROWS } { ONLY | WITH TIES } ]

pivot_clause：
    PIVOT
    (aggregate_function ( expr ) [[AS] alias ]
      [, aggregate_function ( expr ) [[AS] alias ]... ]
     pivot_for_clause
     pivot_in_clause
    )

pivot_for_clause：
    FOR { column| ( column [, column... ]) }

pivot_in_clause
    IN
    ( { { expr| ( expr [, expr...] ) } [ [ AS] alias]... }
       [, { { expr| ( expr [, expr...] ) } [ [ AS] alias] ...} ]
     )

unpivot_clause ：
    UNPIVOT [ {INCLUDE | EXCLUDE} NULLS ]
    ( { column | ( column [, column... ]) }
     pivot_for_clause
     unpivot_in_clause
     )

unpivot_in_clause：
    IN
    ( { column | ( column [, column... ]) }[ AS { literal | ( literal [, literal... ]) } ]
        [, { column | ( column [, column... ] ) }[ AS {literal | ( literal [, literal... ]) } ]]
    )

hierarchical_query_clause:
    [START WITH start_expression] CONNECT BY [NOCYCLE]
        {PRIOR child_expr = parent_expr
        | parent_expr = PRIOR child_expr} [ORDER SIBLINGS BY ...]

Parameters

hierarchical_query_clause

In hierarchical queries, you can use a special pseudo column LEVEL in the SELECT statement to indicate the level of a node, which represents the hierarchy. The level starts at 1 and increments as you move down the hierarchy. This pseudo column is only available when using hierarchical queries. For more information about pseudo columns in hierarchical queries, see Hierarchical Query Pseudo Columns.

• START WITH start_expression: Optional. Specifies the root row in the hierarchical query.
• CONNECT BY: Specifies how to determine the parent-child relationships. Typically, an equality expression is used, but other expressions are also supported.
• NOCYCLE: When this keyword is specified, even if the query results contain cycles, they can still be returned. Cycles can be identified using the CONNECT_BY_ISCYCLE virtual column. If this keyword is not specified, an error will be returned to the client.
• PRIOR child_expr = parent_expr | parent_expr = PRIOR child_expr: PRIOR is a unary operator that indicates the column in the parameter comes from the parent row. It has the same precedence as the unary operators + and -.
• ORDER SIBLINGS BY: Specifies the order of rows at the same level.

For more information about using hierarchical queries, see Hierarchical Query.

table_function

• The LOCATION clause specifies the path where the external table files are stored. Usually, the data files of an external table are stored in a separate directory, which can contain subdirectories. When you create an external table, the system automatically collects all files in the directory. Valid values:

  • file_path: specifies the path of the external table file. For more information, see the following:

    • For a local location, the format is LOCATION = '[file://] local_file_path', where local_file_path can be a relative or absolute path. If you specify a relative path, the current directory must be the installation directory of OceanBase Database. secure_file_priv specifies the file paths that OBServer nodes can access. local_file_path must be a subpath of secure_file_priv.
    • For a remote location, the format is LOCATION = '{oss | s3}://$ACCESS_ID:$ACCESS_KEY@$HOST:s3_region/remote_file_path', where $ACCESS_ID, $ACCESS_KEY, and $HOST are the access information required to access OSS and S3, and s3_region specifies the region selected for S3. These sensitive access information are stored in the system tables of the database in an encrypted manner.

  • @location_name['/path']: specifies the name of the location object to create an external table. ['/path'] is optional and specifies a subdirectory. For more information about creating a location object, see CREATE LOCATION.

    Note

    For OceanBase Database V4.4.x, the @location_name['/path'] parameter is supported starting from V4.4.1.

• The FORMAT clause specifies the file format. Supported file formats: CSV, PARQUET, and ORC.

  • When TYPE = 'CSV', the following fields are available:
    • LINE_DELIMITER: specifies the line delimiter of the CSV file. Default value: LINE_DELIMITER='\n'.
    • FIELD_DELIMITER: optional. Specifies the column delimiter of the CSV file. Default value: FIELD_DELIMITER='\t'.
    • ESCAPE: specifies the escape character of the CSV file. It can be only one byte. Default value: ESCAPE ='\'.
    • FIELD_OPTIONALLY_ENCLOSED_BY: optional. Specifies the character that encloses the field values in the CSV file. Default value: empty. This option is used to enclose only certain types of fields (such as CHAR, VARCHAR, TEXT, and JSON).
    • ENCODING: specifies the character set encoding format of the file. If not specified, the default value is UTF8MB4.
    • NULL_IF: specifies the string that is treated as NULL. Default value: empty.
    • SKIP_HEADER: skips the header and specifies the number of rows to skip.
    • SKIP_BLANK_LINES: specifies whether to skip blank lines. Default value: FALSE, which means not to skip blank lines.
    • TRIM_SPACE: specifies whether to remove leading and trailing spaces in the fields of the file. Default value: FALSE, which means not to remove leading and trailing spaces in the fields of the file.
    • EMPTY_FIELD_AS_NULL: specifies whether to treat an empty string as NULL. Default value: FALSE, which means not to treat an empty string as NULL.
    • PARSE_HEADER: specifies that the first row of the CSV file is used as the column names.

      Notice

      You cannot use the PARSE_HEADER and SKIP_HEADER clauses at the same time.

  • When TYPE = 'PARQUET/ORC', no additional fields are available.

• The PATTERN clause specifies a regular expression pattern to filter files in the LOCATION directory. For each file path in the LOCATION directory, if it matches the pattern, the external table accesses the file; otherwise, it skips the file. If you do not specify this parameter, the external table accesses all files in the LOCATION directory.

For ODPS format, data is not obtained through files, and there is no meaningful URL path. Therefore, only the source form of the table_function is supported.

• When TYPE = 'ODPS', the following fields are available:
  • ACCESSID: specifies the ID of the ODPS user.
  • ACCESSKEY: specifies the password of the ODPS user.
  • ENDPOINT: specifies the connection address of the ODPS service.
  • TUNNEL_ENDPOINT: specifies the connection address of the Tunnel data transmission service.
  • PROJECT_NAME: specifies the project where the table to be queried is located.
  • SCHEMA_NAME: optional. Specifies the schema of the table to be queried.
  • TABLE_NAME: specifies the name of the table to be queried.
  • QUOTA_NAME: optional. Specifies whether to use the specified quota.
  • COMPRESSION_CODE: optional. Specifies the compression format of the data source. Supported compression formats: ZLIB, ZSTD, LZ4, and ODPS_LZ4. If not specified, compression is not enabled.

location_url

• The FORMAT clause specifies the file format. Supported file formats: CSV, PARQUET, and ORC. For CSV files, you must configure parse_header to specify whether to parse the header row, and use the TYPE clause to specify the export file format. For ODPS data, you must use the source clause.

  Sample query:

  SELECT * FROM
  FILES( location = '/data/',
      format (TYPE = 'csv', field_delimiter = ',', parse_header = true),
      pattern = 'datafiles$';
  

Examples

• Read the name data from the tbl1 table.

  obclient> CREATE TABLE tbl1 (id INT,name VARCHAR(10),num INT);
  Query OK, 0 rows affected
  
  obclient> INSERT INTO tbl1 VALUES (1, 'a',100),(2, 'b',200),(3, 'a',50);
  Query OK, 3 rows affected
  Records: 3  Duplicates: 0  Warnings: 0
  
  obclient> SELECT * FROM tbl1;
  +------+------+------+
  | ID   | NAME | NUM  |
  +------+------+------+
  |    1 | a    |  100 |
  |    2 | b    |  200 |
  |    3 | a    |   50 |
  +------+------+------+
  3 rows in set
  
  obclient> SELECT name FROM tbl1;
  +------+
  | NAME |
  +------+
  | a    |
  | b    |
  | a    |
  +------+
  3 rows in set
  

• Read the name data from the tbl1 table and perform deduplication.

  obclient> SELECT DISTINCT name FROM tbl1;
  +------+
  | NAME |
  +------+
  | a    |
  | b    |
  +------+
  2 rows in set
  

• Query the id, name, and num columns from the tbl1 table and output the num column divided by 2, with the output column named avg.

  obclient> SELECT id, name, num/2 AS avg FROM tbl1;
  +------+------+------+
  | ID   | NAME | AVG  |
  +------+------+------+
  |    1 | a    |   50 |
  |    2 | b    |  100 |
  |    3 | a    |   25 |
  +------+------+------+
  3 rows in set
  

• Query the id, name, and num columns from the tbl1 table based on the filter condition name = 'a'.

  obclient> SELECT id, name, num FROM tbl1 WHERE name = 'a';
  +------+------+------+
  | ID   | NAME | NUM  |
  +------+------+------+
  |    1 | a    |  100 |
  |    3 | a    |   50 |
  +------+------+------+
  2 rows in set
  

• Query the name column from the tbl1 table, group by name, and sum the num column. Output the rows where the sum of num is less than 160.

  obclient> SELECT name,SUM(num)  sum
                  FROM tbl1
                  GROUP BY name
                  HAVING SUM(num) < 160;
  +------+------+
  | NAME | SUM  |
  +------+------+
  | a    |  150 |
  +------+------+
  1 row in set
  

• Query the id, name, and num columns from the tbl1 table and output the results in ascending order based on the num column.

  obclient> SELECT * FROM tbl1 ORDER BY num ASC;
  +------+------+------+
  | ID   | NAME | NUM  |
  +------+------+------+
  |    3 | a    |   50 |
  |    1 | a    |  100 |
  |    2 | b    |  200 |
  +------+------+------+
  3 rows in set
  

• Query all columns from the tbl1 table and output the results in descending order based on the name column and ascending order based on the num column.

  obclient> SELECT * FROM tbl1 ORDER BY name DESC,num ASC;
  +------+------+------+
  | ID   | NAME | NUM  |
  +------+------+------+
  |    2 | b    |  200 |
  |    3 | a    |   50 |
  |    1 | a    |  100 |
  +------+------+------+
  3 rows in set
  

• Query the rows with a specified id from the tbl1 table and lock the query result rows using the FOR UPDATE clause.

  /* In session 1, query the rows with id=1 from the tbl1 table and lock them. */
  obclient> SELECT * FROM tbl1 WHERE id=1 FOR UPDATE;
  +------+------+------+
  | ID   | NAME | NUM  |
  +------+------+------+
  |    1 | a    |  100 |
  +------+------+------+
  1 row in set
  
  /* In session 2, query the rows with id=1 or id=2 from the tbl1 table and lock them. */
  obclient> SELECT * FROM tbl1 WHERE id=1 or id=2 FOR UPDATE;
  OBE-30006: resource busy; acquire with WAIT timeout expired
  
  obclient> SELECT * FROM tbl1 WHERE id=1 or id=2 FOR UPDATE SKIP LOCKED;
  +------+------+------+
  | ID   | NAME | NUM  |
  +------+------+------+
  |    2 | b    |  200 |
  +------+------+------+
  1 row in set
  

• Create the group_tbl1 table and insert data. Execute a GROUP BY query statement with the CUBE clause.

  obclient> CREATE TABLE group_tbl1 (group_id INT,job VARCHAR2(10),name VARCHAR2(10),salary INT);
  Query OK, 0 rows affected
  obclient> INSERT INTO group_tbl1 VALUES(10,'Coding','Bruce',1000),
      (10,'Programmer','Clair',1000),
      (20,'Coding','Jason',2000),
      (20,'Programmer','Joey',2000),
      (30,'Coding','Rebecca',3000),
      (30,'Programmer','Rex',3000);
  Query OK, 6 rows affected
  Records: 6  Duplicates: 0  Warnings: 0
   obclient> SELECT * FROM group_tbl1;
   +----------+------------+---------+--------+
   | GROUP_ID | JOB        | NAME    | SALARY |
   +----------+------------+---------+--------+
   |       10 | Coding     | Bruce   |   1000 |
   |       10 | Programmer | Clair   |   1000 |
   |       20 | Coding     | Jason   |   2000 |
   |       20 | Programmer | Joey    |   2000 |
   |       30 | Coding     | Rebecca |   3000 |
   |       30 | Programmer | Rex     |   3000 |
   +----------+------------+---------+--------+
   6 rows in set
   obclient> SELECT group_id, salary, SUM(salary) FROM group_tbl1 GROUP BY CUBE (group_id, salary);
   +----------+--------+-------------+
   | GROUP_ID | SALARY | SUM(SALARY) |
   +----------+--------+-------------+
   |     NULL |   NULL |       12000 |
   |     NULL |   1000 |        2000 |
   |     NULL |   2000 |        4000 |
   |     NULL |   3000 |        6000 |
   |       10 |   NULL |        2000 |
   |       20 |   NULL |        4000 |
   |       30 |   NULL |        6000 |
   |       10 |   1000 |        2000 |
   |       20 |   2000 |        4000 |
   |       30 |   3000 |        6000 |
   +----------+--------+-------------+
   10 rows in set
  

• Query the tbl1 table and group the results by name and num, then count the number of rows in each group.

  obclient> SELECT name, num, COUNT(*) from tbl1 GROUP BY GROUPING SETS(name, num);
  +------+------+----------+
  | NAME | NUM  | COUNT(*) |
  +------+------+----------+
  | a    | NULL |        2 |
  | b    | NULL |        1 |
  | NULL |  100 |        1 |
  | NULL |  200 |        1 |
  | NULL |   50 |        1 |
  +------+------+----------+
  5 rows in set
  

• Convert the rows in the emp_phone table to columns and then convert the columns back to rows.

  obclient> CREATE TABLE emp(name VARCHAR2(50), num CHAR, phone VARCHAR2(50));
  Query OK, 0 rows affected
  
  obclient> INSERT INTO emp VALUES('ZhangSan', '1', '1234-5678'),('ZhangSan', '2', '3219-6066'),('ZhangSan', '3', '5365-9583');
  Query OK, 3 rows affected
  Records: 3  Duplicates: 0  Warnings: 0
  
  obclient> SELECT * FROM emp;
  +----------+------+-----------+
  | NAME     | NUM  | PHONE     |
  +----------+------+-----------+
  | ZhangSan | 1    | 1234-5678 |
  | ZhangSan | 2    | 3219-6066 |
  | ZhangSan | 3    | 5365-9583 |
  +----------+------+-----------+
  3 rows in set
  
  /* Convert the rows in the emp table to columns. */
  obclient> SELECT * FROM emp PIVOT(MAX(phone) FOR num IN (1 AS home, 2 AS office, 3 AS mobile));
  +----------+-----------+-----------+-----------+
  | NAME     | HOME      | OFFICE    | MOBILE    |
  +----------+-----------+-----------+-----------+
  | ZhangSan | 1234-5678 | 3219-6066 | 5365-9583 |
  +----------+-----------+-----------+-----------+
  1 row in set
  
  /* Convert the columns in the emp table to rows. */
  obclient> CREATE VIEW v_emp AS SELECT * FROM emp PIVOT(MAX(phone) FOR num IN (1 AS home, 2 AS office, 3 AS mobile));
  Query OK, 0 rows affected
  
  obclient>  SELECT * FROM v_emp UNPIVOT(phone FOR num IN (home AS 1, office AS 2, mobile AS 3));
  +----------+-----+-----------+
  | NAME     | NUM | PHONE     |
  +----------+-----+-----------+
  | ZhangSan |   1 | 1234-5678 |
  | ZhangSan |   2 | 3219-6066 |
  | ZhangSan |   3 | 5365-9583 |
  +----------+-----+-----------+
  3 rows in set
  

• Query data from a table in a remote database.

  /* Query data from a remote OceanBase database. */
  obclient> SELECT ID FROM tbl2@ob_dblink;
  +------+
  | ID   |
  +------+
  |    1 |
  |    2 |
  |    3 |
  +------+
  3 rows in set
  
  obclient> SELECT * FROM tbl2@ob_dblink;
  +------+------+------+
  | ID   | NAME | NUM  |
  +------+------+------+
  |    1 | a    |  100 |
  |    2 | b    |  200 |
  |    3 | a    |   50 |
  +------+------+------+
  3 rows in set
  
  /*Access the data in the remote Oracle database.*/
  obclient> SELECT ID FROM tbl2@ora_dblink;
  +------+
  | ID   |
  +------+
  |    1 |
  |    2 |
  |    3 |
  +------+
  3 rows in set
  
  obclient> SELECT * FROM tbl2@ora_dblink;
  +------+------+------+
  | ID   | NAME | NUM  |
  +------+------+------+
  |    1 | a    |  100 |
  |    2 | b    |  200 |
  |    3 | a    |   50 |
  +------+------+------+
  3 rows in set
  
  /*Query data in the local and remote databases at the same time.*/
  obclient> SELECT t4.col1,t5.col2 FROM tbl1 t4, tbl2@ob_dblink t5 WHERE t1.col3=t2.col3;
  
  /*Query data in different remote databases at the same time.*/
  obclient> SELECT * FROM tbl2@ob_dblink t_remote1,tbl2@ora_dblink t_remote2 WHERE t_remote1.col1 = t_remote2.col1;
  

• Example of pagination query

  • Query the three employees with the lowest employee numbers.

    obclient> CREATE TABLE emp(
        empno         NUMBER(4,0),
        empname       VARCHAR(10),
        job           VARCHAR(9),
        mgr           NUMBER(4,0),
        hiredate      DATE,
        sal           NUMBER(7,2),
        comm          NUMBER(7,2),
        deptno        NUMBER(2,0),
        CONSTRAINT PK_emp PRIMARY KEY (empno)
    );
    Query OK, 0 rows affected
    
    obclient> INSERT INTO emp VALUES (1839,'KING','PRESIDENT',null,'17-DEC-81',5000,null,10)
       ,(1698,'BLAKE','MANAGER',1839,'01-MAY-81',2850,null,30)
       ,(1782,'CLARK', 'MANAGER', 1839, '09-JUN-81', 2450, null, 10)
       ,(1566,'JONES','MANAGER',1839, '02-APR-81',2975,null,40)
       ,(1788,'SCOTT','ANALYST',1566, '15-JUL-87',3000,null,20)
       ,(1902,'FORD','ANALYST',1566, '05-OCT-81',3000,null,40)
       ,(1369,'SMITH','CLERK',1902, '17-NOV-80',800,null,20)
       ,(1499,'ALLEN','SALESMAN',1698, '20-FEB-81',1600,300,30);
    
    Query OK, 8 rows affected
    Records: 8  Duplicates: 0  Warnings: 0
    
    obclient> SELECT empno, empname FROM emp ORDER BY empno FETCH FIRST 3 ROWS ONLY;
    +-------+---------+
    | EMPNO | EMPNAME |
    +-------+---------+
    |  1369 | SMITH   |
    |  1499 | ALLEN   |
    |  1566 | JONES   |
    +-------+---------+
    3 rows in set
    

  • Query the three employees with the lowest employee numbers again.

    obclient> SELECT empno, empname
    FROM emp
    ORDER BY empno
    FETCH NEXT 3 ROWS ONLY;
    

  • Query the first 25% of the employees with the lowest salaries.

    obclient> SELECT empno, empname sal FROM emp ORDER BY sal
     FETCH FIRST 25 PERCENT ROWS ONLY;
    +-------+-------+
    | EMPNO | SAL   |
    +-------+-------+
    |  1499 | ALLEN |
    |  1698 | BLAKE |
    +-------+-------+
    2 rows in set
    

  • Query the first 25% of the employees with the lowest salaries, and all other employees whose salaries are the same as the last employee in the previous example.

    obclient> SELECT empno, empname sal FROM emp ORDER BY sal
     FETCH FIRST 25 PERCENT ROWS WITH TIES;
    +-------+-------+
    | EMPNO | SAL   |
    +-------+-------+
    |  1499 | ALLEN |
    |  1698 | BLAKE |
    +-------+-------+
    2 rows in set
    

• Query data from the t1 table and specify a table alias.

  obclient> CREATE TABLE t1 (c1 INT, c2 INT );
  Query OK, 0 rows affected
  
  obclient> INSERT INTO t1 VALUES ('1','2');
  Query OK, 1 row affected
  
  obclient> SELECT * FROM (t1) a;
  +------+------+
  | C1   | C2   |
  +------+------+
  |    1 |    2 |
  +------+------+
  1 row in set
  

• Use the RANDOM() function to generate random numbers and directly query the GENERATOR() function after the FROM clause.

  obclient> SELECT RANDOM(4) FROM GENERATOR(3);
  +---------------------+
  | RANDOM(4)           |
  +---------------------+
  | 5267436225003336391 |
  | -851690886662571060 |
  | 1738617244330437274 |
  +---------------------+
  3 rows in set
  

• Access the sequence value in the remote database.

  1.Log in to the local OceanBase Database and create a dblink to the Oracle tenant of the remote OceanBase Database.

  obclient> CREATE DATABASE LINK seq_link CONNECT TO test@oracle IDENTIFIED BY test HOST '127.xxx.xxx.xxx:2828';
  Query OK, 0 rows affected
  

  2.Create a sequence named my_seq in the remote OceanBase Database.

  obclient> CREATE SEQUENCE my_seq START WITH 1 MINVALUE 1 MAXVALUE 10 INCREMENT BY 2 NOCYCLE NOORDER CACHE 30;
  Query OK, 0 rows affected
  

  3.Access the sequence value in the remote OceanBase Database from the local OceanBase Database.

  obclient> SELECT my_seq.NEXTVAL@seq_link FROM DUAL;
  +---------+
  | NEXTVAL |
  +---------+
  |       1 |
  +---------+
  1 row in set
  
  obclient> SELECT my_seq.CURRVAL@seq_link FROM DUAL;
  +---------+
  | CURRVAL |
  +---------+
  |       1 |
  +---------+
  1 row in set
  
  obclient> SELECT my_seq.NEXTVAL@seq_link FROM DUAL;
  +---------+
  | NEXTVAL |
  +---------+
  |       3 |
  +---------+
  1 row in set