Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00942 table or view does not exist
ORA-00942 table or view does not exist [message #261768] Thu, 23 August 2007 09:43 Go to next message
tsp120
Messages: 18
Registered: November 2005
Junior Member
In the environment I am currently working in we get this error quite often (usually through PLSQL procedures that create views in an EXECUTE IMMEDIATE statement). Sometimes its due to tables not having been created on a particular schema or the correct privileges not being granted.

The problem is the error does not suggest which table or view it was unable to find. I understand that for basic security reasons you don't want to give this information out as it could provide valuable information to a hacker.

But that is not a concern in this environment and we would GREATLY benefit from being able to extract exactly which table or view the code was unable to find. Often times we are creating views or running queries that are selecting from MANY tables and it proves to be too much of a hassel to figure this out manually.

Is there any way to find this this table or view name through something in Oracle or programmatically?

Thanks!
Re: ORA-00942 table or view does not exist [message #261770 is a reply to message #261768] Thu, 23 August 2007 09:51 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Often times we are creating views or running queries that are selecting from MANY tables and it proves to be too much of a hassel to figure this out manually.
You reap what you sow.
There rarely is any (good?) reason to do this in an Oracle application.
When you find yourself in a hole, the 1st thing you should do is STOP DIGGING.
I suspect that by doing so you are not using bind variable in the SQL statements above; which results in a non-scalable "application".

Doctor, it hurts when I poke myself in the eye. How can I stop the pain?

Your solution is rather obvious. Stop poking yourself in the eye!
Re: ORA-00942 table or view does not exist [message #261790 is a reply to message #261768] Thu, 23 August 2007 10:56 Go to previous messageGo to next message
tsp120
Messages: 18
Registered: November 2005
Junior Member
Though you may be right its out of my hand.

With that said, is there anyway for me to do this?

Below is a simplified example of what I would like to do:


CREATE OR REPLACE PROCEDURE test_errors IS

v_id          VARCHAR2(15);
v_firsttable  VARCHAR2(100);
v_secondtable VARCHAR2(100);
v_sql         VARCHAR2(1000);


BEGIN

v_firsttable  := 'SCHEMA.TAB1';
v_secondtable := 'SCHEMA.TAB2';
  
v_sql := 'CREATE OR REPLACE VIEW testing_view AS ';
v_sql := v_sql || ' SELECT * ';
v_sql := v_sql || ' FROM ' || v_firsttable || ' a, ' || v_secondtable || ' b ';

EXECUTE IMMEDIATE(v_sql);

END test_errors;


Let's say I don't have the permissions for TAB2. What I get back is the following error:

ORA-00942: table or view does not exist
ORA-06512: at "PERSONALSCHEMA.TEST_ERRORS", line 18
ORA-06512: at line 1



I would like to find a way for it to tell me it is TAB2 that is causing me the problem.

is this possible?

[Updated on: Thu, 23 August 2007 10:57]

Report message to a moderator

Re: ORA-00942 table or view does not exist [message #261794 is a reply to message #261790] Thu, 23 August 2007 11:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I would like to find a way for it to tell me it is TAB2 that is causing me the problem.

Isn't it in the error message?

Regards
Michel
Re: ORA-00942 table or view does not exist [message #261803 is a reply to message #261768] Thu, 23 August 2007 11:33 Go to previous messageGo to next message
tsp120
Messages: 18
Registered: November 2005
Junior Member
No, the error message is this:

ORA-00942: table or view does not exist
ORA-06512: at "PERSONALSCHEMA.TEST_ERRORS", line 18
ORA-06512: at line 1

How am I to know if it's TAB1 or TAB2? Obviously in my real examples there are more tables than just two...but this gives you an idea of the frustration it can cause.
Re: ORA-00942 table or view does not exist [message #261807 is a reply to message #261803] Thu, 23 August 2007 11:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Bad design for bad design: try to create a temporary view on each table before the real one.

Regards
Michel
Re: ORA-00942 table or view does not exist [message #261808 is a reply to message #261803] Thu, 23 August 2007 11:44 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
How did you grant the privileges for the tables to this schema?
Through a role?

By
Vamsi
Re: ORA-00942 table or view does not exist [message #261809 is a reply to message #261808] Thu, 23 August 2007 11:46 Go to previous messageGo to next message
tsp120
Messages: 18
Registered: November 2005
Junior Member
I personally don't set the privileges. That's part of the problem. We release the software on one system and everything is fine. We release it on another (where privileges or a table are missing) and it crashes with the error above.

But I do know that we use roles.
Re: ORA-00942 table or view does not exist [message #261902 is a reply to message #261809] Fri, 24 August 2007 00:34 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
tsp120 wrote on Thu, 23 August 2007 18:46
We release the software on one system and everything is fine.

No, it is not fine. It does not throw errors, which is something quite different.
Unless this is part of your installation-program you should not do ddl in your application.
Re: ORA-00942 table or view does not exist [message #261957 is a reply to message #261768] Fri, 24 August 2007 02:58 Go to previous messageGo to next message
puneet.kakkar
Messages: 11
Registered: February 2007
Junior Member
As far as i understand your problem, you can first check for all the tables/views(used in query to create View on run time), if they exist or you have access from your schema and then issue the DDL statement to create views.
Re: ORA-00942 table or view does not exist [message #262030 is a reply to message #261957] Fri, 24 August 2007 06:35 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
That would not solve the actual problem. It would only solve the symptoms, as you would no longer get an error. The views however would not be created.
Re: ORA-00942 table or view does not exist [message #262151 is a reply to message #261768] Fri, 24 August 2007 13:16 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
in V11 the error message is MUCH more helpful

  1* SELECT DB.COL1, ID.COL1 FROM DBADMIN.TEST2 DB, TEST1 ID WHERE DB.ID1 = ID.ID1
11:14:32 SQL> /
SELECT DB.COL1, ID.COL1 FROM DBADMIN.TEST2 DB, TEST1 ID WHERE DB.ID1 = ID.ID1
                                     *
ERROR at line 1:
ORA-00942: table or view does not exist


11:14:34 SQL> 
Previous Topic: how to find all packages and procedures form user_objects
Next Topic: ORA-00979: not a GROUP BY expression
Goto Forum:
  


Current Time: Fri Dec 09 21:20:13 CST 2016

Total time taken to generate the page: 0.07264 seconds