Can't See Tables in TOAD editor

Toad for Oracle
1

Below is my issue. Hopefully someone can help.

  1. create role roleA;

  2. grant select on SchemaA.TableA to roleA;

  3. grant roleA to SchemaB;

  4. In SQLPlus client window or on the server. logging in as SchemaB:

    Select * from SchemaA.TableA -->> returns all data

  5. In TOAD window. logging in as SchemaB (same as #4):

    Select * from SchemaA.TableA -->> returns ORA-00942: Table or view does not exist

Help? The fact that I can’t do it in TOAD, but can in SQLPlus client window AND on the server tells me that there is something/setting in TOAD that’s preventing me from seeing the data. Anyone has any idea what that is?

2

I don’t think you have to reconnect after performing a grant, but you might want to disconnect and reconnect in Toad.

If that’s not it, you might try running a “SET ROLE ROLEA” command after you’ve connected as SchemaB, although if you don’t have to do that in SQL*Plus, you shouldn’t have to do that in Toad either.

3

I can reproduce the ORA-942 in Toad (as well in SQL*Plus) but only when the role is not default. The following query shows “NO” for the DEFAULT_ROLE field. When I make the role default then “select * from schema.table” returns data as expected. I don’t know of anything specific in Toad that would change that other than on the “Oracle|Roles and Editions” page in options. Do you have anything set in there?

SELECT
*

FROM USER_ROLE_PRIVS

WHERE
GRANTED_ROLE =
‘TEST_ROLE’

From: zoom5 [mailto:bounce-zoom5@toadworld.com]

Sent: Wednesday, December 09, 2015 3:17 PM

To: toadoracle@toadworld.com

Subject: [Toad for Oracle - Discussion Forum] Can’t See Tables in TOAD editor

Can’t See Tables in TOAD editor

Thread created by zoom5

Below is my issue. Hopefully someone can help.

  1. create role roleA;

  2. grant select on SchemaA.TableA to roleA;

  3. grant roleA to SchemaB;

  4. In SQLPlus client window or on the server. logging in as SchemaB:

    Select * from SchemaA.TableA -->> returns all data

  5. In TOAD window. logging in as SchemaB (same as #4):

    Select * from SchemaA.TableA -->> returns ORA-00942: Table or view does not exist

Help? The fact that I can’t do it in TOAD, but can in SQLPlus client window AND on the server tells me that there is something/setting in TOAD that’s preventing me from seeing the data. Anyone has any idea what that is?

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

4

Thanks, guys. I did a bunch of things that seemed to have resolved my issue. I’m not sure which of the “bunch of things” I did actually fixed it. I rebooted TOAD. I also set the default_role to “YES” but I’m not sure if it was set to “YES” to begin with or not. Any way, much appreciate the responses.