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.

Input Code:

IN -> Oracle_01.sql

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

Output Code:

OUT -> Oracle_01.sql

CREATE OR REPLACE SEQUENCE SequenceSample
START WITH 1000
INCREMENT BY 1
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}';

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

• 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.

Input Code:

IN -> Oracle_02.sql

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

Output Code:

OUT -> Oracle_02.sql

select seq1.nextval from dual;

select
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0069 - THE SEQUENCE CURRVAL PROPERTY IS NOT SUPPORTED IN SNOWFLAKE. ***/!!! 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:

IN -> Oracle_03.sql

CREATE SEQUENCE SEQUENCE1
START WITH 9223372036854775808;

CREATE SEQUENCE SEQUENCE2
START WITH -9223372036854775809;

Output Code:

OUT -> Oracle_03.sql

CREATE OR REPLACE SEQUENCE SEQUENCE1
--** SSC-FDM-OR0002 - SEQUENCE START VALUE EXCEEDS THE MAX VALUE ALLOWED BY SNOWFLAKE. **
START WITH 9223372036854775808
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}';

CREATE OR REPLACE SEQUENCE SEQUENCE2
--** SSC-FDM-OR0002 - SEQUENCE START VALUE EXCEEDS THE MAX VALUE ALLOWED BY SNOWFLAKE. **
START WITH -9223372036854775809
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}';

Related EWIs

1. SSC-EWI-OR0069: The sequence CURRVAL property is not supported in Snowflake.

2. SSC-FDM-OR0002: The sequence start value exceeds the max value allowed by Snowflake.