文章/答案/技术大牛

发布

问ORA-02289:序列不存在oracle
EN

Stack Overflow用户

提问于 2021-07-08 16:26:02

回答 1查看 409关注 0票数 0

我正在通过执行插入状态。为数据库表创建了基本模型。在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]

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

oracle12c

oracle

sql-insert

回答 1

Stack Overflow用户

回答已采纳

发布于 2021-07-08 16:51:12

请注意，在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'))

票数 1

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/68305396

复制

相似问题

问ORA-02289:序列不存在oracle
EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问ORA-02289:序列不存在oracleEN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问ORA-02289:序列不存在oracle
EN