4 Replies Latest reply on Jun 5, 2020 4:35 PM by Mike Kutz

    Compile package with SQL Developer

    Earl Lewis

      I asked this question in the SQL and PL/SQL group, but thought it might be applicable here. Yesterday I experienced something I've not seen before and wondering if there are behavior changes with Oracle 19c (or previous?) or SQL Developer that I'm not fully aware of. I was doing some package editing (added a function to the spec and body) and fleshed out most of the function body. As I was going along I compiled many times, sometimes with errors and other times without. Last I remember I did a successful compile before shutting down for the day. In the past (admittedly several month back) it seemed to me that a compile, whether successful or not, was equivalent to a commit on a SQL operation. However, today when I went back in to continue editing the package my changes were gone. No function spec, no function body. Nada.


      Luckily, I've got a good relationship with our DBA and he quickly jumped into action and figured out how to do a flashback query on sys.source$ and recovered a copy of the changes (thanks Rob!).


      At first, I thought I was losing my mind, thinking that I either didn't do what I thought I did or made a huge mistake somewhere along the line and didn't make the changes where I meant to, although I'm not usually that level of spacey. But of course, the flashback query revealed the truth - and my mind is mostly intact.


      So the question: what on earth did I do wrong here that I could have spent at least an hour writing code, compiling, test, etc and then the code is gone the next day? As I said, I thought a compile on a package was essentially committing it to the PL/SQL source code, whether it compiles successfully or not. Did something change with some recent versions of the database or SQL Developer that code changes have to be committed somehow, even though they've been compiled into the database? If so I clearly missed the memo, but I'm truly scratching my head on this one.


      Dazed and confused,


        • 2. Re: Compile package with SQL Developer
          Glen Conway

          You are correct about the source code changes getting committed.  Looking at your question in the other group (Package changes "lost" ), I can only say that


          1) The ALTER PACKAGE syntax used to attempt compilation of an existing stored procedure (whether valid / invalid syntactically / or just invalidated) is DDL.

          2) SQL Developer uses the CREATE OR REPLACE PACKAGE syntax to update the data dictionary and attempt compilation of a new or changed stored procedure.  That is also DDL.

          3) Therefore the implied COMMIT must be happening, always, as suggested in the other discussion.


          If you look at SQL Developer's View > Log > Statements log after doing a Compile, you can verify this (note the sequence is shown in descending order)...


          So, barring true insanity or some major restore action against the database, I would suspect another developer modified that same package and stepped on your changes. That is why you should always employ version control software (VCS) to manage changes. If it is the case that someone else worked on the package in parallel with you, it might be possible that their work got lost when the package got restored to your version!



          1 person found this helpful
          • 3. Re: Compile package with SQL Developer
            Earl Lewis

            Thanks for your thoughts and reply Glen. Lesson learned about source control. This is a development instance with no one else working on this app, so the chances of someone else compiling a different version of the code is extremely low, although not zero. I suspect that another copy of the original package file was open in SQL Developer and it compiled over my changes. Clearly sloppy and unacceptable, but we're all human and we learn by our mistakes. Thanks again.

            • 4. Re: Compile package with SQL Developer
              Mike Kutz

              I recommend you use a Code Repository even for your personal apps.  If only for practice and habit-training.


              The last thing you should do for the day is not "compile the code" but "check in" the code (localhost repo) but not perform a "pull request".  (some exceptions might apply.)


              If you need one, SQL*Developer includes SVN for version control.


              Oh, using the extension pks/pkb will open the file in Package Editor window (instead of a worksheet).

              Once SQL*Developer links a database package to a file, it can auto-save the file with the freshly compiled text (ie click the "compile" button).

              Using the File view, you can easily see if the file has been commited to the Code Repository.



              1 person found this helpful