Help Center> Data Warehouse Service> Tool Guide> DSC - SQL Syntax Migration Tool> SQL Syntax Migration Reference> Oracle Syntax Migration (GaussDB A)> Sequences
View PDF

Sequences

A sequence is an Oracle object used to generate a number sequence. This can be useful when you need to create an autonumber column to act as a primary key.

If MigSupportSequence is set to true (default), a sequence is created in the PUBLIC schema.

  • CACHE and ORDER cannot be migrated.
  • In Oracle, the maximum value of MAXVALUE can be set to 999999999999999999999999999. In GaussDB(DWS), the maximum value of MAXVALUE can be set to 9223372036854775807.
  • Before migrating a sequence, copy the content in the custom_scripts.sql file and paste it to execute the script in all the target databases. For details, see Migration Process.

Sequence

Input - CREATE SEQUENCE

CREATE SEQUENCE GROUP_DEF_SEQUENCE 
  minvalue 1 
  maxvalue 100000000000000000000
  start with 1152 
  increment by 1 
  cache 50 
  order;

Output

INSERT
     INTO
          PUBLIC.MIG_SEQ_TABLE (
               SCHEMA_NAME
               ,SEQUENCE_NAME
               ,START_WITH
               ,INCREMENT_BY
               ,MIN_VALUE
               ,MAX_VALUE
               ,CYCLE_I
               ,CACHE
               ,ORDER_I
          )
     VALUES (
          UPPER( current_schema ( ) )
          ,UPPER( 'GROUP_DEF_SEQUENCE' )
          ,1152
          ,1
          ,1
          ,9223372036854775807
          ,FALSE
          ,20
          ,FALSE
     )
;

Sequence with NOCACHE

Input - CREATE SEQUENCE with NOCACHE

CREATE SEQUENCE customers_seq
 START WITH 1000
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;

Output

INSERT
     INTO
          PUBLIC.MIG_SEQ_TABLE (
               SCHEMA_NAME
               ,SEQUENCE_NAME
               ,START_WITH
               ,INCREMENT_BY
               ,MIN_VALUE
               ,MAX_VALUE
               ,CYCLE_I
               ,CACHE
               ,ORDER_I
          )
     VALUES (
          UPPER( current_schema ( ) )
          ,UPPER( 'customers_seq' )
          ,1000
          ,1
          ,1
          ,999999999999999999999999999
          ,FALSE
          ,20
          ,FALSE
     )
;

CREATE SEQUENCE with Schema Name

Input - CREATE SEQUENCE with a specified schema name

CREATE SEQUENCE scott.seq_customers
 START WITH 1000 INCREMENT BY 1
 MINVALUE 1000 MAXVALUE 999999999999999
 CACHE 20 CYCLE ORDER;

Output

INSERT
     INTO
          PUBLIC.MIG_SEQ_TABLE (
               SCHEMA_NAME
               ,SEQUENCE_NAME
               ,START_WITH
               ,INCREMENT_BY
               ,MIN_VALUE
               ,MAX_VALUE
               ,CYCLE_I
               ,CACHE
               ,ORDER_I
          )
     VALUES (
          UPPER( 'scott' )
          ,UPPER( 'seq_customers' )
          ,1000
          ,1
          ,1000
          ,999999999999999
          ,TRUE
          ,20
          ,FALSE
     )
;

CREATE SEQUENCE with Default Values

Input - SEQUENCE with a default value

CREATE SEQUENCE seq_orders;

Output

INSERT
     INTO
          PUBLIC.MIG_SEQ_TABLE (
               SCHEMA_NAME
               ,SEQUENCE_NAME
               ,START_WITH
               ,INCREMENT_BY
               ,MIN_VALUE
               ,MAX_VALUE
               ,CYCLE_I
               ,CACHE
               ,ORDER_I
          )
     VALUES (
          UPPER( current_schema ( ) )
          ,UPPER( 'seq_orders' )
          ,1
          ,1
          ,1
          ,999999999999999999999999999
          ,FALSE
          ,20
          ,FALSE
     )
;

NEXTVAL

To migrate the NEXTVAL function, a custom function is provided for generating the next value based on increment_by, max_value, min_value, and cycle. During the installation, this function should be created in all the databases where the migration is to be performed.

This function supports all versions of GaussDB(DWS).

NEXTVAL is a system function in Oracle. GaussDB T. GaussDB A and GaussDB(DWS) do not implicitly support this function. To support this function, DSC creates a NEXTVAL function in the PUBLIC schema. The PUBLIC.NEXTVAL function is used in the migrated statements.

If MigSupportSequence is set to true, NEXTVAL is migrated to PUBLIC.NEXTVAL('[schema].sequence').

If MigSupportSequence is set to false, NEXTVAL is migrated to NEXTVAL('[schema].sequence').

Before migrating the NEXTVAL function, copy the content in the custom_scripts.sql file and paste it to execute the script in all the target databases. For details, see Migration Process.

Input - NEXTVAL

[schema.]sequence.NEXTVAL

Output

PUBLIC.nextval('[schema.]sequence')

Input - NEXTVAL

SELECT
          EMP_ID_SEQ.NEXTVAL INTO
               SEQ_NUM
          FROM
               dual
;

Output

SELECT
          PUBLIC.NEXTVAL ('EMP_ID_SEQ') INTO
               SEQ_NUM
          FROM
               dual
;

CURRVAL

To migrate the CURRVAL function, you can customize one to return the current value of a sequence. During the installation, this function should be created in all the databases where the migration is to be performed.

CURRVAL is a system function in Oracle. GaussDB T, GaussDB A and GaussDB(DWS) do not implicitly support this function. To support this function, DSC creates a CURRVAL function in the PUBLIC schema. The PUBLIC.CURRVAL function is used in the migrated statements.

If MigSupportSequence is set to true, CURRVAL is migrated to PUBLIC.CURRVAL('[schema].sequence').

If MigSupportSequence is set to false, CURRVAL is migrated to CURRVAL('[schema].sequence').

Before migrating the NEXTVAL function, copy the content in the custom_scripts.sql file and paste it to execute the script in all the target databases. For details, see Migration Process.

Input - CURRVAL

[schema.]sequence.CURRVAL

Output

currval('[schema.]sequence')

Input - CURRVAL

INSERT
     INTO
          Line_items_tab (
               Orderno
               ,Partno
               ,Quantity
          )
     VALUES (
          Order_seq.CURRVAL
          ,20321
          ,3
     )
;

Output

INSERT
     INTO
          Line_items_tab (
               Orderno
               ,Partno
               ,Quantity
          ) SELECT
                    PUBLIC.CURRVAL ('Order_seq')
                    ,20321
                    ,3
;