Oracle Database 10g Express Edition and ColdFusion

Today I needed to test an application I wrote against Oracle.  It works with Access, MS SQL and Mysql.  I am a complete noob when it comes to Oracle, and had no idea what changes would need to be made if any.

My first step was to Get Oracle Express (XE).  It is their new free version of Oracle.  It only allows 4GB of storage and 1GB of Ram, but otherwise it seems to be the same.  It would be perfect to test with.

I downloaded it from http://www.oracle.com/technology/software/products/database/xe/index.html and went for the installation.  I didn't realize at first, but there is only one database with XE.  I kept looking around at how to make a new one, and wasn't able to find a way.  The docs are not very helpful to an oracle newbie.  Once I figured that out, I was set to test making a datasource with CFMX that would connect.

At first I followed this Adbobe KB article, as I didn't have CFMX Enterprise.
http://www.macromedia.com/cfusion/knowledgebase/index.cfm?id=tn_18344

But as it turns out, my developers edition of CFMX has the oracle driver already, so I didn't end up using the thin driver above.

When setting up the datasource I used my DB name the same as my datasource.  As far as I could tell, the DB name is XE by default.  Then I had to determine the SID Name.  I looked all over for this in the documentation and help of Oracle, but found nothing to clue me in.  Then after some searching online I found a config file that sets the SID Name.  It is default to XE as well.  So with XE entered into both, plus my new user/pass I connected without problem.

The next task was to import the data. I have an SQL text file for mysql, and a full MS SQL DB to copy over if needed.  I decided to try MS SQL.  I searched around and found various utilities to convert db's.  All for around 30-50$.  Then I noticed that Oracle has their own migration utility. 

Basically you install that utility, download the JAR files for the database kinds you want to import from, and you are good to go.  It wasn't 100% clear while doing the migration, but it seemed to go OK.  I didn't get all my data moved over, but it is possible I moved an empty db instead of the full db over.  I am kind of sick today (working from home), and didn't have the energy to figure out how to try it again.

After that everything worked like a charm.  I guess the only two squirrelly things were the DB Name and SID Name.  Oh, and the migration of table and data structure should that be something you need to do as well.