The SitePoint Forums have moved.

You can now find them here.
This forum is now closed to new posts, but you can browse existing content.
You can find out more information about the move and how to open a new account (if necessary) here.
If you get stuck you can get support by emailing forums@sitepoint.com

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

What I think Oracle could learn though is how to make web developers love them. Web apps have slightly different requirements on databases than LAN based distributed applications and there are a number of common problems you need to solve, which MySQL is geared to but in Oracle is real pain to solve.

For example, say I want a "page result set" (like the "Page 1, Page 2 ... Next" stuff you commonly get on search engines).

In MySQL you might have a query like this;

Code:

SELECT col1, col2 FROM table ORDER BY col2 LIMIT 50, 10

To do the same in Oracle we're talking;

Code:

SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY col2) RN, col1, col2 FROM table ORDER BY col2) WHERE RN BETWEEN 50 AND 60

That means loads more coding in my script (php).

Also, there seems to be some problem getting Oracle to tell you how many rows were returned in a SELECT (see OCIrowcount) which is easily done in MySQL ( see mysql_num_rows). That basically forces you to run a query twice, first as a count(*).

There's also grief with things like LONG column type is Oracle, which I've yet to entirely pin down but in MySQL you have TEXT / BLOB and never need to worry about it.

Now I'm no Oracle guru, and perhaps I'm missing the point. But things like these, combined with Oracles myriad of useless GUI tools to "help" you administrate your database (compared to phpMyAdmin and powerful command line tools) really turn me off.

I realise that Oracle is far more powerful, when you take into account pl/SQL and all the stuff like the v$tables for monitoring what's going on on the database but the point is it takes someone perhaps a few weeks to get to grips with MySQL while with Oracle - a few months / years?

I'm working with Oracle 8i btw, so may be newer versions have improved.

Having developed extensively in Oracle which included four different interfaces for one application, I can say that I still prefer Oracle over MySQL.

You are correct in saying that when using simple queries, MySQL is easier and maybe a limit clause would be better but you are missing the power of Oracle when you use simple queries.

In the application I worked on, there was two LAN-based interfaces. One was for handling incoming calls and written in Visual Basic. One was for real-time mapping and calculating turn-by-turn directions to the actual patrol cars. There was a Web-Based interface based on ASP and Crystal Reports that allowed supervisors to pull reports from any workstation. And finally there was a Visual Basic program run on mounted laptops in the patrol cars that used encrypted Cellular communications over CDPD.

The beauty of Oracle is that all the database code could be put into Stored Procedures. From there we could build dynamic stored procedures, temporary tables, and views. So while our mobile interface application was only 400K in Visual Basic, the entirety of the work was done in the database on super fast IBM RS/6000 computers. All the mobile application did was parse and send and receive 400 byte strings. From those strings we could tell where the car was anywhere in the world (20 bytes), what their status was (2 bytes), speed (4 bytes), direction (2 bytes), and other information such as the incident they were working on, officer id and more. This was all run off a massive trigger that determined where that string should go.

The reporting system (web-based), call handler (Visual Basic) and Mapping system (C++), also used the same stored procedures, temporary tables and other data.

Now of course we could have done the reports by using simple select based queries with limit clauses, however the web-server was a pathetic 200 Mhz Pentium II running Windows NT compared to the RS/6000 which had thirty-two 1 Gigahertz processors, 256 gigabytes of RAM and over a terabyte of storage. By using the RS/6000's power to format the reports and return them in an XML format we saved a lot of time getting them to the web-browser... A report with 20-30,000 items would take under a second to process in the database.

In the end the database had 844 tables, 1.4 million lines of code and we added over 1 million records a day. Our the code that generated our reports on the ASP server was 26 lines of ASP and a CSS style sheet.

Even today, I would prefer to develop in Oracle then I would in MySQL.

For the patrol part that I was working on, there were three of us... A C++ programmer, a Visual Basic Programmer and myself which did both C++ and Visual Basic. Plus we all worked on the Oracle programming.

I think a major hurdle w/ Oracle is the cryptic error messages. I'm learning Oracle (which has numerous features not available in MySQL) and when I make a typo on a SQL query I get a very cryptic error message. Instead of researching what the error number or short message means, I paste the same query into a PostgreSQL database and get a straightforward error message and a pointer to exactly where in the query the mistake is. Oracle could really learn and make it more accessable, but I guess selling training courses is more profitable.

Plus Oracle took 3 hours to install and 500 megs of RAM on my computer. I just want to learn it... there's no obvious quit option anywhere forcing me to use msconfig or the task manager to make my computer usable again! (But then again, it's obviously designed to be the exclusive software on a computer so in the big picture this makes sense.)

Originally posted by Owen Plus Oracle took 3 hours to install and 500 megs of RAM on my computer. I just want to learn it... there's no obvious quit option anywhere forcing me to use msconfig or the task manager to make my computer usable again! (But then again, it's obviously designed to be the exclusive software on a computer so in the big picture this makes sense.)

Here's a funny analogy I found when trying to figure out how to return result sets from Oracle stored procedures:

Buying Oracle is like buying a car "as is". It comes with 3 flat tires, a burnt-out headlight, 5% brakes left, and an engine that will blow a rod within 3 weeks. The engine looks like something out of the '70s. There is no room under the hood, and 90% of what you see are smog control devices. Upon closer inspection, half the sparks plugs and the carburetor are missing. A look at the driver's instruments is similarly interesting. Here you'll find not one, but many stick shifts each with 200 gears labelled in hexadecimal which somehow imply functionality. You will see a steering wheel the size of a barge's, as well as a built-in 8-track tape player. The CD player, an after-thought, is dangling from two wires under the glove box. The ignition switch is nowhere to be found.

Note I'm not by any means saying MySQL is better than Oracle - there's a ton of stuff Oracle can do which MySQL can't and for big databases, where uptime and maintanence are critical, or anything where queries and data are performed / stored on multiple systems, Oracle is clearly the right choice over MySQL.

Thing is, as web deployment (Internet or Intranet) is surely the future for applications, I wonder if Oracle should have a serious think about their game plan, perhaps releasing a stripped down version of Oracle (for alot less $$$) geared specifically to web sites and web developers.

Although I'm by no means a db expert, Matts quote there rings true with some of the stuff I've had to deal with...

Sounds good, but I *never* want it to run at startup, but I still want a way to run it on demand. Is there any way to do this?

Anywhoo, I think comparing MySQL and Oracle is like comparing Tangerines and Oranges. They're similar but different enough it's hard to make a meaningful comparison. Or not. Or maybe I just thought that analogy sounded nifty