7 Replies Latest reply on Aug 23, 2016 10:42 PM by SM_Work

    Real Q: In Function review in SQL_Dev, References are way inaccurate.

    SM_Work

      Why?

      First off, I am using 4.1.3.20. I use SQL Dev for inspection, compilation, quick fixes.

      I recognize there are zillions of ways to use this wonderful tool. But I am in love with the inspection piece.

      For tables, views, procedures, references / dependencies ... all seem great. But for functions ... not so much.

       

      E.g., I have a "Function A" that takes a key examines all the child-recs for that key and returns a "type" evaluation for the parent-key.

      (for example, if a work-plan has a child-location-record that says the location requires "going slow" aka "a slow speed", then the entire work-plan is flagged as a slow-speed ... if a work-plan uses a vacuum train, then it is of type "VACM" ... etc. -- and the function follows a specific hierarchy of criteria ... the hierarchy or merits of the function are not the issue).

       

      Said function uses 4 different child tables to come to its final conclusion ... these are shown as dependencies, as in, the function is dependent on them. These Dependencies _ARE_ accurate.

       

      But said function "references" ... it points to views, procedures, synonyms ... which when I check them ... they have no reference to the function! So ... I don't know where the reference is coming from ... and when I inspect user_source text ... I find other things entirely

       

      Worse ... there is a right-click jump disparity.

       

      When I right-click on the dependencies ... I have the option to jump to them, great, wonderful.

       

      But ... when I right-click on the references ... no option to jump, nothing ... so then I have to remember the object name / type and find it in a very long list of objects. I did find at least one specific inaccuracy: there is a named section of a different function (function B) with the same section-name as the original function A ... (but no relation to function A) ... a view using function B shows up in Function A's "references" list ... which is wrong.

       

      So ... why the inaccuracies and why no right click at least?

       

      Thanks.

        • 1. Re: Real Q: In Function review in SQL_Dev, References are way inaccurate.
          thatJeffSmith-Oracle

          this information comes from the data dictionary, PUBLIC_DEPENDENCY

           

          I believe you MIGHT be seeing objects which are references or are dependent on your function by multiple levels, so they use an object which uses an object which uses your function...w/o an example, I can't really tell what you're seeing

           

          in either case, SQL Developer isn't computing the dependencies, the database is - we're just showing you what the database thinks

          1 person found this helpful
          • 2. Re: Real Q: In Function review in SQL_Dev, References are way inaccurate.
            SM_Work

            Ok, Jeff ... here's a picture, I hope this helps.
            (and thanks for the pointer to public_dependency ... I am looking at that ... but ... if real dependencies are involved here ... I had no clue ... code-wise, these items are not related ... not the trigger shown, not the view to which it is tied ... so ... I am trying to learn how the database "thinks" ....)

             

            ttfn (and thanks for your attention!)

            SQL_Developer_Items.jpg

            • 3. Re: Real Q: In Function review in SQL_Dev, References are way inaccurate.
              thatJeffSmith-Oracle

              double-click the blue text that will open the object, in that case, those triggers or procedures

               

              if you want to know how the dependencies view is built, I have no idea - it would be a question for the Database forum.

              • 4. Re: Real Q: In Function review in SQL_Dev, References are way inaccurate.
                SM_Work

                OK, Jeff, thanks for the double-click, sorry to be so stupid ... but it is right-click other places.

                As for asking the database team, I will surely do it, and I have learned something about dependencies that I did not know, so, thanks for that.

                 

                ttfn,

                 

                Suzanne

                • 5. Re: Real Q: In Function review in SQL_Dev, References are way inaccurate.

                  Sorry - but I can't understand what your actual question is.

                   

                  Can you clarify EXACTLY what you are asking and what the relevance is of what you posted?

                  I have a "Function A" that takes a key examines all the child-recs for that key and returns a "type" evaluation for the parent-key.

                  Ok - but you didn't post a 'function A' you posted a screen shot of something else altogether.

                  (for example, if a work-plan has a child-location-record that says . . .

                  What does any of that have to do with your question/issue?

                  Said function uses 4 different child tables to come to its final conclusion ... these are shown as dependencies, as in, the function is dependent on them. These Dependencies _ARE_ accurate.

                  Ok - those are objects that the FUNCTION depends on. If those objects are changed or become invalid it will also invalidate your function.

                  But said function "references" ... it points to views, procedures, synonyms ... which when I check them ... they have no reference to the function!

                  Correct - why would they reference the function? They are NOT dependent on the function. It makes NO DIFFERENCE if the function is valid or even exists.

                   

                  Think about it - if you add Jeff's email to your contact list how would Jeff know anything at all about that? A million people could add his email to their contact lists. Jeff won't know, and won't care.

                   

                  So while your email list depends on Jeff's email Jeff is NOT dependent on your contact list.

                   

                  Oracle does NOT track/link objects that depend on an object - only objects that an object depends on.

                  So ... I don't know where the reference is coming from

                  Huh? What 'reference' are you talking about?

                  ... and when I inspect user_source text ... I find other things entirely

                  What 'other things'? Post them.

                  But ... when I right-click on the references ... no option to jump, nothing ...

                  Jump to what? The dependency link is ONLY in one direction. It is NOT doubly linked as I described above about the email list example.

                   

                  I did find at least one specific inaccuracy: there is a named section of a different function (function B) with the same section-name as the original function A ... (but no relation to function A) ... a view using function B shows up in Function A's "references" list ... which is wrong.

                   

                  I have no idea what any of that even means.

                   

                  The image you posted is meaningless unless you tell us what the image represents and what parts of it you are talking about.

                   

                  Just to summarize:

                   

                  If Function A references table B the *.DEPENDENCY views will show that function A 'DEPENDS ON' table B.

                   

                  But those views will NOT show any dependence for table B since table B does NOT depend on function A.

                  • 6. Re: Real Q: In Function review in SQL_Dev, References are way inaccurate.
                    SM_Work

                    OK, RP0428 ... let me try to clarify.

                    There was no need to post function A (or any function for that matter).

                    I thought that saying "SP_TYPE" was just awkward. So I reduced it to Function A.

                     

                    But, yes, the Dependencies and References -- tabs in SQL Developer ... I rely on these a lot for any object in my master schema. THING / OBJECT sits between ... items _it_uses to do whatever (its dependencies) and items that in turn call _it_ to do another thing-or-two (its references).

                     

                    If I change my Object ...the things upon which it depends do not care a hoot.

                    (in this case, SP_Type looks at 4 tables (based on an argument-key), and makes a decision as to what "Service Plan Type" it should report back to its caller ... those 4 tables don't care at all about any changes made to the coding of the SP_Type function). If I go to any of those 4 ... they show SP_Type as a dependent.

                     

                    On the other hand ... various views and triggers and reports and other things ... use that SP_Type Object to decide on further actions / prints / summaries, whatever ... THOSE refer-ers care A LOT about what happens in the innards of SP_Type ... and the kind of info each caller might get back. If I change SP_Type ... e.g., I make it return a 10-char string instead of a 4-char string ... all those callers could potentially de-compile / break for a whole host of reasons.

                     

                    What I just found out, thanks to Jeff is ... that those references contain a _string_ of dependencies, both direct and indirect. E.g., there is _one_ view that contains a call to SP_Type ... that view shows up in the reference list. However ... all of _its_ dependents show up in the list too ... because ... if SP_Type decompiles, it takes down VWServicePlans which in turn takes down at least 10 other views.

                     

                    So ... now I have two new problems: how to accurately tease out the SP_Type direct-callers (like VWServicePlans) from the indirect callers (that view's 10 other views) and how to make direct vs. indirect evident in that references listing ... or in some extract I have yet to devise (I have, for example, a set of views that allow my team to compare our 4 databases, to see differences between them, in terms of data, objects, and arguments ... I wanted to finagle in dependencies).

                     

                    This has _nothing_ to do with whether or not I have Jeff's email in my contact list (I may). Our lists are not linked ... so ... his email would not care (and my lists won't care until / unless I write to bad accounts (and I won't know it unless I write, hence the spam in the world)). When I add my application users to my Active Directory groups ... I see all the old no-longer-valid-addresses for folks I used to know who have moved on in one way, shape, or form. For some, I have them added to my personal email lists (and they indeed know it). For others, I keep them in my prayers.

                     

                    Items _not_ dependent on SP_Type, whether directly or indirectly ... should not show up in that reference list ... what I did not realize until today ... was the "indirect" part. So ... now I have an idea of what I am up against.

                     

                    As a side note: I did post "Function A" ... that's SP_Type in the image ... with the "References" tab selected (its actual code is irrelevant to the question). The inset was a trigger with the word "Type" highlighted ... because I was looking for _DIRECT_ references to SP_Type, not indirect references. In the case of the highlighted trigger ... neither it nor its view (so far as I can tell at this writing) have any direct / indirect reference to SP_Type (and I know from both the data and the coding, that this should be true) ... so ... if the DB dependency views are reporting this / calculating this ... something is off.

                     

                    So .. I have detective work ahead ... as Sherlock would say, the game is afoot.

                     

                    Kind regards,

                     

                    Suzanne

                    • 7. Re: Real Q: In Function review in SQL_Dev, References are way inaccurate.
                      SM_Work

                      One more bit RP0428 ...

                       

                      You said ...

                      <snip>

                      I did find at least one specific inaccuracy: there is a named section of a different function (function B) with the same section-name as the original function A ... (but no relation to function A) ... a view using function B shows up in Function A's "references" list ... which is wrong.

                       

                      I have no idea what any of that even means.

                      <snip>

                       

                      What I meant was ... I had a case statement in another function (for brevity's sake "Function B").

                       

                      <<SP_Type>>

                      CASE

                      When ... etc.

                      END Case SP_Type;

                       

                      ... this code section had NOTHING to do with the function "SP_Type" except to share a name.

                       

                      I do not know if _that_ accidental reference (written before the actual SP_Type function existed) has anything to do with clouding the database's understanding of what is related to what.

                       

                      That's all.

                       

                      Kind regards,

                       

                      Suzanne