Why Oracle Sucks

Oracle talks a lot about 11g Express Edition, and how it differs from the full (paid) versions of their database, but it does not say a word about the most important thing:

Is it compatible at the SQL syntax level?

No way to find out other than to try it.  So, let's try it.

(Useful pre-reading: About these papers)

The downloaded installation zip file contains a folder called DISK1.  Now, Oracle Database Express Edition 11g Release 2 bears the date June 4, 2014, so 14 years into the 3rd millennium Oracle is still masturbating with the concept of "disks".

The downloaded installation zip file is 316MB, it contains a setup.exe which is 317MB, and when you run it, it extracts a contained msi file, which in turn extracts its contents.

The installer asks me to specify a database password.  It says that this password will be used for the SYS and SYSTEM accounts.  If both accounts are to have the same password, then clearly, one of them must be redundant, no?

Oracle 11g Express installs an icon on my desktop which is literally hideous.

I double-click the icon, and an error message dialog pops up.  The title of the dialog is:

http://127.0.0.1:%HTTPPORT%/apex/f?p=4950

and the message in the dialog is:

Windows cannot find 'http://127.0.0.1:%HTTPPORT%/apex/f?p=4950'. Make sure you typed the name correctly, and then try again.

I open up the start menu, find a "Start Database" icon, double click it, and an elevated command prompt shows up.  The command prompt appears to do nothing at all, it just sits there waiting for me to type something. I haven't the slightest clue as to what I am supposed to type in there.  After some troubleshooting it turns out that the command prompt executed a command which (might have) silently started the database.  But they used the /k option, so the command prompt stayed open, so it appears as if nothing was done.

And then, that's it.  Some database must have started somewhere, but you are given no means of working with it.  There is a "Get Started" icon in the start menu, but clicking it gives the same stupendous message as the one on the desktop:

Windows cannot find 'http://127.0.0.1:%HTTPPORT%/apex/f?p=4950'. Make sure you typed the name correctly, and then try again.

So, you have to guess that you must download and run Oracle SQLDeveloper.

When Oracle SQLDeveloper starts, it begins with a "Confirm Import Preferences" box which asks me if I want to import preferences from a previous SQL Developer installation.  The dialog has a list of "previous installations", but it is empty.  You have to use your magic powers to guess that you need to click on a tiny magnifying glass icon on the side, to search for a previous installation.  It opens a file selection dialog which starts pointing to "roaming/SQLDeveloper", and then you have to guess that you need to doubleclick one of its subdirectories.

The process of importing preferences from the previous version takes forever, (entire minutes on a 4GHz i7-4790 with SATA3 SSD,) even though I hardly used that previous version: I just launched it once and closed it.

My firewall shows that as soon as Oracle SQLDeveloper launches, it calls home, even as it is prompting me whether I would like to participate in their data collection scheme.  ("Allow automated usage reporting to Oracle.")

When trying to create a connection, I am presented with a dialog with far more options than should be necessary, and I have to guess what to enter in various fields.  Trying with "SYS" for username yields an error message saying "ORA-28009: connection as SYS should be as SYSDBA or SYSOPER", but then trying with "SYSDBA" and with "SYSOPER" gives "ORA-01017: invalid username/password; logon denied".  So, again, I have to guess that "SYSTEM" must be the magic word.

Also, it is not enough to check "Save password", I have to also remember to click "Save" to save the saved password.

Upon opening a brand spanking new (empty) database, it contains dozens of system tables.  A filter has already been applied, but it only excludes tables in some "recycle bin".  It is possible to extend the filter to exclude most system tables, but it takes quite a bit of work, because they do not all obey a consistent naming pattern.

There is a "manage database" option which opens up a purely informational page, nothing to manage there.

So, it turns out that Oracle 11g Express Edition installed with a "sid" of "xe", which is different from the one that I needed.  I do not know what this "sid" thing is, and I do not care, and frankly, it should not exist, but it does. So, I have to change the sid of my local instance. Luckily, there is an article on stackoverflow on how to do it: http://stackoverflow.com/a/3424544/773113  I decide that this is insanely complicated and opt to uninstall my local instance and re-install it from scratch, only to discover that nowhere during installation does oracle ask me for the sid that the local instance will have.

The first time I invoke databaseMetaData. getImportedKeys() it takes an exorbitant amount of time for the server to respond, (14 seconds on an i7-4790K with SATA3 SSD,) even on a freshly created, empty database.

In Oracle SQL Developer, If you select a single table, the context menu allows you to drop it.  But if you select multiple tables, a different context menu is shown, which does not contain an option to drop them.  So, you have to do it table by table.

In Oracle SQL Developer, there is a "Quick DDL" entry in the context menu.  If you select multiple tables and activate this menu entry, you find out that it is anything but quick: it takes 4 minutes for 20 small tables.

The dialog shown for lengthy operations has a "run in background" option.  If you select this option, the dialog disappears and not a single hint of it remains anywhere to be seen.  So, you don't know if it is running, and you have no way of checking its progress.

Supposedly, "run in background" means "take this application-modal dialog out of my face so that I can continue using the application".  But if you send a lengthy operation to the background, and then you try to do anything with the application, you get a message saying that "the connection is currently busy".

The "connection is currently busy" dialog has two buttons: one says "Retry" and the other says "Abort".  This makes you wonder what is going to happen if you click "Abort": is it going to terminate the application?  Is it going to terminate the background process?  No, it turns out that it just cancels the dialog. How callous must an engineer be call the button "Abort" when they just mean "Cancel"?

In Oracle SQL Developer, whenever you issue a command which fails, you get extensive but completely lame error messages.  For example, the following:

Error starting at line : 3 in command -
ALTER TABLE "APV_OWNER"."CONNECTIBLES" DROP FOREIGN KEY "SYS_C0010516"
Error report -
SQL Error: ORA-00905: missing keyword
00905. 00000 -  "missing keyword"
*Cause:   
*Action:

There is a problem with every single line of the above error report:

1. "Error starting at line : 3 in command -": When it says "line 3" it does not mean line 3 of the failed command, it means that the failed command was taken from line 3 of the original script.  But I chose to execute only that particular command from the script, so I really do not care to hear which line of the script it came from: it is the one and only one command that I chose to execute.  Furthermore, if the failed command was several lines long then this blatant lie that Oracle says about line 3 would have sent me on a wild goose chase, looking for an error at line 3 of the failed command.
2. ALTER TABLE "APV_[...]": I know what command I issued, you see, I just issued it, so I do not need to see its full text again, inside the error report.  At most, the command should have been echoed when it was about to be executed.  Furthermore, Oracle does not help me at all to pinpoint the precise location within the command where the error occurred, (see further down,) so including the full text of the command with the error report without telling me where the error occurred is only adding insult to injury.
3. "Error report -": I know that this is an error report, I do not need to be told that it is an error report.  This is a Z.I.S. (Zero Information Statement.)
4. "SQL Error: ORA-00905: missing keyword": A useless message which is giving me absolutely no hint as to what went wrong, and where. This is as good as saying "an error occurred".
5. 00905. 00000 -  "missing keyword": Oracle, you are repeating yourself.
6. "*Cause:"  So, what is the cause?  How lame is this?
7. "*Action:"  So, what is the action?  How lame is this?

So, as it turns out, Oracle Express Edition luckily does understand the exact same SQL syntax as the paid versions.  Now, let's look at how Oracle SQL differs from other relational databases:

1. Numeric data types do not correspond to machine data types. Instead, they are all stored in Binary-Coded-Decimal (BCD) form, and they must be declared in ancient SQL syntax.  So, there is no such thing as INTEGER and BIGINT, there is only NUMBER(10) and NUMBER(19).  The corresponding stupidity holds true for real numbers.  This comes straight from the nineteen sixties.  The middle of the previous frigging century.
2. Identifiers can only be up to 30 characters long.  I do not know whether the folks at Oracle have realized this, but for more than a decade now, we live in the third millennium.  Unlimited identifier length is what we have grown accustomed and expect for decades now. We use long identifiers, quite commonly in excess of 30 characters long, and we also use tools and techniques that automatically construct identifier names from other identifiers.  For example, a foreign key name is commonly constructed as "fk_" + tableName + "_" + keyColumnName.  This all breaks under Oracle.
3. There are no catalogs, and schemas correspond to users.  Which means that in order to create a schema, you need to create a user.  With a password.  And when creating a new user, you must specify something called a tablespace, which must have the magic name "USERS", which will hopefully work on any installation out there. All this means that automated schema creation suddenly becomes very tricky business with Oracle. I understand that features like this are very good for the job security of the sysadmins, but they are very bad for anyone wishing to get any useful work done.
4. The empty string is treated the same as NULL. This is the most devastating difference between Oracle and other RDBMSes, and it alone is enough for me to never recommend Oracle for anything, to anyone. (Except my enemies.)  What it means is that if you write NULL, you get NULL, but if you write an empty string, you also get NULL. There is no option to change this behaviour, and no easy workaround.  This is incredibly lame.  Actually, words cannot express how lame this is.  The lameness of this is of epic proportions.
5. There is no such thing as an AUTOINCREMENT column.  Oracle supports SEQUENCEs, but it does not offer any easy way of specifying that a column should obtain its value from a sequence.  There is a Byzantine way of declaring a TRIGGER which gets triggered once a row is inserted and fills the value of a column with the next value from a sequence, but good luck in getting that bitch to work.
6. There is no LIMIT or TOP clause. In order to limit the number of rows returned by a query you have to embed it as a subquery of an absolutely idiomatic "SELECT * FROM (query) WHERE ROWNUM <= N".
7. The syntax is generally very poor and lacking in features to make the life of the programmers easier.  For example, there are no niceties such as DROP TABLE IF EXISTS and CREATE OR REPLACE SEQUENCE, forcing you to write (and debug) tons of script, which in turn locks you with their product.