This content has been marked as final. Show 8 replies
Wait! I notice this works correctly against an 11g database and incorrectly against a 10g database.
Hmmm ... the thot plickens.
Just did a quick check viewing a couple of triggers in the standard HR schema against 10g XE and 126.96.36.199. Both show the same dependencies, but no references. Are there any other details you can provide about your 10g case?
I was too quick by half ...
Against 10g ... if I start from a view with ISO trigger ...
Triggers tab has trigger, I right click, choose "Go To [trig name]" that is fine.
Once on trigger, References tab has nothing,
Dependencies tab has a zoo of stuff ... repeated over and over for no apparent reason.
IF I filter Dependencies on VIEW ... then I see all this stuff I would not have thought had ANY relation
to the trigger (to the caller view ... maybe, but its refs/dependencies appeared fine!).
And, after I filter, I can find the original starting point view.
Against 10g ... if I start from any trigger, and look at its Refs/Dependencies tabs ...
same deal: No Refs, tons of Deps, no clue as to why, must filter for sense.
Against 11g, same behaviors.
Cursory glance at select parts ...
Views have both Refs and Deps.
Tables have only Deps, no Refs.
Packages Specs have Table Deps, other action Refs.
Package Bodies have Synonym Dep, long long list of Refs (a la the Triggers).
Procedures and Functions seem to have both Refs and Deps in correct proportion.
Sequences have correct Refs, no Deps.
Types seem to be correct, we don't have a lot of these, so am not sure.
Public Synonyms shows ALL Pub Syns, not just those of the current schema.
All true for 10g and 11g ...
My conclusion: something is amiss with how Trigger and Package Body Refs / Dependencies are handled.
As in ... they are not to be trusted (at least, not thru SQL Developer) ... thus a bug.
That's all I know at moment, more later, if I see other things.
I suppose there is always the possibility of a bug, but in this case the object viewer definitely uses more than just one of the dependencies views to get its information. If you want to see exactly what the data dictionary says about such dependencies, go to View -> Reports -> Data Dictionary Reports -> Dependencies, get the report for the schema in question, then filter by the desired object name/type in the query result grid.
In the object viewer the SQL query is much more complex. It includes a join on the public_dependency view with a start with ... connect by nocycle prior. So the seemingly extra items under the trigger are due to
1. The view is dependent on the trigger which references (is dependent on) table(s).
2. The View is also dependent on the table(s)
3. The table(s), at the least, get double-counted.
4. The ISO case may be the "perfect storm" for this situation to become noticeable.
If views have multiple triggers, and triggers have multiple tables, the duplication, of course, is compounded.
Hope this helps,
Edited by: Gary Graham on Apr 12, 2013 5:24 PM
Very interesting Gary_Graham!
I will look at that ... I mostly find it interesting that ...
All those other "kinds of things" have no problem with their Refs / Deps.
(including Functions and Procedures)
BUT ... triggers and package body text ... THAT has a problem.
Which says to me that that "connect by" stuff (which I know is powerful, but which I am only learning (we use it in some places to flatten multi-record items to one row ... a bit of code we can ditch with new funcitonality in 11g, but ... we're not there yet)) ... is not quite right.
If there were just multiples of things "that I knew" were clearly related to all these triggers (and it is not just ISO triggers, that's just that's where I started looking) ... that would be one thing.
But no ... there stuff in there I have no idea how it's related to the trigger ... and since I vetted every inch of code in our database ... I really do have no idea (we're not talking Emp-Dept ... this is a calendaring / scheduling app of multi-related functionality (Oh, you have THAT calendar? can I use your database too, so my work can be shared with the already existing work? ... and so it has acreted purpose over the years)).
I will dig deeper into those things next week. Thanks for that info.
stuff in there I have no idea how it's related to the triggerCould those be synonyms? For Oracle DB 10 and higher, the query should also return any synonyms.
Gary, can images be posted here? Then you could see what I mean.
It's not related to synonyms, not that's obvious to me.
Tonight, I will post them to my Facebook page, and link them here ... unless there's another way.
I have no particular web-based drive space.
Thanks for your time,
can images be posted here?No, but I'm hoping a soon-to-come upgrade to the external Oracle forums may permit it:
Re: We are upgrading OTN Forums! This is the place for questions and comments.
the internal Oracle forums already do. You may want to search the web for some free service to host those images that is more anonymous than a Facebook post...