Enabling Read Only Access on a View to a User in Oracle

Have you gotten an ORA-00942 fault? I'll explain the cause and the solution of the error in this commodity.

ORA-00942 Cause

The error message appears when you attempt to run an SQL statement:

ORA-00942: tabular array or view does not be

This happens for ane of many reasons:

• The argument references a table or view that does not exist
• You do not take access to that table or view
• The table or view belongs to a different schema and you did not refer to the schema name
• You're running Oracle 12c, using a sequence as a default value, but don't have select privileges on the sequence.

The cause of the error should be the same in each database version. Information technology shouldn't matter if you're getting this "table or view does not exist" error in Oracle 10g, Oracle 11g, or Oracle 12c.

The just difference is the sequence-related cause mentioned above, considering i of the new features in Oracle 12c is the ability to utilise a sequence as a default value.

Let's take a look at some of the solutions, depending on the crusade.

ORA-00942 Solution

At that place are several solutions for this error, depending on the cause.

Offset, check that the tabular array exists. You lot tin can practice that by running this query:

                SELECT owner, object_name, object_type FROM all_objects WHERE object_type IN ('TABLE','VIEW') AND object_name = 'OBJECT_NAME';              

Substitute the discussion OBJECT_NAME with your table proper name. Information technology must be in upper example as well.

                SELECT possessor, object_name, object_type FROM all_objects WHERE object_type IN ('Table','VIEW') AND object_name = 'CLASS';              

Results:

OWNER	OBJECT_NAME	OBJECT_TYPE
Arrangement	Class	TABLE

If your tabular array does not prove, so it does non exist, and you'll demand to look into why it doesn't be.

Or, if you're using SQL Programmer, yous can bank check the table exists by expanding the Tables section on the left side of the screen. If you see the table there, information technology means information technology exists and you're the possessor.

Next, cheque the owner of the tabular array.

If the table exists, and you're getting this error, then check the possessor of the table.

Yous can use the same query as to a higher place, and accept note of the possessor of the tabular array.

If the owner is not y'all, then you'll need to contact the database administrator to request privileges to select from the tabular array (or to perform whatever functioning you were trying to do).

Finally, cheque your query to ensure it refers to the correct schema.

If the table or view exists, and yous accept the privileges you lot need, so it could be an issue in your query.

Let'southward say your username is "bob". You lot have a prepare of tables nether the "bob" schema.

If y'all desire to select from a table called "employee", and this is in the "mary" schema, it is owned by "mary". When you refer to the table (such as in a SELECT statement), yous might have a query like this:

                SELECT * FROM employee;              

You might become the ORA-00942 error at this point. This is considering Oracle is looking in your schema, or "bob", for an employee table. Just, it doesn't exist in your schema – it's in the "mary" schema.

So, you'll need to modify your query to include the schema name.

                SELECT * FROM mary.employee;              

This query should run without the error.

Oracle 12c and Sequences

If you're getting the ora-00942 table or view does not exist in Oracle 12c, then it could be caused by this situation:

• Another user has a table and a sequence
• One of the columns in the tabular array has a default value of the sequence.nextval
• You have the correct privileges on the tabular array

Withal, y'all tin go this error if you're querying this tabular array and don't have select privileges on the sequence.

Consider this situation:

As user "bob":

                CREATE SEQUENCE sequence_book_id;  CREATE Table books (   book_id NUMBER(v) DEFAULT sequence_book_d.nextval Chief Primal,   title VARCHAR2(100) );  GRANT SELECT, INSERT, UPDATE, DELETE ON books TO "mary";              

Now, logged in as "mary":

                INSERT INTO books (championship) VALUES ('The Adventure');              

Yous'll get an ORA-00942 fault here.

The reason for this is that "mary" doesn't have SELECT privileges on sequence_book_id. She has INSERT privileges on the table, but as a event of inserting into the tabular array, a SELECT on the sequence is called, which causes this error.

To resolve this, grant SELECT privileges to the second user.

                GRANT SELECT ON sequence_book_id TO mary;              

That should now work.

I hope this article has helped you resolve the ORA-00942 error.

Lastly, if yous savour the information and career communication I've been providing,sign up to my newsletter below to stay up-to-date on my manufactures. You'll too receive a fantastic bonus. Thanks!

dicksonbubtroge.blogspot.com

Source: https://www.databasestar.com/ora-00942/

Share this post