a picture of the original hard copy oracle manuals from version 4 to version 8

Version 7 was so much better…

Posted by

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

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!

image

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:

image

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?

image

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!

image

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:

image

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!

image

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” Smile

Planning on using XML? Well, that is unfortunate.

image

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.

image

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

image

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. Smile

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? Smile

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

image

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

image

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.

image

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: Smile


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!

image

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.

image

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

image

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

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 Smile

(Thanks to Toon for the pics of the docs)

10 comments

  1. A good read, Connor.
    There are countless faithful who’ll testify their fav NF for each major release, me included.
    Having started my database journey with v4.0, I was fortunate to have worked on a very large lease-finance system in the uk that started Dev on v5, went live on v6, then I was tasked to upgrade it v7.1 to resolve the y2k – back in 1995-6 ! My company wrote 7 year leases and the system was fudged until v7 gave us the RR parameter to resolve.

    I must admit, ALL the major NF introduced since the venerable v7 have been groundbreaking, both from a developer and DBA perspective.

  2. 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).’

    ——-

    No, no, no, no, no. We just looked for the script that Tom Kyte had already written for us!
    I still have it.

    1. Didn’t all the scripts on the SAMPLE CD originate from Tom K ?
      Oracle-Base also carried great insights such that I’d be merrily recommending it to my Oracle AppDev students.

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.