Skip to content

cx_Oracle.DatabaseError: ORA-00942: table or view does not exist #5784

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Closed
BayerSe opened this issue Dec 18, 2020 · 3 comments
Closed

cx_Oracle.DatabaseError: ORA-00942: table or view does not exist #5784

BayerSe opened this issue Dec 18, 2020 · 3 comments
Labels
bug Something isn't working oracle regression something worked and was broken by a change
Milestone
1.3.x

Comments

@BayerSe
Copy link
Contributor

BayerSe commented Dec 18, 2020

Describe the bug
After upgrading from 1.3.20 to 1.3.21 I get the following exception upon establishing a connection:

cx_Oracle.DatabaseError: ORA-00942: table or view does not exist

Expected behavior
No exception, as in the previous version.

To Reproduce

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

conn_string = '...hidden...'

engine = create_engine(conn_string)
make_session = sessionmaker(bind=engine)
session = make_session()

con = session.connection()

Error

Traceback (most recent call last):
  File "/home/user/.local/share/virtualenvs/project-03q6S6S0/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2336, in _wrap_pool_connect
    return fn()
  File "/home/user/.local/share/virtualenvs/project-03q6S6S0/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 364, in connect
    return _ConnectionFairy._checkout(self)
  File "/home/user/.local/share/virtualenvs/project-03q6S6S0/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 778, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/home/user/.local/share/virtualenvs/project-03q6S6S0/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 495, in checkout
    rec = pool._do_get()
  File "/home/user/.local/share/virtualenvs/project-03q6S6S0/lib/python3.9/site-packages/sqlalchemy/pool/impl.py", line 140, in _do_get
    self._dec_overflow()
  File "/home/user/.local/share/virtualenvs/project-03q6S6S0/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
    compat.raise_(
  File "/home/user/.local/share/virtualenvs/project-03q6S6S0/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/home/user/.local/share/virtualenvs/project-03q6S6S0/lib/python3.9/site-packages/sqlalchemy/pool/impl.py", line 137, in _do_get
    return self._create_connection()
  File "/home/user/.local/share/virtualenvs/project-03q6S6S0/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 309, in _create_connection
    return _ConnectionRecord(self)
  File "/home/user/.local/share/virtualenvs/project-03q6S6S0/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 440, in __init__
    self.__connect(first_connect_check=True)
  File "/home/user/.local/share/virtualenvs/project-03q6S6S0/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 664, in __connect
    pool.dispatch.first_connect.for_modify(
  File "/home/user/.local/share/virtualenvs/project-03q6S6S0/lib/python3.9/site-packages/sqlalchemy/event/attr.py", line 314, in exec_once_unless_exception
    self._exec_once_impl(True, *args, **kw)
  File "/home/user/.local/share/virtualenvs/project-03q6S6S0/lib/python3.9/site-packages/sqlalchemy/event/attr.py", line 285, in _exec_once_impl
    self(*args, **kw)
  File "/home/user/.local/share/virtualenvs/project-03q6S6S0/lib/python3.9/site-packages/sqlalchemy/event/attr.py", line 322, in __call__
    fn(*args, **kw)
  File "/home/user/.local/share/virtualenvs/project-03q6S6S0/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 1406, in go
    return once_fn(*arg, **kw)
  File "/home/user/.local/share/virtualenvs/project-03q6S6S0/lib/python3.9/site-packages/sqlalchemy/engine/strategies.py", line 199, in first_connect
    dialect.initialize(c)
  File "/home/user/.local/share/virtualenvs/project-03q6S6S0/lib/python3.9/site-packages/sqlalchemy/dialects/oracle/cx_oracle.py", line 921, in initialize
    super(OracleDialect_cx_oracle, self).initialize(connection)
  File "/home/user/.local/share/virtualenvs/project-03q6S6S0/lib/python3.9/site-packages/sqlalchemy/dialects/oracle/base.py", line 1320, in initialize
    super(OracleDialect, self).initialize(connection)
  File "/home/user/.local/share/virtualenvs/project-03q6S6S0/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 325, in initialize
    self.default_isolation_level = self.get_isolation_level(
  File "/home/user/.local/share/virtualenvs/project-03q6S6S0/lib/python3.9/site-packages/sqlalchemy/dialects/oracle/cx_oracle.py", line 956, in get_isolation_level
    cursor.execute(
cx_Oracle.DatabaseError: ORA-00942: table or view does not exist
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
  File "<input>", line 10, in <module>
  File "/home/user/.local/share/virtualenvs/project-03q6S6S0/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1142, in connection
    return self._connection_for_bind(
  File "/home/user/.local/share/virtualenvs/project-03q6S6S0/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1150, in _connection_for_bind
    return self.transaction._connection_for_bind(
  File "/home/user/.local/share/virtualenvs/project-03q6S6S0/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 433, in _connection_for_bind
    conn = bind._contextual_connect()
  File "/home/user/.local/share/virtualenvs/project-03q6S6S0/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2302, in _contextual_connect
    self._wrap_pool_connect(self.pool.connect, None),
  File "/home/user/.local/share/virtualenvs/project-03q6S6S0/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2339, in _wrap_pool_connect
    Connection._handle_dbapi_exception_noconnection(
  File "/home/user/.local/share/virtualenvs/project-03q6S6S0/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1583, in _handle_dbapi_exception_noconnection
    util.raise_(
  File "/home/user/.local/share/virtualenvs/project-03q6S6S0/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/home/user/.local/share/virtualenvs/project-03q6S6S0/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2336, in _wrap_pool_connect
    return fn()
  File "/home/user/.local/share/virtualenvs/project-03q6S6S0/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 364, in connect
    return _ConnectionFairy._checkout(self)
  File "/home/user/.local/share/virtualenvs/project-03q6S6S0/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 778, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/home/user/.local/share/virtualenvs/project-03q6S6S0/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 495, in checkout
    rec = pool._do_get()
  File "/home/user/.local/share/virtualenvs/project-03q6S6S0/lib/python3.9/site-packages/sqlalchemy/pool/impl.py", line 140, in _do_get
    self._dec_overflow()
  File "/home/user/.local/share/virtualenvs/project-03q6S6S0/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
    compat.raise_(
  File "/home/user/.local/share/virtualenvs/project-03q6S6S0/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/home/user/.local/share/virtualenvs/project-03q6S6S0/lib/python3.9/site-packages/sqlalchemy/pool/impl.py", line 137, in _do_get
    return self._create_connection()
  File "/home/user/.local/share/virtualenvs/project-03q6S6S0/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 309, in _create_connection
    return _ConnectionRecord(self)
  File "/home/user/.local/share/virtualenvs/project-03q6S6S0/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 440, in __init__
    self.__connect(first_connect_check=True)
  File "/home/user/.local/share/virtualenvs/project-03q6S6S0/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 664, in __connect
    pool.dispatch.first_connect.for_modify(
  File "/home/user/.local/share/virtualenvs/project-03q6S6S0/lib/python3.9/site-packages/sqlalchemy/event/attr.py", line 314, in exec_once_unless_exception
    self._exec_once_impl(True, *args, **kw)
  File "/home/user/.local/share/virtualenvs/project-03q6S6S0/lib/python3.9/site-packages/sqlalchemy/event/attr.py", line 285, in _exec_once_impl
    self(*args, **kw)
  File "/home/user/.local/share/virtualenvs/project-03q6S6S0/lib/python3.9/site-packages/sqlalchemy/event/attr.py", line 322, in __call__
    fn(*args, **kw)
  File "/home/user/.local/share/virtualenvs/project-03q6S6S0/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 1406, in go
    return once_fn(*arg, **kw)
  File "/home/user/.local/share/virtualenvs/project-03q6S6S0/lib/python3.9/site-packages/sqlalchemy/engine/strategies.py", line 199, in first_connect
    dialect.initialize(c)
  File "/home/user/.local/share/virtualenvs/project-03q6S6S0/lib/python3.9/site-packages/sqlalchemy/dialects/oracle/cx_oracle.py", line 921, in initialize
    super(OracleDialect_cx_oracle, self).initialize(connection)
  File "/home/user/.local/share/virtualenvs/project-03q6S6S0/lib/python3.9/site-packages/sqlalchemy/dialects/oracle/base.py", line 1320, in initialize
    super(OracleDialect, self).initialize(connection)
  File "/home/user/.local/share/virtualenvs/project-03q6S6S0/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 325, in initialize
    self.default_isolation_level = self.get_isolation_level(
  File "/home/user/.local/share/virtualenvs/project-03q6S6S0/lib/python3.9/site-packages/sqlalchemy/dialects/oracle/cx_oracle.py", line 956, in get_isolation_level
    cursor.execute(
sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-00942: table or view does not exist
(Background on this error at: http://sqlalche.me/e/13/4xp6)

Versions.

  • OS: Ubuntu 18.04
  • Python: 3.9.1
  • SQLAlchemy: 1.3.21
  • Database: Oracle 12.1.0.2.0
  • DBAPI: cx-Oracle 8.1.0
@BayerSe BayerSe added the requires triage New issue that requires categorization label Dec 18, 2020
@Tolker-KU
Copy link

Tolker-KU commented Dec 18, 2020 •
edited
Loading

Note that this bug is reported in #5783

@CaselIT CaselIT added bug Something isn't working duplicate This issue or pull request already exists oracle regression something worked and was broken by a change and removed requires triage New issue that requires categorization duplicate This issue or pull request already exists labels Dec 18, 2020
@CaselIT CaselIT mentioned this issue Dec 18, 2020
Closed
@sqla-tester
Copy link
Collaborator

Mike Bayer has proposed a fix for this issue in the master branch:

Gracefully degrade on v$transaction not readable https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/2421

@sqla-tester
Copy link
Collaborator

Mike Bayer has proposed a fix for this issue in the rel_1_3 branch:

Gracefully degrade on v$transaction not readable https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/2422

@zzzeek zzzeek added this to the 1.3.x milestone Dec 18, 2020
sqlalchemy-bot pushed a commit that referenced this issue Dec 18, 2020
@zzzeek
Gracefully degrade on v$transaction not readable
d6842bc 
Fixed regression which occured due to [ticket:5755] which implemented
isolation level support for Oracle.   It has been reported that many Oracle
accounts don't actually have permission to query the ``v$transaction``
view so this feature has been altered to gracefully fallback when it fails
upon database connect, where the dialect will assume "READ COMMITTED" is
the default isolation level as was the case prior to SQLAlchemy 1.3.21.
However, explicit use of the :meth:`_engine.Connection.get_isolation_level`
method must now necessarily raise an exception, as Oracle databases with
this restriction explicitly disallow the user from reading the current
isolation level.

Fixes: #5784
Change-Id: Iefc82928744f3c944c18ae8000eb3c9e52e523bc
(cherry picked from commit 566f1b5)
@tjbergst tjbergst mentioned this issue Dec 18, 2020
Closed
@jagamts1 jagamts1 mentioned this issue Dec 23, 2020
Merged
6 tasks
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bug Something isn't working oracle regression something worked and was broken by a change
Projects
None yet
Milestone
1.3.x
Development

No branches or pull requests
5 participants
@zzzeek @sqla-tester @BayerSe @CaselIT @Tolker-KU