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,
Earl