Create Sequence

In this section, you will find information about the transformation done to the Create Sequence in Oracle.

Let's first see a code example, and what it would look like after it has been transformed.

--Oracle
CREATE SEQUENCE SequenceSample
START WITH 1000
INCREMENT BY 1
NOCACHE
NOCYCLE;

--Snowflake
/*** MSC-WARNING - MSCEWI3044 - NOCACHE REMOVED FROM THE OPTIONS. ***/
/*** MSC-WARNING - MSCEWI3044 - NOCYCLE REMOVED FROM THE OPTIONS. ***/
CREATE OR REPLACE SEQUENCE MYDB.PUBLIC.SequenceSample
START WITH 1000
INCREMENT BY 1;

The first change that it is done is to apply the schema or datawarehouse to the name of the sequence. The second transformation consists in removing some elements and add then as comments, since oracle has some elements in the create sequence that are not supported in snowflake.

In Oracle, after the name of the Sequence, the elements that are NOT commented are the following

• START WITH 1000

• INCREMENT BY 1

If the element is not one of those, it will be commented and added as a warning just before the create sequence, like in the example.

The following elements are the ones that are removed and added as comments

• MAXVALUE

• NOMAXVALUE

• MINVALUE

• NOMINVALUE

• CYCLE

• NOCYCLE

• CACHE

• NOCACHE

• ORDER

• NOORDER

• KEEP

• NOKEEP

• SESSION

• GLOBAL

• SCALE

• EXTEND

• SCALE

• NOEXTEND

• NOSCALE

• SHARD

• EXTEND

• SHARD

• NOEXTEND

• NOSHARD

SEQUENCE EXPRESSIONS

• NEXTVAL: Snowflake grammar is the same as the Oracle one.

• CURRVAL: Snowflake does not has an equivalent so it is transformed to a stub function. Check this link to understand Snowflake's approach.

-- Oracle
select seq1.nextval from dual;
select seq1.currval from dual;

-- Snowflake
select seq1.nextval from dual;
select
/*** MSC-ERROR - MSCEWI3069 - THE SEQUENCE CURRVAL PROPERTY IS NOT SUPPORTED IN SNOWFLAKE. ***/
PUBLIC.CURRVAL_UDF('seq1.currval')
from dual;

Sequence START WITH

START WITH statement value may exceed the maximum value allowed by Snowflake. What Snowflake said about the start value is: Specifies the first value returned by the sequence. Supported values are any value that can be represented by a 64-bit two’s compliment integer (from -2^63 to 2^63-1). So according to the previously mentioned, the max value allowed is 9223372036854775807 for positive numbers and 9223372036854775808 for negative numbers.

Example Code

Input Code:

CREATE SEQUENCE SEQUENCE1
START WITH 9223372036854775808;

CREATE SEQUENCE SEQUENCE2
START WITH -9223372036854775809;

Output Code:

/*** MSC-ERROR - MSCEWI3068 - SEQUENCE START VALUE EXCEEDS THE MAX VALUE ALLOWED BY SNOWFLAKE. ***/
CREATE OR REPLACE SEQUENCE PUBLIC.SEQUENCE1
--START WITH 9223372036854775808
                              ;

/*** MSC-ERROR - MSCEWI3068 - SEQUENCE START VALUE EXCEEDS THE MAX VALUE ALLOWED BY SNOWFLAKE. ***/
CREATE OR REPLACE SEQUENCE PUBLIC.SEQUENCE2
--START WITH -9223372036854775809
                               ;