MySQL and Oracle XE

So lately I’ve been playing around with Oracle XE and MySQL.  It’s funny, most people don’t like to talk to me about MySQL — they seem to assume that since I’m an Oracle guy, I’ll either hate MySQL or not know anything about it.  How can you be a competent database person nowadays and not at least be familiar with MySQL and Postgres in addition to Oracle and/or SQL Server?  Beats me.

I’ve got nothing against MySQL at all — I understand why a lot of people use it (cost, religion, etc).  Within my organization I like to look at what the decision factors are around choosing MySQL and see if / how Oracle and/or Oracle XE matches up.  With XE, cost certainly goes away, so that shouldn’t really be a barrier.  The only barrier I’ve come up against is the 4GB database size limit in XE — seems a little small to me (I understand why there’s a limit) — I would think that 16GB would have been a better limit.

That being said, I’ve been playing with ways to convert MySQL databases into Oracle (XE or otherwise).  It’s kind of fun, since most of the Internet articles detail stuff going the other way.  We’ve got an Oracle XE database installed alongside a MySQL database on a Windows box for testing purposes.  (It’s also a place where I can work with XE and some of the neato web interfaces for administering it).

I’ll talk more about our experiences in future posts, but here’s some of our initial work:

I thought it might be easy to convert from MySQL to Oracle using database links over ODBC (heck, it’s a Windows box — ODBC is just sitting there ready to be used).  And, so far, so good — it was ridiculously easy to set up Oracle’s Heterogeneous Services (even if the name sounds complicated and threatening) :-)  I owe a lot of thanks to these simple crib notes.

Once I had it set up, I could easily select from and describe the tables on the MySQL server.  A couple of notes though:

  1. select count(*) produced really weird results — definately not correct.  Even select count(1) did too — I needed to reference an actual column name in the MySQL table in order to get correct results.
  2. A lot of the stuff in MySQL appears case sensitive — so you get used to enclosing table and column names in double quotes in order to reference them correctly.
  3. That includes username and password attributes in database links.

We’ll be trying some “create table as select” commands later on — I’m looking forward to seeing how we handle type conversions and text / long fields (I’m guessing not so well on the long fields).

For reference, we’re using the following versions: MySQL Server 4.1, MySQL ODBC Driver 3.51, and Oracle XE (Universal)

One Response to “MySQL and Oracle XE”

  1. Mathew Butler Says:

    I had similar fun using HS to migrate some access data to Oracle. I also had problems with long data ( memo datatype translates as a long ). When selecting data over HS where the remote table contains a memo datatype my query would complete after a spoecific number of rows regardless of how many rows there were in the table. It may be an access specific thing, but the bug reference is 5307885 FYI.

Leave a Reply

Posting code can be a pain. To make sure your code doesn't get eaten, you may want to pre-format it first by using HTML Encoder