Migrating Oracle to SQL Server using SSMA - Error O2SS0221 Call to identity sequence NEXTVAL not supported
By Bill Ramos and Mayank Bhanawat, Advaiya Inc.
[Updated 2/8/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 there are some difference since SSMA v5.1 did some changes on sequence(see Converting Oracle Sequence to SQL Server "Denali").]
This blog post covers some of the reasons why SQL Server Migration Assistant (SSMA) for Oracle doesn’t convert some procedure containing a Sequence with NEXTVAL function. A Sequence is an independent database object in Oracle, which automatically creates unique integers typically for primary key/foreign key values. When used with NEXTVAL, it returns the next sequence number from the sequence and increments the counter value.
Error O2SS0221 Call to identity sequence NEXTVAL not supported
Background
When converting Sequence object with the NEXTVAL function, SSMA uses two techniques namely, ‘Emulation’ and ‘Sequence to Identity conversion’. In Emulation, SSMA converts the source schema and generates two additional objects in the target SQL Server schema for each Sequence. In the ‘Sequence to Identity conversion’, SSMA does not convert the Oracle Sequence to the IDENTITY in SQL Server and generates error, which is described in this post.
For more information on how SSMA implements the Oracle sequence object, please see the article from DBBest titled Migration of Oracle sequences to SQL Server 2005/ 2008
Reasons
In Oracle, a sequence that generates a value typically used for a primary key/foreign key relationship and the table that is using the Sequence value are completely independent. Typically, to load sequence values into table, the NEXTVAL method is used with the INSERT statement.
In SQL server, we don’t have the same feature. When we want to produce primary keys for a table in SQL Server, we use a numeric column and call it as IDENTITY. An IDENTITY value cannot be set explicitly in SQL Server as a part of INSERT statement; hence, it generates error O2SS0221 during migration.
Possible Remedies
SSMA provides two option settings that determine how sequences are converted. One option is to convert the column using the sequence to use an identity column in SQL Server. However, if you use NEXTVAL as part of an insert statement, the identity column approach will not work and triggers the error.
Consider the following example:
CREATE OR REPLACE PROCEDURE proc1 AS
t1 NUMBER;
t2 NUMBER;
BEGIN
SELECT customers_seq.NEXTVAL into t1 FROM dual;
SELECT customers_seq.currval into t2 FROM dual;
END;
Here, this query refers to the procedure proc1 created in Oracle, which SSMA will try to migrate to SQL server. But if the SSMA project settings as mentioned below are your default settings:
Under the Change Sequence-to-identity Conversion tab:
· Allow sequence-to-identity conversion is selected “YES”
[Updated: SSMA v5.1 later(including v5.1) uses "Convert Sequence Generator"->"Using column identity" to instead this tab.]
· Convert CURRVAL outside triggers is selected “NO”
Then, it should give the following error:
“O2SS0221: SQL Server Migration Assistant for Oracle Error message: Call to identity sequence NEXTVAL not supported”
To resolve this error, you should perform the following steps:
1. Click the Tools menu and select Project Settings.
2. Then, click the General tab.
3. Now, under ‘Sequence-to-identity Conversion’ , select ‘Allow sequence-to-identity conversion’ to “NO”.
[Updated: In SSMA v5.1 later, select 'Convert Sequence Generator' to 'Using SSMA sequence generator'.]
Now, apply these settings and click OK.
Related Errors
There are several other errors related to NEXTVAL that you may encounter. These include the following:
· O2SS0214 Column has bound sequence, but sequence was not found in source metadata
· O2SS0217 Call to identity sequence CURRVAL not supported
· O2SS0188 Sequence not found in source metadata
References
For more information, check out the following references:
Migrating Oracle to SQL Server 2008 White Paper
CREATE SEQUENCE – Oracle Database SQL Language Reference 11g Release