ORA-1722 Error Creating a Sequence

刘泽宇

现象:
========

You are trying to create a sequence starting with a value one greater
than a value found in an existing column of a table.  This column contains
some unknown maximum number.  You keep getting the following error:

  ORA-01722: invalid number


例子:
--------

SQL> create sequence testusers start with (max(UNIQUEFIELD) + 1)
     from users;
create sequence testusers start with (max(UNIQUEFIELD) + 1)
                       *
ERROR at line1:
ORA-01722: invalid number


处理方法:
=========

There are two solutions to this problem:

o  Determine the maximum value of the table column and then
   supply it to the CREATE SEQUENCE statement:

   SQL> select max(deptno+1) from <TABLE_NAME>

   MAX(DEPTNO+1)
   -------------
              41

   SQL> create sequence test1 start with 41;

or,

o  Use a PL/SQL script with EXECUTE IMMEDIATE to create the sequence:

    DECLARE
      v_num   NUMBER(10);
      bar     VARCHAR2(100);
    BEGIN
      SELECT max(deptno+1)
      INTO v_num
      FROM <USERNAME>.<TABLE_NAME>
      bar := 'CREATE SEQUENCE test1 START WITH '||v_num;
      execute immediate bar;
    END;
    /


解释:
============

This SQL statement always returns an ORA-1722 error because the
syntax for CREATE SEQUENCE is expecting an INTEGER when using the
START WITH clause.