metaData can't reflect tables for oracle9i

[ABU LU]

python 3.4.3

cx-oracle 5.2.1

SQLAlchemy 1.1.4

Oracle database: NLSRTL 9.2.0.1.0 ; Oracle9i Enterprise Edition 9.2.0.1.0 ; PL/SQL 9.2.0.1.0 ; TNS for 32-bit Windows: 9.2.0.1.0 ;

When I called metadata.reflect(), exception raised:

sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-00904: "COMPRESSION": invalid identifier [SQL: 'SELECT table_name, compression FROM ALL_TABLES WHERE table_name = :table_name AND owner = :owner ']

And I deleted all the views of database(for testing), after calling metadata.reflect(), there is an empty dict of metadata.tables.

[Mike Bayer]

this column name is based on a version check for oracle 9, per oracle's documentation at https://oracle-base.com/articles/11g/table-compression-enhancements-11gr1 table compression was introduced in 9i. we will have to modify the version check with the correct version for when "compression" was introduced.

[Mike Bayer]

Oracle 9.2 docs, COMPRESSION not present in ALL_TABLES: http://docs.oracle.com/cd/B10501_01/server.920/a96536/ch2152.htm#1303953

[Mike Bayer]

oracle 10.1, COMPRESSION present: http://docs.oracle.com/cd/B14117_01/server.101/b10755/statviews_1190.htm#REFRN20286

[Mike Bayer]

as I don't have 9i available it would be helpful if you could test:

diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py
index 87e1004..e4e5d7a 100644
--- a/lib/sqlalchemy/dialects/oracle/base.py
+++ b/lib/sqlalchemy/dialects/oracle/base.py
@@ -1026,7 +1026,7 @@ class OracleDialect(default.DefaultDialect):
     @property
     def _supports_table_compression(self):
         return self.server_version_info and \
-            self.server_version_info >= (9, 2, )
+            self.server_version_info >= (10, 1, )

     @property
     def _supports_table_compress_for(self):

[Mike Bayer]

Bump "table compression" flag to Oracle 10.1

• Fixed bug where the "COMPRESSION" keyword was used in the ALL_TABLES query on Oracle 9.2; even though Oracle docs state table compression was introduced in 9i, the actual column is not present until 10.1.

Change-Id: Iebfa59bfcfdff859169df349a5426137ab006e67 Fixes: #3875

→ <<cset 910347aef28f>>

[ABU LU]

Hi, thank you.

I've tested and no exception of 'compression' now.

But it's an empty list of metadata.tables after calling metadata.reflect().

I try to debug and found that:

file: sqlalchemy/dialects/oracle/base.py

line 1184

in this method get_table_names, the sql_str was combined as:

'SELECT table_name FROM all_tables WHERE nvl(tablespace_name, \'no tablespace\') NOT IN (\'SYSTEM\', \'SYSAUX\') AND OWNER = :owner AND IOT_NAME IS NULL AND DURATION IS NULL'

this sql string can query a empty list from database, no table name. So I changed tablespace_name to table_name:

'SELECT table_name FROM all_tables WHERE nvl(table_name, \'no tablespace\') NOT IN (\'SYSTEM\', \'SYSAUX\') AND OWNER = :owner AND IOT_NAME IS NULL AND DURATION IS NULL'

And It works. could you check it? thank you very much.

[Mike Bayer]

you likely have your tables in the SYSTEM tablespace which on Oracle is not common practice, and these tablespaces are skipped by default. See http://docs.sqlalchemy.org/en/latest/dialects/oracle.html#table-names-with-system-sysaux-tablespaces for how to change this.

[ABU LU]

I see, thank you so much.