CODEWITHSUNDEEP
oracle-databasecursorora-00942
user2102665
user2102665

Reputation: 505

Query DBA_TABLES and USER_TABLES in PL SQL cursor

I have a cursor in PLSQL procedure to select from DBA_TABLES and USER_TABLES.

CURSOR c1 IS
  SELECT DISTINCT(DB.TABLE_NAME) FROM DBA_TABLES DB, USER_TABLES UT WHERE DB.TABLE_NAME = UT.TABLE_NAME AND DB.IOT_TYPE IS NOT NULL;

BEGIN
  OPEN c1;
  FETCH c1  INTO IOT_TABLE;
  CLOSE c1;

However the procedure complain

"ORA-00942 Table or View does not exist"

during execute.

Upvotes: 2

Views: 6014

Answers (2)

Peter G
Peter G

Reputation: 79

Follow the steps in krokodilko's answer if you just need DBA_TABLES. The SELECT ANY DICTIONARY privilege is another option if you need to query multiple data dictionary objects. It grants select access to the DBA views (including DBA_TABLES). Login as SYSTEM and run:

grant select any dictionary to your-username;

The SELECT ANY DICTIONARY privilege allows the user to query any data dictionary object in the SYS schema, with the exception of: DEFAULT_PWD$, ENC$, LINK$, USER$, USER_HISTORY$, and XS$VERIFIERS.

Incidentally, the c1 cursor in the question probably doesn't need to access DBA_TABLES. The following query should produce the same result:

CURSOR c1 IS
  SELECT TABLE_NAME FROM USER_TABLES WHERE IOT_TYPE IS NOT NULL;

Upvotes: 1

krokodilko
krokodilko

Reputation: 36107

The user has to have SELECT privilege on that view.
To check whether the user has the SELECT permission against this view, login as this user and run the below query:

select * from user_tab_privs WHERE table_name = 'DBA_TABLES';

If this query returns none, then the user has no rights to that view and cannot see it.

In order to grant this privilege to that user, login as SYS or SYSTEM, and issue:

GRANT select ON DBA_TABLES TO username;

After this the user should see a result like below, and will have access to that view:

select table_name, privilege from user_tab_privs WHERE table_name = 'DBA_TABLES';

TABLE_NAME PRIVILEGE                               
---------- ---------
DBA_TABLES SELECT

Upvotes: 3

Related Questions