I just experienced something I've not seen before and wondering if there are behavior changes with 19c (or previous?) that I'm not fully aware of. Yesterday 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. 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? As I said, I thought a compile on a package was essentially committing it to the source code, whether it compiles successfully or not. Did something change with some recent versions of Oracle that rolls back package changes, even though they've been compiled into the database?
Dazed and confused,
Earl