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 sequence_scripts.sql file and paste it to execute the script in all the target databases. For details, see Executing Custom DB Scripts.
Sequence
Input - CREATE SEQUENCE
1 2 3 4 5 6 7 |
CREATE SEQUENCE GROUP_DEF_SEQUENCE
minvalue 1
maxvalue 100000000000000000000
start with 1152
increment by 1
cache 50
order;
|
Output
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
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
1 2 3 4 5 |
CREATE SEQUENCE customers_seq
START WITH 1000
INCREMENT BY 1
NOCACHE
NOCYCLE;
|
Output
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
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
)
;
|
Input - CREATE SEQUENCE with a specified schema name
Input - CREATE SEQUENCE with a specified schema name
1 2 3 4 |
CREATE SEQUENCE scott.seq_customers
START WITH 1000 INCREMENT BY 1
MINVALUE 1000 MAXVALUE 999999999999999
CACHE 20 CYCLE ORDER;
|
Output
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
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 a Default Value
Input - SEQUENCE with a default value
1 |
CREATE SEQUENCE seq_orders;
|
Output
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
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.
NEXTVAL supports all GaussDB(DWS) versions.
NEXTVAL is a system function of Oracle and is not implicitly supported by GaussDB(DWS). 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 sequence_scripts.sql file and paste it to execute the script in all the target databases. For details, see Executing Custom DB Scripts.
Input - NEXTVAL
1 |
[schema.]sequence.NEXTVAL
|
Output
1 |
PUBLIC.nextval('[schema.]sequence')
|
Input - NEXTVAL
1 2 3 4 5 6 |
SELECT
EMP_ID_SEQ.NEXTVAL INTO
SEQ_NUM
FROM
dual
;
|
Output
1 2 3 4 5 6 |
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 of Oracle and is not implicitly supported by GaussDB(DWS). 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 sequence_scripts.sql file and paste it to execute the script in all the target databases. For details, see Executing Custom DB Scripts.
Input - CURRVAL
1 |
[schema.]sequence.CURRVAL
|
Output
1 |
currval('[schema.]sequence')
|
Input - CURRVAL
1 2 3 4 5 6 7 8 9 10 11 12 13 |
INSERT
INTO
Line_items_tab (
Orderno
,Partno
,Quantity
)
VALUES (
Order_seq.CURRVAL
,20321
,3
)
;
|
Output
1 2 3 4 5 6 7 8 9 10 11 |
INSERT
INTO
Line_items_tab (
Orderno
,Partno
,Quantity
) SELECT
PUBLIC.CURRVAL ('Order_seq')
,20321
,3
;
|
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot