Edit

Share via

O2SS0217: Call to identity sequence CURRVAL 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 CURRVAL function.

Background

A SEQUENCE is an independent database object in Oracle, which automatically creates unique integers typically for primary key/foreign values. A CURRVAL is used to access the current 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 CURRVAL 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.CURRVAL INTO t1 FROM DUAL;
END;

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

O2SS0217: Call to identity sequence CURRVAL not supported

Possible remedies

To resolve this error, either rewrite the PL/SQL code to eliminate access to CURRVAL, 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.