1 2 3 Previous Next 31 Replies Latest reply: May 14, 2014 5:05 PM by Jeff Smith Sqldev Pm-Oracle RSS

    Modified by an external source

    matthew_morris

      I have an error/notification message pop up in SQL*Developer periodically when I am working on PL/SQL blocks.  It indicates that the package has been modified by an external source and asks if I want to reload. I assume that it has something to do with my connection or the way I am VPN'd into the database.  Whatever the reason, if I am distracted enough to click OK (like a few seconds ago), the PL/SQL package I'm working on gets reloaded from the data dictionary and I lose all the work I did since the last compile.

       

      Pretty please tell me there is some way to shut this lovely feature off.

        • 1. Re: Modified by an external source
          rp0428
          I have an error/notification message pop up in SQL*Developer periodically when I am working on PL/SQL blocks.  It indicates that the package has been modified by an external source and asks if I want to reload. I assume that it has something to do with my connection or the way I am VPN'd into the database.  Whatever the reason, if I am distracted enough to click OK (like a few seconds ago), the PL/SQL package I'm working on gets reloaded from the data dictionary and I lose all the work I did since the last compile.

           

          Pretty please tell me there is some way to shut this lovely feature off.

          It's not clear what you think the 'solution' should be. Here are some basic facts related to what you ask:

           

          1. There can be only ONE version of any stored PL/SQL package/procedure/function.

          2. That one version can be used by multiple database users. In fact another user could be 'changing' that same package.

          3. That one version may have dependencies on other database objects

          4. If Oracle detects an invalid code object during execution of a transaction it will AUTOMATICALLY try to recompile it to make it valid. For example if a package references a table/view and someone, anyone, modifies that table/view it will invalidate the package.

          5. If Oracle recompiles an object it will use the source for that object that is ALREADY in the data dictionary.

          6. Oracle does not know about, and does not care about, any work you do 'since the last compile'.

           

          That feature that you want turned off is trying to warn you that you may be about to risk the integrity of that package code.

           

          Conclusion: don't try to modify database objects that are in use by Oracle or others.

           

          You need to make and test your changes in a sandbox environment where those other factors will not occur. Give your modified package a different name while you are developing/testing it. You can also create a synonym for it so that it appears to have the original name.

           

          Once you are ready to update the original code you should be doing that update using code from a version control system.

          • 2. Re: Modified by an external source
            matthew_morris

            I am the only user with the rights to modify the database.  No other users in the company even have an Oracle account.  It's just me and people that access the database through Apex applications (APEX_PUBLIC_USER).

             

            The function I'm working on is in development -- first created this morning -- and no one but me has any access to it via any interface (including Apex)

             

            This message has popped up five times so far today while I'm coding on a standalone PL/SQL function that no other session in the database could possibly be accessing in any fashion. It will not be part of any transaction and is not a dependent object of anything else in the database (yet).

            • 3. Re: Modified by an external source
              matthew_morris

              Make that six times.  I happened again right after my last reply to this thread.  Figured I'd add the specific dialog message:

               

              "Function xxxxx has been modified outside JDeveloper. Do you want to reload?"

               

              There is no way that anyone has modified the function.  I do not know what is triggering SQL*Dev to think that someone has.

              • 4. Re: Modified by an external source
                rp0428
                Make that six times.  I happened again right after my last reply to this thread.  Figured I'd add the specific dialog message:

                 

                "Function xxxxx has been modified outside JDeveloper. Do you want to reload?"

                 

                There is no way that anyone has modified the function.  I do not know what is triggering SQL*Dev to think that someone has.

                That is curious - you are using SQL Developer but the dialog message has JDeveloper in it. That part might just be a carryover from sql developer using the JDeveloper framework libraries.

                 

                If this really is an isolated piece of code that can't have any dependencies then you will need to wait for someone from the sql developer team to weigh in on whether this issue has been reported before.

                • 5. Re: Modified by an external source
                  matthew_morris

                  That is curious - you are using SQL Developer but the dialog message has JDeveloper in it. That part might just be a carryover from sql developer using the JDeveloper framework libraries.


                  Dunno.  However, after I posted the exact error message here, I did another Google search using the specific text and found two other threads in this forum (unanswered) that remark on the JDeveloper discrepancy:

                   

                  https://community.oracle.com/message/11295245?tstart=0

                  https://community.oracle.com/thread/2560996?start=0


                  If this really is an isolated piece of code that can't have any dependencies...

                   

                  Really truly honestly unless I have an evil twin somewhere that has used our psychic connection (that apparently only goes one way) to crack into my system and mess with me.

                  • 6. Re: Modified by an external source
                    Arpod

                    Just a note that I occasionally encounter this problem (and this exact error message), even when working in a test environment where no other users should interfere. I wasn't able to notice the sequence of actions leading to it, though.

                    • 7. Re: Modified by an external source
                      PaoloM

                      Hello Matthew,

                      you are not alone, I am getting the same issue as well and I find it really annoying.

                      I will try to find out how to replicate consistently the issue and I will report back in the hope that it will get fixed.

                       

                      Thanks,

                      Paolo

                      • 8. Re: Modified by an external source
                        rp0428

                        Ok - but you didn't specifically address part of what I said above.

                        This message has popped up five times so far today while I'm coding on a standalone PL/SQL function that no other session in the database could possibly be accessing in any fashion.

                        Other sessions do NOT have to be accessing your function.

                        It will not be part of any transaction and is not a dependent object of anything else in the database (yet).

                        Huh? All PL/SQL code will be part of a transaction.

                         

                        Anyway, the issue is whether that function is dependent on OTHER objects in the database. If your function references a table and another user modifies that table it will invalidate your function. Same for views or other functions/procedures/packages that your function references.

                        • 9. Re: Modified by an external source
                          Jeff Smith Sqldev Pm-Oracle

                          We're checking the time in the db to see when the last time it was modified and comparing it to your local time. Can you confirm your db and local times are correct?

                          • 10. Re: Modified by an external source
                            Jeff Smith Sqldev Pm-Oracle

                            Also, if you're working from files, don't forget the History panel in the editor which keeps a running history of versions of your source. That can help you get stuff back you've inadvertently clobbered.

                            • 11. Re: Modified by an external source
                              matthew_morris

                              rp0428 wrote:

                               

                              Ok - but you didn't specifically address part of what I said above.

                              This message has popped up five times so far today while I'm coding on a standalone PL/SQL function that no other session in the database could possibly be accessing in any fashion.

                              Other sessions do NOT have to be accessing your function.

                              It will not be part of any transaction and is not a dependent object of anything else in the database (yet).

                              Huh? All PL/SQL code will be part of a transaction.

                               

                              Your reply was indicating that Oracle might be trying to recompile the function I was working on while I was working on it because it was part of a transaction that occurred while I was developing.  My reply was to indicate that nothing was going to call that particular function except me because no SQL or other PL/SQL code was tied to it yet. Likewise, I am the only person in the database with the ability to change any database objects, so even if it had dependencies on other objects (it doesn't), they would not be getting changed by anyone but me.

                              • 12. Re: Modified by an external source
                                matthew_morris

                                I'm in EST if that's what you're looking for. 

                                 

                                My SQL Developer version is 4.0.0.13 Build MAIN-13-80 and it is accessing an Oracle Database 11g Release 11.2.0.3.0 - 64bit Production database  (Standard Edition One, if that matters).

                                • 13. Re: Modified by an external source
                                  matthew_morris

                                  ...and for that matter, even if Oracle decided to recompile the function, that would affect the code in the data dictionary.  Why would Oracle decide to come out and try to change code pulled out to SQL Developer?  As you noted in your first response, "Oracle does not know about, and does not care about, any work you do 'since the last compile'."

                                   

                                  This can't have anything to do with a recompile at the database level.  It's SQL Developer that is performing this action.

                                  • 14. Re: Modified by an external source
                                    Jeff Smith Sqldev Pm-Oracle

                                    Ok. Is the time on your local machine reporting the same time as your db server?

                                    1 2 3 Previous Next