DBA_VIEWS giving ORA-00942 Error [message #659211] |
Mon, 09 January 2017 10:30 |
|
abhi_orcl
Messages: 40 Registered: December 2016
|
Member |
|
|
Hi everyone,
I am facing issue with DBA_VIEWS table. Actually, if I am using the same in a procedure and compling the procedure code,
I am getting the "ORA-00942: table or view does not exist.Below is the procedure:
create or replace procedure t_user.test_v
as
v_row number := 0;
begin
select 1 into v_row
from dual
where exists
(select null
from dba_views
where view_name = 'TEST_V'
and owner = 'TEST');
dbms_output.put_line(v_row);
end;
But when I am running the SQL query as standalone, it runs perfectly and gives me the result:
select 1
from dual
where exists
(select null
from dba_views
where view_name = 'TEST_V'
and owner = 'TEST');
Just to add further, when I am replacing DBA_VIEWS with ALL_VIEWS the procedure runs successfully.
The user t_user is a DBA user so there should not be any privilege issue.
Moreover, the same user is able to select from the table when running it as standalone.
Could someone advise on the issue?
[Updated on: Mon, 09 January 2017 10:32] Report message to a moderator
|
|
|
Re: DBA_VIEWS giving ORA-00942 Error [message #659212 is a reply to message #659211] |
Mon, 09 January 2017 10:40 |
|
Michel Cadot
Messages: 68694 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
1/ By default, roles are not enabled in a procedure and so privileges acquired by roles are not active.
2/ You must have direct privilege on this view to be able to statically use it in a procedure, so DBA is not sufficient (nor necessary).
[Updated on: Mon, 09 January 2017 10:42] Report message to a moderator
|
|
|
|
Re: DBA_VIEWS giving ORA-00942 Error [message #659316 is a reply to message #659213] |
Thu, 12 January 2017 08:01 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
You could also create the procedure in a schema that does have access to the DBA views and create it with DEFINER rights and then grant execute to any schema you want. This blocks the user from seeing the dba view, but allows the procedure to still access them.
|
|
|