Re: Give user index grants Oracle 8/9i

Sean wrote:
> 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.

Personally I am not real fond of using synonyms to hide the schema owner part of a query.

For your specific circumstances ( thanks for the longer explanation ) perhaps you can get by like this. Try creating a view that contains the complete "select * from schema_owner.tablename " ( or a list of specific columns ... the select * has complications when columns are added/modified in a table ).

Then try using the synonym to now point to that view. Does this work for you now?

Perhaps there are microsoft fixes for ADO which would make the keycolumn visible? Received on Sat Nov 18 2006 - 12:01:59 CST