Converting Oracle Sequence to SQL Server "Denali"

  • Article

Updated 2/7/2012 Jiafei Yuan - Microsoft SQL Server Migration Assistant (SSMA) for Oracle v5.2.  The information provided below is still valid for SSMA for Oracle v5.2, but SQL Server "Denali" is changed to 2012.  Users should download the lastest SSMA for Oracle]

One of the new feature in SQL Server Migration Assistant (SSMA) for Oracle v5.1 is the support for converting Oracle sequence to SQL Server “Denali” sequences. Although the functionalities are similar with Oracle’s sequence, there are a few differences. This blog article describes the differences and how SSMA handle those during conversion.

Data Type

SQL Server, by default, uses INTEGER data type to define sequence value. The maximum number supported with INTEGER type is 2147483647. Oracle supports 28 digit of integer value in its sequence implementation. On the other hand, SQL Server supports different data types when creating sequence.  When migrating Sequence from Oracle, SSMA migrate Sequence from Oracle by creating sequence object in SQL Server with type of NUMERIC(28). This is to ensure that the converted sequence can store all possible values from the original sequence object in Oracle.

Oracle SQL Server
CREATE SEQUENCE MySequence; CREATE SEQUENCE MySequence AS numeric (28,0) 
CREATE TABLE MyTable (id NUMBER, text VARCHAR2(50)); CREATE TABLE MyTable (id float, text VARCHAR(50))

INSERT INTO MyTable (id, text) VALUES (MySequence.NEXTVAL,  'abc');

 INSERT INTO MyTable (id, text) VALUES (NEXT VALUE FOR MySequence, 'abc')
UPDATE MyTable SET id=MySequence.NEXTVAL WHERE id = 1; UPDATE MyTable SET id= NEXT VALUE FOR MySequence WHERE id = 1
Starting Value

Unless explicitly defined, SQL Server create sequence with starting number of the lowest number in the data type range. For example, when using simple CREATE SEQUENCE MySequence, SQL Server “Denali” creates a sequence with starting value of -2147483648, as supposed in Oracle which uses default starting value of 1.

Another migration challenge is how to maintain continuity after the database migration, so the next sequence generated after migration to SQL Server is consistent with that of Oracle. SSMA creates SQL Server sequence using START WITH property using Oracle sequence's last_number property to resolve this challenge. 

Oracle SQL Server
CREATE SEQUENCE MySequence; -- last_number is 10

CREATE SEQUENCE MySequence AS numeric (28,0)   START WITH 10;
Maximum CACHE Value

Oracle supports 28 digit of positive integer for CACHE. SQL Server supports up to 2147483647 for CACHE property value (even when the SEQUENCE is created with data type of NUMERIC(28)).

SSMA sets CACHE to the maximum supported value if the CACHE value in Oracle is set greater than 2147483647.

Oracle SQL Server
CREATE SEQUENCE MySequence CACHE 2147483648;

CREATE SEQUENCE MySequence AS numeric (28,0)   START WITH 10 CACHE 2147483647;
ORDER|NOORDER Property

SQL Server does not support ORDER|NOORDER property in sequence.  SSMA ignores this property when migrating sequence object.

Oracle SQL Server
CREATE SEQUENCE MySequence NOORDER;

CREATE SEQUENCE MySequence AS numeric (28,0)   START WITH 10;
CURRVAL

SQL Server does not support obtaining current value of a sequence. You can lookup current_value attribute from sys.sequences . However, note that the current_value represent global value of sequence (across all sessions). Oracle's CURRVAL returns the current value for each session. Consider the following example:

Session 1 (Oracle) Session 2 (Oracle)

SELECT MySequence.nextval FROM dual;

NEXTVAL ------------ 1

  

SELECT MySequence.currval FROM dual;

CURRVAL ------------ 1

  
 

SELECT MySequence.nextval FROM dual;

NEXTVAL ------------ 2
 

SELECT MySequence.currval FROM dual;

CURRVAL ------------ 2

SELECT MySequence.currval FROM dual;

CURRVAL ------------ 1

  

The current_value from sys.sequence record the latest value across all session. Hence, in the example above, the current_value is the same for any sessions (current_value = 2).

When converting  PL/SQL statement containing CURRVAL to SQL Server Denali, SSMA generates the following error:

*   SSMA error messages:
*   O2SS0490: Conversion of identifier <sequence name> for CURRVAL is not supported.

When you encounter this error, you should consider rewriting your statement by storing next sequence value into a variable then read the variable value as current value. For example:

Oracle SQL Server

CREATE PROCEDURE create_employee (Name_in IN NVARCHAR2,  EmployeeID_out OUT NUMBER) AS BEGIN

   INSERT INTO employees (employeeID, Name)    VALUES (employee_seq.nextval, Name_in);

   SELECT employee_seq.currval INTO EmployeeID_out FROM dual;

END;

CREATE PROCEDURE create_employee @Name_in NVARCHAR(50),  @EmployeeID_out INT OUT AS

   SET @EmployeeID_out = NEXT VALUE FOR employee_seq

   INSERT INTO employees (employeeID, Name)    VALUES (@EmployeeID_out, @Name_in)

If you are using CURRVAL extensively in your application and rewriting all statements with CURRVAL is not possible, a alternative approach is to convert Oracle Sequence to SSMA custom sequence emulator. When migrating to SQL Server prior to "Denali", SSMA converts Oracle sequence using custom sequence emulator. You can use this conversion approach when migrating to SQL Server "Denali" (which support conversion of CURRVAL) by changing the SSMA project setting (Tools > Project Settings > General > Conversion > Sequence Conversion).

image