Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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 .

like image 469
Gaurav Soni Avatar asked Apr 11 '12 06:04

Gaurav Soni


People also ask

How resolve table or view does not exist in SQL?

So how can we solve it? You can easily solve this error either by creating the missing object (by object I mean a table, view, synonym or a cluster) or by importing it from another source into your schema.

How do I fix error ORA 00955?

Resolving The Problem If you have created a database object such as a table, view, index, or synonym that already exists you will receive this error. The database objects must have distinct names. You need to have a unique name for the database object or modify or drop the existing object so it can be reused.

What could be the reason for failure with error table or view does not exist in Informatica?

This error will occur when the table name prefix is not specified for the table. The Table name prefix is a requirement for loading in bulk mode for Oracle 9i. 1) For Solution, enter CR with a Workaround if a direct Solution is not available.


2 Answers

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
like image 124
Korhan Ozturk Avatar answered Sep 19 '22 21:09

Korhan Ozturk


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.

like image 44
a_horse_with_no_name Avatar answered Sep 21 '22 21:09

a_horse_with_no_name