OCIError: ORA-00918: column ambiguously defined

Rails
1

Hi all,
when I execute the command “rake db:schema:dump” i get this error in the
db/schema.rb file.

Could not dump table “region” because of following

ActiveRecord::StatementInvalid

OCIError: ORA-00918: column ambiguously defined:

SELECT lower(i.index_name) as index_name, i.uniqueness, lowe
r(c.column_name) as column_name
FROM user_indexes i, user_ind_columns c
WHERE i.table_name = ‘REGION’
AND c.index_name = i.index_name
AND i.index_name NOT IN (SELECT index_name FROM
user_constraints WHERE constraint_type = ‘P’)
ORDER BY i.index_name, c.column_position

I have the same error message in the db/schema.rb file for all the
database tables (OCIError: ORA-00918: column ambiguously defined).

could you help me please ? thanks

2

The Allnighter wrote:

Hi all,
when I execute the command “rake db:schema:dump” i get this error in the
db/schema.rb file.

Could not dump table “region” because of following

ActiveRecord::StatementInvalid

OCIError: ORA-00918: column ambiguously defined:

SELECT lower(i.index_name) as index_name, i.uniqueness, lowe
r(c.column_name) as column_name
FROM user_indexes i, user_ind_columns c
WHERE i.table_name = ‘REGION’
AND c.index_name = i.index_name
AND i.index_name NOT IN (SELECT index_name FROM
user_constraints WHERE constraint_type = ‘P’)
ORDER BY i.index_name, c.column_position

I have the same error message in the db/schema.rb file for all the
database tables (OCIError: ORA-00918: column ambiguously defined).

could you help me please ? thanks

change the sql from
SELECT lower(i.index_name) as index_name, i.uniqueness, lowe
r(c.column_name) as column_name
FROM user_indexes i, user_ind_columns c
WHERE i.table_name = ‘REGION’
AND c.index_name = i.index_name
AND i.index_name NOT IN (SELECT index_name FROM
user_constraints WHERE constraint_type = ‘P’)
ORDER BY i.index_name, c.column_position
to
SELECT lower(i.index_name) as index_name, i.uniqueness, lowe
r(c.column_name) as column_name
FROM user_indexes i, user_ind_columns c
WHERE i.table_name = ‘REGION’
AND c.index_name = i.index_name
AND i.index_name NOT IN (SELECT uc.index_name FROM
user_constraints uc WHERE uc.constraint_type = ‘P’)
ORDER BY i.index_name, c.column_position

3

change the sql from
SELECT lower(i.index_name) as index_name, i.uniqueness, lowe
r(c.column_name) as column_name
FROM user_indexes i, user_ind_columns c
WHERE i.table_name = ‘REGION’
AND c.index_name = i.index_name
AND i.index_name NOT IN (SELECT index_name FROM
user_constraints WHERE constraint_type = ‘P’)
ORDER BY i.index_name, c.column_position
to
SELECT lower(i.index_name) as index_name, i.uniqueness, lowe
r(c.column_name) as column_name
FROM user_indexes i, user_ind_columns c
WHERE i.table_name = ‘REGION’
AND c.index_name = i.index_name
AND i.index_name NOT IN (SELECT uc.index_name FROM
user_constraints uc WHERE uc.constraint_type = ‘P’)
ORDER BY i.index_name, c.column_position
thanks for the reply.
i’ve tried to execute this query with ScratchPad (Oracle Enterprise
Manager), but i get the error : ORA-00904 : Invalid Column Name
even if I get this query working, how can i tell rake to modify this
generated request ?

4

The A. wrote:

even if I get this query working, how can i tell rake to modify this
generated request ?

The original query works fine in the versions of Oracle I’ve tested.
What version are you running?