ORA-02289:序列不存在oracle

Question

我正在通过python SQLALCHEMY执行插入语句。为数据库表创建了一个基本模型。在Oracle中，我创建了如下所示的序列

CREATE SEQUENCE XXBRIM.XXBRIM_HEADER_INTER_ID_SEQUENCE
START WITH 60001
MAXVALUE 9999999999999999999999999999
MINVALUE 1
NOCYCLE
CACHE 10000
NOORDER
NOKEEP
GLOBAL;

在下方显示的触发器中使用上述序列

CREATE OR REPLACE TRIGGER XXBRIM.XXBRIM_HEADER_ID_INCREAMENT
BEFORE INSERT OR UPDATE
ON XXBRIM.XXBRIM_SUBHEADER_INTERMEDIA_T FOR EACH ROW
BEGIN
insert into dummy_table (col1) values(1);
IF :NEW.ID_PK IS NULL THEN
SELECT  XXBRIM_header_inter_id_SEQUENCE.NEXTVAL INTO :NEW.ID_PK FROM DUAL;
END IF;
END;

在python中，使用SQLAlchemy创建了一个基本模型并使用了序列。部分代码如下：

class HeaderIntermediate(Base):
__tablename__ = 'XXBRIM_SUBHEADER_INTERMEDIA_T'
ID_PK = Column(Integer, Sequence('XXBRIM.XXBRIM_header_inter_id_SEQUENCE'))

尝试在python中使用以下代码插入：

session.execute(
HeaderIntermediate.__table__.insert(),
[new_ls]

(new_ls是一个字典，列名为关键字，值作为值插入(

我得到以下错误：

nsert exec had an exception: (cx_Oracle.DatabaseError) ORA-02289: sequence does not exist
[SQL: INSERT INTO "XXBRIM_SUBHEADER_INTERMEDIA_T" ("ID_PK") VALUES ("XXBRIM.XXBRIM.XXBRIM_HEADER_ID_INCREAMENT".nextval) RETURNING "XXBRIM_SUBHEADER_INTERMEDIA_T"."ID_PK" INTO :ret_0]
[parameters: {'ret_0': <cx_Oracle.Var of type DB_TYPE_VARCHAR with value [[], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], []]>}]
(Background on this error at: http://sqlalche.me/e/14/4xp6)
Traceback (most recent call last):
File "/Users/risav/.conda/envs/json_parser_ver1/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1770, in _execute_context
self.dialect.do_execute(
File "/Users/risav/.conda/envs/json_parser_ver1/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 717, in do_execute
cursor.execute(statement, parameters)
cx_Oracle.DatabaseError: ORA-02289: sequence does not exist
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/Users/risav/Desktop/BRIM_INTEGRATION/json_parser_final_version_1/engine/runtime/parse_json_version1.py", line 105, in process_to_db
sentence = batch_insert(table_name, row_deduplicate,session)
File "/Users/risav/Desktop/BRIM_INTEGRATION/json_parser_final_version_1/engine/runtime/db_utils.py", line 72, in batch_insert
session.execute(
File "/Users/risav/.conda/envs/json_parser_ver1/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 1689, in execute
result = conn._execute_20(statement, params or {}, execution_options)
File "/Users/risav/.conda/envs/json_parser_ver1/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1582, in _execute_20
return meth(self, args_10style, kwargs_10style, execution_options)
File "/Users/risav/.conda/envs/json_parser_ver1/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
return connection._execute_clauseelement(
File "/Users/risav/.conda/envs/json_parser_ver1/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1451, in _execute_clauseelement
ret = self._execute_context(
File "/Users/risav/.conda/envs/json_parser_ver1/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1813, in _execute_context
self._handle_dbapi_exception(
File "/Users/risav/.conda/envs/json_parser_ver1/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1994, in _handle_dbapi_exception
util.raise_(
File "/Users/risav/.conda/envs/json_parser_ver1/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
raise exception
File "/Users/risav/.conda/envs/json_parser_ver1/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1770, in _execute_context
self.dialect.do_execute(
File "/Users/risav/.conda/envs/json_parser_ver1/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 717, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-02289: sequence does not exist
[SQL: INSERT INTO "XXBRIM_SUBHEADER_INTERMEDIA_T" ("ID_PK") VALUES ("XXBRIM.XXBRIM.XXBRIM_HEADER_ID_INCREAMENT".nextval) RETURNING "XXBRIM_SUBHEADER_INTERMEDIA_T"."ID_PK" INTO :ret_0]
[parameters: {'ret_0': <cx_Oracle.Var of type DB_TYPE_VARCHAR with value [[], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], []]>}]
(Background on this error at: http://sqlalche.me/e/14/4xp6)
[10102]

但我也创作并承诺了这个序列。不知道我错过了什么

Answer 1

请注意，模式名称在SQL中指定了两次；这导致了错误：

INSERT INTO "XXBRIM_SUBHEADER_INTERMEDIA_T" ("ID_PK") VALUES ("XXBRIM.XXBRIM.XXBRIM_HEADER_ID_INCREAMENT".nextval) RETURNING "XXBRIM_SUBHEADER_INTERMEDIA_T"."ID_PK" INTO :ret_0]

我建议不要在列定义中指定模式名称，因为它似乎是在其他地方自动添加的。

ID_PK = Column(Integer, Sequence('XXBRIM_header_inter_id_SEQUENCE'))