SIMPLE SELECT

The syntax of SELECT is relatively complex. This topic describes the syntax structure 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:
  '<string>'
  (
  {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 = '<string>',
    {
      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 a hierarchical query, you can use a special pseudocolumn LEVEL in the SELECT statement to indicate the level, namely, the hierarchy of the node. Counting from the start point of a query, the level starts from 1, and so forth. This parameter is valid only in a hierarchical query. For more information about pseudocolumns in hierarchical queries, see Hierarchical query pseudocolumns.

• START WITH start_expression: (Optional) Specifies the root row in the hierarchical query.
• CONNECT BY: Specifies how to determine the parent-child relationship. An equality expression is usually used, but other expressions are also supported.
• NOCYCLE: If this keyword is specified, the result can still be returned even if it contains a cycle. The CONNECT_BY_ISCYCLE pseudocolumn can be used to specify where the cycle occurs. Otherwise, an error is returned.
• PRIOR child_expr = parent_expr | parent_expr = PRIOR child_expr: PRIOR is a unary operator, indicating that 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 sorting order for sibling rows at the same level.

For more information about hierarchical queries, see Hierarchical query.

table_function

• The LOCATION clause specifies the path where the external table files are stored. Typically, the data files of an external table are stored in a separate directory. The directory can contain subdirectories. When the table is created, the external table automatically collects all files in this directory.

  • The local LOCATION syntax is LOCATION = '[file://] local_file_path', where local_file_path can be a relative path or an absolute path. If a relative path is used, the current directory must be the installation directory of OceanBase Database. The secure_file_priv parameter is used to configure the file path that an OBServer node is allowed to access. local_file_path must be a subpath of the path specified by secure_file_priv.
  • The remote LOCATION syntax is LOCATION = '{oss|cos|s3}://$ACCESS_ID:$ACCESS_KEY@$HOST:s3_region/remote_file_path', where $ACCESS_ID, $ACCESS_KEY, and $HOST are the access information required for accessing OSS, COS, or S3, and s3_region specifies the region when you use S3. These sensitive access parameters are stored in system tables of the database in encrypted form.

• The FORMAT clause specifies the properties related to the file format. The supported file formats are CSV, PARQUET, and ORC.

  • When TYPE = 'CSV', the following fields are supported:
    • LINE_DELIMITER: Specifies the line delimiter of the CSV file. The default value is LINE_DELIMITER='\n'.
    • FIELD_DELIMITER: (Optional) Specifies the column delimiter of the CSV file. The default value is FIELD_DELIMITER='\t'.
    • ESCAPE: Specifies the escape character in the CSV file. It must be one byte. The default value is ESCAPE='\'.
    • FIELD_OPTIONALLY_ENCLOSED_BY: (Optional) Specifies the character that encloses field values in the CSV file. The default value is empty. When this option is used, only some types of fields (such as CHAR, VARCHAR, TEXT, and JSON) are enclosed.
    • ENCODING: Specifies the character set encoding of the file. If this parameter is not specified, the default value UTF8MB4 is used.
    • NULL_IF: Specifies the strings that are treated as NULL. The default value is empty.
    • SKIP_HEADER: Skips the file header and specifies the number of header rows to skip.
    • SKIP_BLANK_LINES: Specifies whether to skip blank lines. The default value is FALSE, which means that blank lines are not skipped.
    • TRIM_SPACE: Specifies whether to trim leading and trailing spaces of fields in the file. The default value is FALSE, which means that the spaces are not trimmed.
    • EMPTY_FIELD_AS_NULL: Specifies whether to treat empty strings as NULL. The default value is FALSE, which means that empty strings are not treated as NULL.
    • PARSE_HEADER: If this parameter is specified, OceanBase Database directly reads the first line in the CSV file and uses it as the column names.

      Notice

      PARSE_HEADER cannot be used together with SKIP_HEADER because their semantics conflict.

  • When TYPE = 'PARQUET' or TYPE = 'ORC', no additional fields are required.

• The PATTERN clause specifies a regular expression pattern to filter files in the LOCATION directory. For each file path in the LOCATION directory, if the file path matches the pattern, the external table reads the file. Otherwise, the file is skipped. If this parameter is not specified, all files in the LOCATION directory are accessible.

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

• When TYPE = 'ODPS', the following fields are supported:
  • ACCESSID: The ID of the ODPS user.
  • ACCESSKEY: The password of the ODPS user.
  • ENDPOINT: The endpoint of the ODPS service.
  • TUNNEL_ENDPOINT: The endpoint of the Tunnel data transmission service.
  • PROJECT_NAME: The project where the table to be queried resides.
  • SCHEMA_NAME: (Optional) The schema where the table to be queried resides.
  • TABLE_NAME: The name of the table to be queried.
  • QUOTA_NAME: (Optional) Specifies whether to use a specific quota.
  • COMPRESSION_CODE: (Optional) The compression format of the data source. The supported formats are ZLIB, ZSTD, LZ4, and ODPS_LZ4. If this parameter is not specified, compression is disabled.

location_url

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

  Example:

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

Examples

• Query the data in the name column 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
  

• Query the data in the name column from the tbl1 table and deduplicate the query results.

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

• Query the id, name, and num columns in the tbl1 table, divide values in the num column by 2, and name the output column corresponding to the num column as 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
  

• Return the values of the corresponding id, name, and num columns in the tbl1 table based on the filtering 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 and num values from the tbl1 table, calculate the sum of the num values by name, and return the rows with a sum 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 values from the tbl1 table, and sort the results by the num column in ascending order.

  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 return the query results sorted in descending order by name and in ascending order by num.

  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 row with a specified id from the tbl1 table, and use the FOR UPDATE clause to lock the row in the result set.

  /*Query and lock the row where the ID is 1 in the tbl1 table in Session 1.*/
  obclient> SELECT * FROM tbl1 WHERE id=1 FOR UPDATE;
  +------+------+------+
  | ID   | NAME | NUM  |
  +------+------+------+
  |    1 | a    |  100 |
  +------+------+------+
  1 row in set
  
  /*Query and lock the rows where the ID is 1 or 2 in the tbl1 table in Session 2.*/
  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 a table named group_tbl1 and insert data into it. Execute a GROUP BY query with CUBE.

  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 rows from the tbl1 table by name and num and calculate the number of rows in each result set.

  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
  

• Pivot the rows in Table emp_phone to columns, and then pivot the columns 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
  
  /*Pivot 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
  
  /*Pivot 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 the data of a table in a remote database.

  /*Access a remote OceanBase database to query data.*/
  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 a remote Oracle database to query data.*/
  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 the data in the local and remote databases concurrently.*/
  obclient> SELECT t4.col1,t5.col2 FROM tbl1 t4, tbl2@ob_dblink t5 WHERE t1.col3=t2.col3;
  
  /*Query the data in different remote databases concurrently.*/
  obclient> SELECT * FROM tbl2@ob_dblink t_remote1,tbl2@ora_dblink t_remote2 WHERE t_remote1.col1 = t_remote2.col1;
  

• Examples of pagination queries

  • Query the three employees with the smallest employee IDs (empno).

    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 smallest employee IDs (empno) again.

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

  • Query the 25% of 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 25% of employees with the lowest salaries and all other employees with the same salary as the last employee returned 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
    

• Use the alias of the t1 table to query data from the table.

  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 RANDOM() to generate random numbers and query the results of the GENERATOR() function directly by using the FROM clause.

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

• Access sequence values in a remote database.

  1. Log in to the local OceanBase Database and create a DBLink for connecting to an Oracle tenant in a 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 values of the sequence in the remote OceanBase Database from the local 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