ITPub博客

首页 > 数据库 > Oracle > Using some data dictionary views in PLSQL with ORA-00942 occuring

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

原创 Oracle 作者:sating 时间:2007-11-06 16:41:52 0 删除 编辑
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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-17

  • 博文量
    8
  • 访问量
    84602