PL/SQL: ORA-00942: table or view does not exist V$SQL

Question

I have created a procedure and used the below statement inside that .

select sql_id into v_sql_id from v_$sql where sql_text =v_sql;

I am getting the following error PL/SQL: ORA-00942: table or view does not exist

I have checked the synonym its owner is PUBLIC,so it should run in this case ,but its not working .

Another thing i can select sql_id from v_$sql where sql_text =v_sql; this in simple editor .Can anyone help me with this .

Answer 1

Database dictionary related or system tables (v_$sql in this case) are owned by Oracle sys user and needs special privileges to access them. You need to login to oracle database as sysdba user or get those privilages (your DBA might help you with this) to get access for the data dictionary views.

As mentioned in this article

The problem is that procedures don't respect roles; only directly granted rights 
are respected. So, that means that table_owner has to regrant the right to select

So, try the following to grant the SELECT on all dictionay view so that you can use it in your pl/sql blocks.

grant select any dictionary to USERNAME

Answer 2

Most probably the user you are using was given access to the view through a role rather than the select privilege directly.

Unfortunately privileges obtained through a role are not active when running PL/SQL.

You need to ask your DBA to grant the SELECT privilege directly to your user.