| ORA-00942 table or view does not exist [message #261768] |
Thu, 23 August 2007 09:43  |
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 #261790 is a reply to message #261768] |
Thu, 23 August 2007 10:56  |
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 #261803 is a reply to message #261768] |
Thu, 23 August 2007 11:33 |
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 #261809 is a reply to message #261808] |
Thu, 23 August 2007 11:46 |
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 |
Frank
Messages: 7901
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 #262151 is a reply to message #261768] |
Fri, 24 August 2007 13:16 |
 |
BlackSwan
Messages: 26766
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>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
