Home » SQL & PL/SQL » SQL & PL/SQL » DBA_VIEWS giving ORA-00942 Error (Oracle Database 12c 12.1.0.2.0 - 64bit Production )
DBA_VIEWS giving ORA-00942 Error [message #659211] Mon, 09 January 2017 10:30 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 65844
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 #659213 is a reply to message #659212] Mon, 09 January 2017 10:53 Go to previous messageGo to next message
abhi_orcl
Messages: 40
Registered: December 2016
Member
Great..thanks Michel..I granted direct privilege and it worked.
I think its time again to revisit the basics
Re: DBA_VIEWS giving ORA-00942 Error [message #659316 is a reply to message #659213] Thu, 12 January 2017 08:01 Go to previous message
Bill B
Messages: 1802
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.
Previous Topic: add a sequential number to a query
Next Topic: Help to write the SQL query to
Goto Forum:
  


Current Time: Thu Oct 18 21:41:20 CDT 2018