0

See example:

ORA-00942: table or view does not exist : How do I find which table or view it is talking about

Basically in a case like this Oracle responds with something like: SQL Error: ORA-00942: table or view does not exist

Obscure error messages from Oracle when using an ORM lib like Hibernate aren't exactly a once in a lifetime experience. Why doesn't Oracle simply mention the NAME of the table or view which doesn't exist? Why all the auditing and other complex "solutions" posted in the example question?

In short: Is there some rational, technical explanation for Oracle's seemingly piss poor error feedback, or is this more likely the result of a lack of motivation (on Oracle's part) to improve due to their almost 'monopolistic' popularity status? (Or other? Lack of coordination with ORM devs and DB vendors?)

Actually, this also begs the question of whether other competing (particularly OSS) DBs provide any better feedback, which I have no idea of really, so this may apply to more than just Oracle.

Community
  • 1
  • 1
Manius
  • 3,594
  • 3
  • 34
  • 44
  • Wild guess, but if they made the system easier to use, they would have a hard time selling those expensive support contracts. – mikerobi Oct 03 '10 at 19:12
  • @mikerobi, i don't think people would contact oracle support to find out which object doesn't exist! – Paul Creasey Oct 03 '10 at 19:14
  • @Paul Creasey, you took me way to seriously. – mikerobi Oct 03 '10 at 19:16
  • I sympathise and agree with you, but this question has no answer, and is liable to get closed as a result. – skaffman Oct 03 '10 at 19:25
  • I know that was tongue in cheek but I suspect there is an element of truth to it. This is a very simple example, but it illustrates a case where seemingly no effort would be needed to greatly improve life for certain types of developers, yet we still get back from Oracle, essentially: "Something went wrong!" – Manius Oct 03 '10 at 19:26
  • True above, I guess we aren't allowed to ask "why" on stackoverflow, just "how". How disappointing... – Manius Oct 03 '10 at 19:28
  • 1
    @Crusader: Maybe so, but without that focus, Stackoverflow.com would be useless. – skaffman Oct 03 '10 at 19:30
  • Wouldn't happen to know of a **similarly active** alternative site for such questions would you? :) – Manius Oct 03 '10 at 19:38
  • 1
    Actually, you can get the error position as well as the message so you should be able to work out which 'word' in the query Oracle is referring to. – Gary Myers Oct 04 '10 at 01:19
  • That's true, and that works when you're running plain sql. I think the catch here is that if you're using an ORM lib like Hibernate, there's no known way (by me) to get the exact, full sql statement that was executed, so I believe such error positions are essentially useless. – Manius Oct 06 '10 at 05:58

2 Answers2

3

This lack of table name is probably to help prevent code knowledge leaks. Typical example - if a webapp was badly coded, and an error like this propagated to the top-level & was displayed to the user, then an Evil Person could use that to SQL inject the site or do other bad stuff.

thecoop
  • 45,220
  • 19
  • 132
  • 189
  • Thanks, I think I can accept that (in this case at least). It'd be nice to have a 'debug mode' or something for devs though! – Manius Oct 03 '10 at 19:39
1

"In short: Is there some rational, technical explanation for Oracle's seemingly piss poor error feedback, or is this more likely the result of a lack of motivation (on Oracle's part) to improve due to their almost 'monopolistic' popularity status? "

Bear in mind that Oracle is a venerable technology, stretching back thirty years. There is a huge extant code base. So changing fundamental behaviours could have widespread ramifications.

Whether you regard that as a "rational" or "technical" explanation is a matter of taste.

Most of us who use Oracle databases have learned to live with its quirks. for instance, it is a simple matter to reveal the table responsible for an ORA-00942 error:

prompt Dropping non_existent_table

drop table non_existent_table
/

It's not elegant but then not knowing the condition of our target schema is far from elegant. In fact, blindly issuing DROP TABLE statements is the blunderbuss approach to schema management.

"this also begs the question of whether other competing (particularly OSS) DBs provide any better feedback"

Certainly the equivalent MySQL error, MySQL Error: 1146, names the table. Of course, Oracle now owns MySQL, so its status as a competing DBMS is debatable :)

APC
  • 144,005
  • 19
  • 170
  • 281
  • Not schema management--initial schema generation for the Java dev who has better things to do with their time than hand-code sql scripts that could be generated instead. That said, product age and the difficulty maintaining a large code base makes sense as an explanation. – Manius Oct 03 '10 at 23:41
  • Have you consider iterating over the tables in the data dictionary, dropping them, instead of hand-maintaining a script? – Adam Musch Oct 04 '10 at 02:33