- changed status to wontfix
Oracle function-based index reflection fails
Ubuntu, python 2.5.2, sqlalchemy 0.6.x-dev, cx_Oracle 5.0.2, Oracle client 11g.
stromnov@localhost:~$ cat test.py
# -*- coding: utf-8 -*-
import sqlalchemy
print sqlalchemy.__version__
login, passwd, db = ('login', 'passwd', 'db')
engine = sqlalchemy.create_engine("oracle://%s:%s@%s" % (login, passwd, db), convert_unicode=True, optimize_limits=True, assert_unicode=True)
metadata = sqlalchemy.MetaData(engine, reflect=True)
stromnov@localhost:~$ python2.5 test.py
0.6beta1
Traceback (most recent call last):
File "test.py", line 8, in <module>
metadata = sqlalchemy.MetaData(engine, reflect=True)
File "/usr/lib/python2.5/site-packages/sqlalchemy/schema.py", line 1622, in __init__
File "/usr/lib/python2.5/site-packages/sqlalchemy/schema.py", line 1747, in reflect
File "/usr/lib/python2.5/site-packages/sqlalchemy/schema.py", line 202, in __new__
File "/usr/lib/python2.5/site-packages/sqlalchemy/schema.py", line 250, in _init
File "/usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py", line 1450, in reflecttable
File "/usr/lib/python2.5/site-packages/sqlalchemy/engine/default.py", line 122, in reflecttable
File "/usr/lib/python2.5/site-packages/sqlalchemy/engine/reflection.py", line 332, in reflecttable
File "/usr/lib/python2.5/site-packages/sqlalchemy/schema.py", line 202, in __new__
File "/usr/lib/python2.5/site-packages/sqlalchemy/schema.py", line 248, in _init
File "/usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py", line 1161, in reflecttable
File "/usr/lib/python2.5/site-packages/sqlalchemy/engine/base.py", line 1450, in reflecttable
File "/usr/lib/python2.5/site-packages/sqlalchemy/engine/default.py", line 122, in reflecttable
File "/usr/lib/python2.5/site-packages/sqlalchemy/engine/reflection.py", line 360, in reflecttable
File "/usr/lib/python2.5/site-packages/sqlalchemy/util.py", line 666, in __getitem__
KeyError: u'sys_nc00021$'
This code works perfectly with SQLAlchemy 0.5.x branch:
stromnov@localhost:~$ python2.5 test.py
0.5.5
Comments (8)
-
repo owner -
Account Deleted - changed status to open
- removed status
Sorry, I can't provide full schema according to NDA.
But I'd figured out, that exception raised after retrieving of column names. This is last log record:
2009-08-23 13:26:39,270 INFO sqlalchemy.engine.base.Engine.0x...992c SELECT a.index_name, a.column_name, b.uniqueness FROM ALL_IND_COLUMNS a INNER JOIN ALL_INDEXES b ON a.index_name = b.index_name AND a.table_owner = b.table_owner AND a.table_name = b.table_name WHERE a.table_name = :table_name AND a.table_owner = :schema ORDER BY a.index_name, a.column_position 2009-08-23 13:26:39,271 INFO sqlalchemy.engine.base.Engine.0x...992c {'table_name': 'MAILBOX', 'schema': 'MAIL'}
This is results of this query:
In [6](6): engine.execute(query).fetchall() 2009-08-23 14:29:13,005 INFO sqlalchemy.engine.base.Engine.0x...576c SELECT a.index_name, a.column_name, b.uniqueness FROM ALL_IND_COLUMNS a INNER JOIN ALL_INDEXES b ON a.index_name = b.index_name AND a.table_owner = b.table_owner AND a.table_name = b.table_name WHERE a.table_name = 'MAILBOX' AND a.table_owner = 'MAIL' ORDER BY a.index_name, a.column_position 2009-08-23 14:29:13,005 INFO sqlalchemy.engine.base.Engine.0x...576c {} Out[6](6): ['FID', 'NONUNIQUE'), ('I_MAILBOX_FLDRCVD', 'SYS_NC00021$', 'NONUNIQUE'), ('I_MAILBOX_STID', 'ST_ID', 'NONUNIQUE'), ('I_MBOX_THREADID', 'THREAD_ID', 'NONUNIQUE'), ('PK_MAILBOX', 'MID', 'UNIQUE')](('I_MAILBOX_FLDRCVD',)
But there is not such field in schema named as 'SYS_NC00021$'.
In [8](8): engine.execute("""SELECT * FROM ALL_IND_COLUMNS WHERE TABLE_NAME = 'MAILBOX'""").fetchall() Out[8](8): ['PK_MAILBOX', 'MAIL', 'MAILBOX', 'MID', 1, 22, 0, 'ASC'), ('MAIL', 'I_MBOX_THREADID', 'MAIL', 'MAILBOX', 'THREAD_ID', 1, 22, 0, 'ASC'), ('MAIL', 'I_MAILBOX_STID', 'MAIL', 'MAILBOX', 'ST_ID', 1, 100, 100, 'ASC'), ('MAIL', 'I_MAILBOX_FLDRCVD', 'MAIL', 'MAILBOX', 'FID', 1, 22, 0, 'ASC'), ('MAIL', 'I_MAILBOX_FLDRCVD', 'MAIL', 'MAILBOX', 'SYS_NC00021$', 2, 12, 0, 'DESC')](('MAIL',)
I'd found some docs, referring to 'SYS_NC*' columns:
When you create a function based index, oracle creates a hidden column on a base table. Oracle creates this column as COL# as 0 and NAME as SYS_NC<Oracle Assign Number>$.
-
Account Deleted Exact code, as I mentioned above is:
stromnov@localhost:~$ cat test.py # -*- coding: utf-8 -*- import sqlalchemy print sqlalchemy.__version__ login, passwd, db = ('login', 'passwd', 'db') engine = sqlalchemy.create_engine("oracle://%s:%s@%s" % (login, passwd, db), convert_unicode=True, optimize_limits=True, assert_unicode=True) metadata = sqlalchemy.MetaData(engine, reflect=True)
-
repo owner - changed title to Oracle function-based index reflection fails
OK can you please provide DDL to create a function-based index in oracle which, upon reflection, recreates that error ? thanks. the issue does not occur in 0.5 because 0.5 does not reflect indexes on Oracle.
-
Account Deleted Here is snippet, reproducing index reflection:
stromnov@localhost:~$ cat sa_bug_1513.py # -*- coding: utf-8 -*- import sqlalchemy print sqlalchemy.__version__ login, passwd, db = ('login', 'passwd', 'db') engine = sqlalchemy.create_engine( "oracle://%s:%s@%s" % (login, passwd, db), echo = True ) query = """ CREATE TABLE TEST_INDEX_REFLECT ( DATA VARCHAR2(20) NOT NULL, CONSTRAINT TEST_INDEX_REFLECT_PK PRIMARY KEY ( DATA ) ENABLE ) """ engine.execute(query) query = """ CREATE INDEX DATA_IDX ON TEST_INDEX_REFLECT (UPPER(DATA)) """ engine.execute(query) metadata = sqlalchemy.MetaData(engine) # XXX: This one fails - no such table (bug or feature?) # metadata.reflect(only=['TEST_INDEX_REFLECT']('TEST_INDEX_REFLECT')) metadata.reflect(only=['test_index_reflect']('test_index_reflect')) stromnov@localhost:~$
PS. Note, table created in upper-case, but can be reflected using only lower-case naming.
-
repo owner Replying to guest:
PS. Note, table created in upper-case, but can be reflected using only lower-case naming.
here's from the API docs for Table:
Names which contain no upper case characters will be treated as case insensitive names, and will not be quoted unless they are a reserved word. Names with any number of upper case characters will be quoted and sent exactly. Note that this behavior applies even for databases which standardize upper case names as case insensitive such as Oracle.
-
repo owner - changed status to resolved
I added a regexp to skip those names in fb6be4d35906ccc822634a7657ec109b5d8cbb7f. While it might be nicer for get_indexes() to include this information in the result, its not really compatible with the current reflection "columns" collection and I'd rather not allow that list to include non-column names.
-
repo owner - removed milestone
Removing milestone: 0.6.0 (automated comment)
- Log in to comment
hi -
you'll have to put some more effort into describing your issue. Need to know:
the full, exact schema of your oracle database, including all tables, synonyms, owners, etc.
exact code to reproduce the problem.
All reflection tests pass with oracle: