Oracle – for when it was like that when you got there

Main menu

Post navigation

Sinister Synonyms and Dependencies in Oracle

It’s been quite an eventful week. Deb has got her results back and is now officially a lady of “Distinction” (two of them, no less). Even Wales’ narrow defeat to England in the Rugby hasn’t put a dent in her good mood.
I, on the other hand, found myself doing my Marvin-the-paranoid-android-as-a-DBA impression the other day….”Synonyms. Loathe them or hate them, you can’t ignore them”.
Now, whilst synonyms definitely have their uses, they can be something of a double-edged sword.
The cause of this particular downbeat assessment of their merits was the fact that I’d deployed my CRUD tool on a new database, but it had failed to pick up some dependencies.
Let’s have a closer look at this issue and see how Oracle’s own DBMS_UTILITY copes with these circumstances.

A Test Case

OK, we need a table, a synonym on the table, and a database object that references the synonym…

Nothing up my sleeve…

At this point, it’s probably worth mentioning DBMS_UTILITY.GET_DEPENDENCY, which does actually understand about synonyms…despite it’s own little quirks.
This procedure relies on DBMS_OUTPUT to return it’s findings ( none of that boring OUT parameter stuff, oh no)…

That’s handy, we’ve now got our complete dependency hierarchy – i.e. that the function is dependent on the table via the synonym. However, we can’t dictate the output format so using it programatically is going to be a bit fiddly.
Furthermore, look what happens when the synoym is not owned by the object owner…

By plugging this query into the CRUD procedure everything is now working. I’ll post the complete updated code separately. In the meantime, it’s Saturday evening and I’ve promised someone that I’ll pretend I have a life.