标题: ORA-1722 Error Creating a Sequence [打印本页] 作者: 刘泽宇 时间: 2024-9-29 14:55 标题: 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.