SIMPLE SELECT

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

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 the LEVEL pseudo column in the SELECT statement to indicate the level, which represents the hierarchy of nodes. The level starts at 1, and increases as you move down the hierarchy. This pseudo column is only available in hierarchical queries. For more information about pseudo columns in hierarchical queries, see Pseudo columns in hierarchical queries.

• START WITH start_expression: Optional. Specifies the root row in the hierarchical query.
• CONNECT BY: Specifies how to determine the parent-child relationship. Typically, an equality expression is used, but other expressions are also supported.
• NOCYCLE: If this keyword is specified, the query will still return results even if a cycle is detected in the result set. You can use the CONNECT_BY_ISCYCLE virtual column to identify where the cycle occurs. If this keyword is not specified, an error will be returned to the client when a cycle is detected.
• 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 + and - operators.
• ORDER SIBLINGS BY: Specifies the order of sibling rows at the same level.

For more information about hierarchical queries, see Hierarchical queries.

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, which can contain subdirectories. When you create an external table, it automatically collects all files in the directory. The value can be:

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

    • 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 have permission to 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 information when using S3. These sensitive access information are stored in the system tables of the database in an encrypted manner.

  • @location_name['/path']: specifies the path of the external table by referencing a Location object. ['/path'] is an optional parameter that 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 related attributes. It supports CSV, PARQUET, and ORC file formats.

  • When TYPE = 'CSV', the following fields are included:
    • LINE_DELIMITER: specifies the line delimiter of the CSV file. The default value is LINE_DELIMITER='\n'.
    • FIELD_DELIMITER: an optional parameter that specifies the column delimiter of the CSV file. The default value is FIELD_DELIMITER='\t'.
    • ESCAPE: specifies the escape character of the CSV file. It can only be one byte. The default value is ESCAPE ='\'.
    • FIELD_OPTIONALLY_ENCLOSED_BY: an optional parameter that specifies the character used to enclose field values in the CSV file. The default value is an empty string. This option is used to enclose only specific 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. The default value is an empty string.
    • SKIP_HEADER: skips the file header and specifies the number of rows to skip.
    • SKIP_BLANK_LINES: specifies whether to skip blank lines. The default value is FALSE, indicating that blank lines are not skipped.
    • TRIM_SPACE: specifies whether to remove leading and trailing spaces from fields in the file. The default value is FALSE, indicating that leading and trailing spaces are not removed.
    • EMPTY_FIELD_AS_NULL: specifies whether to treat empty strings as NULL. The default value is FALSE, indicating that empty strings are not treated as NULL.
    • PARSE_HEADER: specifies that after this configuration, the first line of the CSV file is directly obtained and used as the column names for each column.

      Notice

      PARSE_HEADER cannot be used with SKIP_HEADER at the same time, as they have conflicting semantics.

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

• 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 will access the file; otherwise, it will skip the file. If this parameter is not specified, the default is to access all files in the LOCATION directory.

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

• When TYPE = 'ODPS', the following fields are included:
  • 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: an optional parameter that specifies the schema of the table to be queried.
  • TABLE_NAME: specifies the name of the table to be queried.
  • QUOTA_NAME: an optional parameter that specifies whether to use the specified quota.
  • COMPRESSION_CODE: an optional parameter that specifies the compression format of the data source. It supports ZLIB, ZSTD, LZ4, and ODPS_LZ4. If not specified, compression is not enabled.

location_url

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

  Sample query:

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

Examples

• Read data from the name column in 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 data from the name column in the tbl1 table and remove duplicates.

  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 where the name column is equal to '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 the results by the name column, and sum the num column. Output only the rows where the sum of the num column 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 from the tbl1 table where the id column is specified and lock the query result rows using the FOR UPDATE clause.

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

  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 the name and num columns. 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 data from a 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 from a local database and a remote database at the same time.*/
  obclient> SELECT t4.col1,t5.col2 FROM tbl1 t4, tbl2@ob_dblink t5 WHERE t1.col3=t2.col3;
  
  /*Query data from 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;
  

• Paging query example

  • Query the employee numbers of the three employees with the lowest salaries.

    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 employee numbers of the three employees with the lowest salaries again.

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

  • Query the employees in the first 25% of the lowest salary employees.

    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 employees in the first 25% of the lowest salary employees, and all other employees whose salary is the same as the last employee in the previous query.

    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
  

• Generate random numbers by using the RANDOM() function, and 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 a remote database.

  1.Log in to the local OceanBase Database and create a database link 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