SQL history lesson with Oracle V2

I recently stubmbled upon this website that hosts a publicly available Oracle RDBMS instance running Oracle v2.3.2, which according to this Wikipedia article is the first commercially available version of Oracle. This version is not written in C, but in PDP-11 assembly. The website also has the manuals available. At this time the company was called Relational Software Incorporated or RSI for short, which they later renamed to Oracle Systems Corporation and then to Oracle Corporation. Before this the company was called Software Development Laboratories (SDL).

Let’s have a quick look at this and see how it compares with newer versions.

This version uses “UFI”, the predecessor of SQL*Plus.

Let’s first create a table

SQL>CREATE TABLE T1
SQL>ID(NUMBER NONULL UNIQUE IMAGE),
SQL>NAME(CHAR(20) NONULL)
SQL>/
Table created.

And let’s do the same with Oracle 26ai

SQL> CREATE TABLE T1 (
  2  ID NUMBER PRIMARY KEY,
  3  NAME CHAR(20) NOT NULL
  4  ) 
  5  /

Table created.

The prompt has changed slightly, but it looks mostly the same. Note the missing (...) around the list of columns and the datatype within (...). Some other small changes: NONULL instead of NOT NULL and IMAGE to indicate an indexed column.

Now let’s use the system catalog to inspect the table.

SQL>SELECT * FROM tab WHERE TABLE='T1'
SQL>/

 NAME                          TYPE  CREATOR          GRANTEE
------------------------------ ----- ---------------- ----------------
 T1                            TABLE SCOTT            SCOTT

SQL>SELECT * FROM col WHERE TABLE='T1'
SQL>/

 TABLE                         COLUMN                              COLID
------------------------------ ------------------------------ ----------
 T1                            ID                                      1
 T1                            NAME                                    2
SQL>

And the same on 26ai

SQL> SELECT TNAME,CNAME,COLNO FROM COL WHERE TNAME='T1'
  2  /

TNAME
--------------------------------------------------------------------------------
CNAME
--------------------------------------------------------------------------------
     COLNO
----------
T1
ID
     1

T1
NAME
     2

TNAME
--------------------------------------------------------------------------------
CNAME
--------------------------------------------------------------------------------
     COLNO
----------


SQL> COLUMN TNAME FORMAT A10
SQL> COLUMN CNAME FORMAT A10
SQL> /

TNAME      CNAME       COLNO
---------- ---------- ----------
T1         ID                  1
T1         NAME                2

SQL> 

I didn’t query tab, but that also still works. The available columns have changed a bit, making the default output less readable.

And some DML

SQL>INSERT INTO T1:
SQL><1,'Test'>
SQL>/
1 record created.

SQL>SELECT * FROM T1
SQL>/

        ID NAME
---------- --------------------
Test

SQL>DELETE T1 WHERE ID=1
SQL>/
1 record deleted.
SQL>

And the same on 26ai:

SQL> INSERT INTO T1  
  2  VALUES(1,'Test')
  3  /

1 row created.

SQL> SELECT * FROM T1
  2  /

    ID NAME
---------- --------------------
     1 Test

SQL> DELETE FROM T1 WHERE ID=1
  2  /

1 row deleted.

Note the : and the VALUES(...) syntax for the insert instead of the <...> and DELETE instead of DELETE FROM.

And some cleanup

SQL>DROP TABLE T1
SQL>/
Table dropped.
SQL>#EXIT
Logged off from ORACLE.

And the same on 26ai:

SQL> DROP TABLE T1
  2  /

Table dropped.

SQL> #EXIT
Disconnected from Oracle AI Database 26ai Free Release 23.26.1.0.0 - Develop, Learn, and Run for Free
Version 23.26.1.0.0

In the manual you can also see that ALTER TABLE was still called EXPAND TABLE. Other things that the manual mentions is: joins, views, CONNECT BY for hierachical data and the IMP and EXP utilities for import and export.