O2SS0221: Call to identity sequence NEXTVAL not supported (Error)

This article covers some of the reasons why SQL Server Migration Assistant (SSMA) for Oracle doesn't convert procedures with reference to sequence's NEXTVAL function.

Background

A SEQUENCE is an independent database object in Oracle, which automatically creates unique integers typically for primary key/foreign values. A NEXTVAL is used to access the next value of the specified sequence.

When converting SEQUENCE objects, SSMA can use two techniques, namely Sequence to Sequence, and Sequence to Identity. When SSMA converts to sequence, it creates a corresponding SEQUENCE object in the SQL Server schema for each Oracle sequence. In the Sequence to Identity mode, SSMA doesn't create any SEQUENCE objects in SQL Server thus calls to NEXTVAL for the Oracle sequence results in an error message.

Example

Consider the following example:

CREATE OR REPLACE PROCEDURE proc1
AS
    t1 NUMBER;
BEGIN
    SELECT customers_seq.NEXTVAL INTO t1 FROM DUAL;
END;

Here we have a stored procedure that attempts to retrieve next value for the sequence customers_seq. If Sequence to Identity mode is used in SSMA, then the following error message is produced:

O2SS0221: Call to identity sequence NEXTVAL not supported

Possible remedies

To resolve this error, either rewrite the PL/SQL code to eliminate access to NEXTVAL, or change the SSMA project settings to produce SEQUENCE objects in SQL Server, instead of identity columns. To do the later:

1. Select the Tools menu and select Project Settings.
2. Then, select the General tab.
3. Now, under Sequence conversion group set Convert Sequence Generator to Using SQL Server sequence generator.
4. Apply these settings and select OK.

Related conversion messages

• O2SS0188: Sequence not found in source metadata (Error)
• O2SS0214: Column has bound sequence, but sequence was not found in source metadata
• O2SS0217: Call to identity sequence CURRVAL not supported (Error)