Using some data dictionary views in PLSQL with ORA-00942 occuring

When you try to use some data dictionary views such as v$, dba_ in a procedure, you may receive an error “ORA-00942: table or view does not exist”. At the same time, you can select the views within SQL under the same schema. I don’t understand the reason, while I find a approach to solve the problem, which you need to grant certain privileges to the user explicitly. [@more@]  

[oracle@oracleserver oracle]$ sqlplus s/s

 

SQL> select value from v$parameter where name='shared_servers';

 

VALUE                                                                          

--------------------------------------------------------------------------------

1                                                                              

 

SQL> create or replace procedure p_test

  2  is

  3  v varchar2(512);

  4  begin

  5  select value into v from v$parameter where name='shared_servers';

  6  end;

  7  /

 

Warning: Procedure created with compilation errors.

 

SQL> show error

Errors for PROCEDURE P_TEST:

 

LINE/COL ERROR                                                                 

-------- -----------------------------------------------------------------     

5/1      PL/SQL: SQL Statement ignored                                         

5/26     PL/SQL: ORA-00942: table or view does not exist                       

 

SQL> exit

 

[oracle@oracleserver oracle]$ sqlplus sys as sysdba

 

SQL> grant select on v_$parameter to s;

 

Grant succeeded.

 

SQL> exit

 

[oracle@oracleserver oracle]$ sqlplus s/s

 

SQL> create or replace procedure p_test

  2  is

  3  v varchar2(512);

  4  begin

  5  select value into v from v$parameter where name='shared_servers';

  6  end;

  7  /

 

Procedure created

 

SQL> exit

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/20294/viewspace-980988/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/20294/viewspace-980988/