Saturday, April 12, 2014

Personal Oracle 7.2.2

Introduction

The first version of Oracle that I ever used was version 7.3.4 for SCO OpenServer 5.0.0. I think. Something like that. It had an odd installer that required the screen resolution to be set to 256 colors. Only 256 colors. No more, no less. It also assumed you were using NIS and getting the installation to run without it required copying the entire CD onto the hard drive, tweaking the install script, and then running it from there, rather than from the CD. This was back in the late 90's, when a 1.6G hard drive was huge and you could easily run out of space during the installation because of having had to copy the CD onto the local drive.

Good times.

I've long wanted to relive those days, so I've had an eBay search going for Oracle for a while now. It usually turns up modernish versions, but a month or two ago a seller popped up with a still-shrink-wrapped copy of Personal Oracle 7.2.2.

windows 95 - 3. Personal Oracle 7.2.2

Ha! I'd forgotten about the existence of Personal Oracle.

Back before they gave the DB away for development and non-commercial use, Oracle sold Personal Oracle 7. It was inexpensive, ran on Windows 95, and only allowed a single connection. I guess it was a useful development tool for a lot of people, but back then we ran Unix on the servers and everybody had a Mac on their desk. If we had 3 Windows installations in the whole office, it would have been a lot, so we didn't pay it much attention.

Still though, seeing it on eBay widened my eyes. It was Oracle7, and you rarely run into any kind of Oracle7. I tend to avoid still-shrink-wrapped stuff because some die hard collector might appreciate it more than I do, but...

Sold!

Finding a Compatible OS

The first hurdle was finding something it would run on. The box said Windows 95 and didn't mention anything else.

I'd recently gotten Windows 3.1 off of eBay, a copy of NT 4.0 came with Visual Studio 5 that I bought ages ago, I inherited a copy of Win2k when a company I used to work for went out of business, and there's a Windows 98 upgrade CD in my folk's basement, but I was all out of Windows 95.

Windows 3.1 is 16 bit. No chance there.

I tried it with my NT and Win2k VM's, but no luck. It doggedly refused to install. I didn't even try Windows 98 because I'd have needed a copy of Windows 95 just to install it.

Windows 95 is a little sketchy. There are probably a hundred listings for it on eBay but about 95% of those are just the disk that came with some old computer and marked "for distribution with a new pc" (or something like that) and I'm not 100% sure of the legalities involved in buying one of those. It seemed like the remaining 5% were all upgrades too, so I waited. And waited. And waited... And eventually did some research. Turns out Microsoft only sold upgrades. Ha! No use waiting any longer.

Sold!

Getting it to install in VMware was a little tricky. The VM configuration was straightforward but the CD predates bootable CD's and it didn't come with a boot floppy. I couldn't just start the install and insert a Win 3.1 disk to prove I owned one. I had to run the install CD from an already-fully installed version of Windows 3.1.

More good times. But I got it going.

windows 95 - 1. installed

Sort of. Windows 95 predates VESA. I ended up with a 16 color, 640x480 desktop and all of my attempts to install VMware-tools or other third-party SVGA drivers failed.

The CPU was slammed at 100% too. Apparently Windows 95 didn't idle the CPU. There are free utilities for DOS and Win 3.1 to take care of that, but the only one available for Windows 95 cost money and did a bunch of other stuff related to overclocking.

Oh well. Maybe I'll buy it later.

Windows 95's default network configuration is odd by today's standards too. I removed IPX/SPX and NetBEUI and installed TCP/IP. I gave it a static IP and played around with telnet, ftp and a couple of browsers. Netscape 6.0 installed and ran just fine. So did IE 5.5. Aside from the 16 colors, they were both even semi-usable.

windows 95 - 2. ie5.5

I'd have preferred better resolution, and for some reason the daylight-savings-time handler kept resetting the date to sometime in 1988 every time I'd reboot, but things worked well enough to do what I really wanted to do.

Oracle Installation

Installing Oracle on Linux or Unix is a ten-thousand step process. Well, not literally, but it can be a lot of work. Installing on Windows is a breeze.

You just click stuff, answer some intuitive questions, and voila...

windows 95 - 4. oracle install

Nothing too it.

Poking Around

I remember various versions of Oracle 8 up and filling my start menu with tons of software. Personal Oracle 7 is much more spartan.

On the server side, you get:

  • Start Database
  • Stop Database
  • Backup Manager
  • Recovery Manager
  • Personal Oracle7 Navigator

...plus a few docs.

On the client side, you get:

  • SQL Plus
  • SQL Net Easy Configuration

...plus a few docs.

This is all from way before Oracle got interested in Java, so the Navigator is a native Win32 app.

windows 95 - 5. oracle navigator

It's fairly rudimentary too. You can create, list and destroy database objects, but you can't run queries.

Running queries requires SQL Plus.

I'd used the "local connection" object in the Navigator to access the DB, but SQL Plus requires a username, password and SID and I wasn't sure what SID corresponded to "local connection". The SQL Net configurator revealed a tcp-loopback SID though, so I used that, plus the standard scott/tiger credentials, and before long I was looking at the familiar example database.

windows 95 - 6. sqlplus

Ha ha!

system/manager worked too and the system tables were familiar.

Remote Access

Ok, so I could access the DB via the "local connection" and via the tcp-loopback SID. I presumed that the local connection was happening through some kind of IPC and that the tcp-loopback connection was happening through a TCP listener. Was the TCP listener accessible from the outside?

Well, I could telnet to port 1521 from another machine, so maybe it was.

It's a-whole-nother story or two, but I have various old versions of Oracle running on various old versions of Redhat linux, from before the Fedora project. I added this SID...

orcl =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL= TCP)(Host= windows95.firstworks.com)(Port= 1521))
    (CONNECT_DATA = (SID = orcl))
  )

...to tnsnames.ora on a couple of them, and gave sqlplus a try.

No problem from Oracle 8.0.5 on Redhat 5.2:

[dmuse@redhat52 admin]$ sqlplus system/manager@orcl

SQL*Plus: Release 8.0.5.0.0 - Production on Fri Apr 11 23:52:32 2014

(c) Copyright 1998 Oracle Corporation.  All rights reserved.


Connected to:
Personal Oracle7 Release 7.2.2.3.1 - Production
With the distributed and replication options
PL/SQL Release 2.2.2.3.1 - Production

SQL>

And it worked equally well from Oracle 8.1.7 on Redhat 6.2.

But Oracle 9 on Redhat 9 wasn't as happy.

[dmuse@redhat9 dmuse]$ sqlplus scott/tiger@orcl                                 
SQL*Plus: Release 9.2.0.4.0 - Production on Fri Apr 11 23:56:36 2014

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

ERROR:
ORA-24439: Connections to Oracle7 server are no longer supported

And, true to the licensing scheme described in the docs that came with the media, only one concurrent connection is supported. Second attempts fail with:

ERROR:
ORA-12500: TNS:listener failed to start a dedicated server process

Limitations notwithstanding, I was able to create a tablespace and schema for a test user:

CREATE TABLESPACE testtablespace
DATAFILE 'C:\oracle\testtablespace01.dbf'
SIZE 1M REUSE
AUTOEXTEND ON NEXT 1M
MAXSIZE UNLIMITED;

CREATE TABLESPACE testtablespacetemp
DATAFILE 'C:\oracle\testtablespacetemp01.dbf' 
SIZE 2M REUSE
AUTOEXTEND ON NEXT 1M
MAXSIZE UNLIMITED;

CREATE USER testuser IDENTIFIED BY testpassword
DEFAULT TABLESPACE testtablespace 
TEMPORARY TABLESPACE testtablespacetemp;

GRANT CREATE SESSION TO testuser;

GRANT RESOURCE TO testuser;

The only trouble that I had was that I usually use the NOLOGGING option with CREATE TABLESPACE, and it wasn't supported.

It seems like Oracle7 did support that option though, so maybe that's specific to Personal Oracle 7. No idea.

SQL Relay

Hooking up SQL Relay to the DB was easy. I configured it to only start 1 connection, used the testuser/testpassword credentials to log into Oracle, and used the orcl SID that I'd defined earlier.

I could only do this on my Redhat 6.2/Oracle 8.1.7 and Redhat 5.2/Oracle 8.0.5 VM's though, as newer versions of Oracle refused to connect to the DB.

Still, I was able to access the DB from Fedora 20 x64 through the relays running on those old VM's.

fedora 20 x64 - Oracle 7.2.2

And with a few tweaks, my standard Oracle test script for SQL Relay ran.

IDENTIFY:
success
PING:
success
CREATE TEMPTABLE:
success
INSERT:
success success
AFFECTED ROWS:
success
BIND BY POSITION:
success success success
ARRAY OF BINDS BY POSITION:
success
BIND BY NAME:
success success
ARRAY OF BINDS BY NAME:
success
BIND BY NAME WITH VALIDATION:
success
OUTPUT BIND BY NAME:
success success success success success success success success success success success success
OUTPUT BIND BY POSITION:
success success success success success success success success success success success success
OUTPUT BIND BY NAME WITH VALIDATION:
success success success success success success success success success success success success
SELECT:
success
...

The few tweaks I did make had to do with the limitations of Oracle7. Specifically:

  • The default date format is DD-MON-YY with a 2-digit year.
  • VARCHAR2's have a maximum size of 2000 bytes.
  • Multiple connections aren't supported.
  • Output bind cursors either don't work, or work very differently. The PL/SQL that I used for Oracle 8+ was rejected.
Conclusions

SQL Relay appears to work with Oracle7. Personal Oracle 7 has some limitations, but none that make it painful to play with. Windows 95 is a little quirky, but I suspect that if I look around I'll find solutions.

All-in-all, a fun experiment. Well worth the effort involved.

No comments:

Post a Comment