Full disclosure: This is a rant post. If you’re not into ranting and pontificating, perhaps best to move along now

So it’s just another typical day on the interwebs, because there’s always a post, or a comment, or a tweet, where someone chips in with:

“You know, Oracle 7 was the best version of the database”

Really? The best ??

Now, first let me clearly state: I loved version 7 of the database. And I know that many other people loved it as well. But whilst I can’t speak for everyone, I know the reason I loved version 7 was because it was not version 6! For anyone that had worked with version 6 (which was fine for its time), version 7 was a revelation to both administrators and developers with innovations like the shared pool, PLSQL, maxextents unlimited, and so on.

Because version 7 was such a jump over version 6, I think a lot of people over the years have been swept up by a false euphoria over its capabilities. As the years go by, and memories of using it become more folklore than fact, we forget what it was like to use it day to day, and just as easily forget how many things we take for granted today, that in fact version 7 did not have.

Yes, it was a very cool release, but let us take just a few examples to see how far we’ve come, and see whether you would really like to head back in time and be on version 7.

Want to reorg that table? No problem – just make sure you email everyone and tell them the system is going to be down for the day! No online operations for you in version 7!

No matter, we can just fall back to using good old DROP and CREATE to reload that data. So first I’ll just grab the DDL for that table:

Yeah, that ain’t happening. To get that DDL, you better fire up some 3rd party tool that you probably paid a stack of money for (because your free SQL Developer wasn’t around at the time).

And of course, running a DROP command always comes with some risk. What if you inadvertently dropped the wrong table? That’s an easy fix right?

Sorry. That table is gone…gone forever. No flashback for you in version 7. Most of us had to adopt of a policy of “rename it, check it, drop it later”.

And how about security? After all, one of the reasons we ask people to stay current with their database releases is so that they are more secure. Well, security was simply a pain in version 7, because even the DBAs couldn’t give out privileges!

Many of us have probably forgotten that we had to hack together a PLSQL procedure into the owning schema in order to run grants as a DBA.

Let’s look at performance tuning. I’d like a nice formatted execution plan please:

Nope, you don’t get that in version 7, so you’ll need to write your own SQL against the PLAN_TABLE, which by the way, did not exist by default – you needed to know how to find and run the UTLXPLAN.SQL script, plus have a cleanup routine, because global temporary tables did not exist either!

Of course, even if DBMS_XPLAN had existed, you still would not be querying it as per the example above, because TABLE functions did not exist. Tables functions could not exist because you could not even create the object types to handle them!

Since we’re on the topic of trying to create things, if you are a developer on any of the modern releases of the Oracle database, then you are in for a few disappointments when we downgrade all of our databases back to version 7 because “it was so much better”

Planning on using XML? Well, that is unfortunate.

Maybe you would just like to store some accurate time data? Too bad, you will have to make do with just storing times down to the second.

Or perhaps just a simple table with a simple surrogate key is all that you were after?

Sorry, it has to be a sequence and a trigger and a context switch and a select from dual and a performance drag for you.

Even with all those features you did not get in version 7, but that you do get in the latest releases of the database, at least SQL is SQL right? I mean after all, as a developer, maybe all I need to do is just write some nice simple SQL against my version 7 database and build applications. What could possibly go wrong?

Oh? Thinking of ANSI joins? Sorry, not in version 7.

Maybe you’d like to get the first 5 employees from the EMP table?

That’s right – no ORDER BY in an inline view until version 8i, so you better brush up on your classical GROUP BY, HAVING, subquery syntax because for any requirements like “top-n”, you will needing to go back to your first principles on SQL set theory.

And of course, don’t even think about heading down the analytic SQL path to see if that is going to help, because they are still years away.

But…Maybe you’re a fan of going back to the first principles SQL? I mean, let’s face it – why would I want write code like this to find the median…

SQL> select
  2      deptno,
  3      median(sal)
  4  from emp
  5  group by deptno;

    DEPTNO MEDIAN(SAL)
---------- -----------
        10        3725
        20        2975
        30        1375
        40        1300

…when I can just as easily do it like this :-)

SQL> select deptno, avg(distinct sal) median
  2  from
  3    (select cp1.deptno, cp1.sal
  4     from emp cp1, emp cp2
  5     where cp1.deptno = cp2.deptno
  6     group by cp1.deptno, cp1.sal
  7     having sum(decode(cp1.sal, cp2.sal, 1, 0)) >=
  8                 abs(sum(sign(cp1.sal - cp2.sal))))
  9  group by deptno
 10  /

    DEPTNO     MEDIAN
---------- ----------
        10       3725
        20       2975
        30       1375
        40       1300

Even with this you might be thinking …”No problem”. After all, one of the best ways of structuring a complicated SQL is using a Common Table Expression, also known as a WITH clause.

It’s a shame that you don’t get that either in version 7. In fact, SQL Plus won’t even let you get past the first line!

You can now perhaps realise why PLSQL was such a godsend for us developers back in version 7, because the moment our data requirements got complicated, and we could not solve these tasks with the limitations of the version 7 SQL, we could at least resort to doing the processing ourselves in PLSQL.

Of course, not that we could solve business problems efficiently, because you didn’t get any native array processing for PLSQL back in version 7.

and good luck trying to debug any problems with your code using DBMS_OUTPUT.

Or at least, make sure you debug it in 255byte size chunks!

Don’t get me wrong. I still maintain that Oracle 7 was a landmark release for Oracle DBAs and Developers alike. In the days where the mainframe was still king of the hill, it opened up so many opportunities for large scale systems on Unix platforms to become serious competitors to the mainframe world in the enterprise. In fact, the company I worked for in the mid 90s moved their entire mining asset management implementation from an IBM mainframe to a Sun system running Oracle 7, and saved millions in the first year alone.

Oracle 7 was a magnificent release, and you are right to raise a glass and toast its abilities for its time.. But it is not even close to being included in the same conversation as the modern Oracle database versions, and it will be distanced even more so with 23c and every new version that follows that.

If you have read this far, I know you’re an Oracle 7 fan…but it’s time to let go

(Thanks to Toon for the pics of the docs)