ORA-00942: Ok, but WHICH TABLE?

One of the reasons I parole Twitter is because some of my best blog content derives from everyday problems that everyday people run into.

Ric is one of those people, although I should qualify that Ric is an Oracle ACE with decades of Oracle Tuning experience, and is a teacher at heart. Click on his name above to see a nice story he shares on making Explain Plan and AutoTrace more user friendly in SQL Developer.

#oracle #sql @Oracle would it kill ya to tell me WHICH table or view doesn’t exist with the ORA-00942 error? I have like 170+ tables and inline views in this query, throw me a bone eh? (And no I didn’t write it, I’m trying to tune it)

— Ric Van Dyke 🍺 🎲 (@RicVanDyke) March 19, 2019

Ok, so if you dive into that thread, you’ll see that the database is trying to protect itself. So let’s acknowledge that, but also acknowledge that in this case, we have direct access to the database, and we can run SQL to ask it questions.

How can SQL Developer help here?

I have the HR schema. We all know, and loathe it.

I have also created another user called NO_TABLES. Mr. NO_TABLES has no tables, or really anything else in their schema. All they have is some SELECT privs on a few tables in HR, and some SYNONYMS to take advantage of.

I’ve quickly written a query that works just handy-dandy.

Now what I also need to provide is the Country that these employees are located in. That’s not part of the PLACES object (what’s really HR.LOCATIONS), but rather I need to join to COUNTRIES on the COUNTRY_ID to get the actual name.

Well, that’s easy to code. I’ve known HR for decades, and I don’t need a tool’s help to code that.

What the what? ORA-00942?

There’s nothing wrong with this query. Well, there’s nothing wrong unless you’re an Oracle person who for 20 years has written SQL the way that Larry intended vs this new ANSI stuff, but that’s a different story.

Now there’s a few ways to quickly figure this out. The obvious way is to individually query each table. But that SUUUUUUCKS when your query has 30, 60, or even 300 tables and/or views involved. Don’t laugh – I’ve seen those abominations in the real world more than a few times.

But let’s look at that code again – look closely at the bottom.

And as you’re writing this code, you’ll notice these ‘squiggles’ BEFORE you actually execute the code block.

Since version 17.3, we’ve been using our background parser connection to ask the database if it knows what these objects are – and then warning you if your user can’t see what you’re asking to see.

And how are we doing this?

SYS.DBMS_UTILITY.NAME_RESOLVE() – this function lets you ask the database if the object you are looking for is…available. And it won’t ding you with auditors if you have logging setup such to record attempts to query an object you’re not supposed to query.

We use this same function to see if you have access to the DBA_ views when you login, as they’re much faster for querying the data dictionary than say the ALL_ views.

You can of course see EVERYTHING we’re doing in the Log > Statements panel.

So now you know how to:

• Ask the database if YOU can ‘see’ a table or view – without getting in trouble with ‘the man’ (DBMS_UTILITY)
• See how SQL Developer works with the database (Statements panel)
• Quickly figure out what is causing those pesky ORA-00942’s
• Get me to write a blog post and and name drop you