Compatible with Oracle sequence
1. Objective
-
This document is designed for compatibility with Oracle sequence functions, in order to use Oracle’s sequences in IvorySQL.
2. Function description
-
Sequence is a database object, similar to tables and views, that represents an integer sequence that can be used by any table and view in the global database namespace. Sequence values can be accessed using NEXTVAL and CURRVAL. Sequences can be in ascending or descending order.
3. Test cases
ivorysql=# CREATE sequence seq;
CREATE SEQUENCE
ivorysql=# SELECT seq.NEXTVAL FROM DUAL;
nextval
---------
1
(1 row)
ivorysql=# ALTER sequence seq restart start with 10;
ALTER SEQUENCE
ivorysql=# SELECT seq.NEXTVAL FROM DUAL;
nextval
---------
10
(1 row)
ivorysql=# DROP SEQUENCE seq;
DROP SEQUENCE
4. Import rows into an identity column
In Oracle mode, an identity column obtains generated values from an associated sequence. Choose the clause according to how the import supplies IDs.
| Identity clause | Omitted value or DEFAULT |
Explicit number | Literal NULL |
|---|---|---|---|
|
Generated |
Rejected by a plain |
Rejected |
|
Generated |
Stored as supplied |
Rejected |
|
Generated |
Stored as supplied |
Generated |
The example imports an existing ID of 42 and starts the sequence above the imported range. The two new rows receive 1000 and 1001.
CREATE TABLE identity_import_demo (
item_id bigint GENERATED BY DEFAULT ON NULL AS IDENTITY (START WITH 1000)
PRIMARY KEY,
label varchar2(30)
);
INSERT INTO identity_import_demo VALUES (42, 'legacy');
INSERT INTO identity_import_demo (label) VALUES ('generated');
INSERT INTO identity_import_demo VALUES (NULL, 'literal null');
SELECT item_id, label FROM identity_import_demo ORDER BY item_id;
DROP TABLE identity_import_demo;
Inserting explicit IDs leaves the identity sequence’s counter untouched. Set the start value or reseed the sequence to a safe value for the imported data before allowing generated inserts. A primary key or unique constraint enforces uniqueness.