Re: Give user index grants Oracle 8/9i

Thanks for your responses. I can see that the table's constraints are available to a non-schema owner through ALL_INDEXES.

The problem is that I've got a legacy app I've been given to manage, and the app queries tables using synonyms via ADO. While it appears to be set up properly on the Oracle side, when the app queries a table, i.e. "Select * from SOMETABLESYNONYM", the result set has no primary key, and you can't tell ADO manually what field(s) constitue the primary key.

The primary key in ADO is denoted by the "KEYCOLUMN" property of the primary key field, which should be TRUE for the primary key field, but is FALSE when the table is queried the above way. This is what I mean by ADO can't resolve the primary key information. It's very frustrating to say the least to not be able to manually set this information once you perform the query. If you query the above table by i.e. "Select * from <TABLE_OWNER>.<SOMETABLE>", the "KEYCOLUMN" property returns TRUE like it should for the primary key.

So, the reason why this is so important is because I update the ADO recordsets using adCriteriaKey, which needs the primary key! If AEO can't find one, the update will fail if another user has changed any other field on the record, which is a concurrency nightmare.

I will look over maybe including the primary key constraint in the query by pulling it from ALL_INDEXES, and maybe that will help ADO resolve the primary key properly. I would rather not rewrite all the queries in this app, but it may be necessary since ADO fumbles the primary keys on the synonymmed (is that a word?) tables. Received on Sat Nov 18 2006 - 12:44:50 CST