Oracle 10g XE + APEX = blessing or curse? (Part I)

[Gena01]

Preface

This article is something I've been planning to write for some time now. It's also my third time trying to sit down and putting it all together. As you can guess I've been pushing it off and it has been quite a bumpy ride. It's also something that I mentioned to a few people who said I should finally sit down and put together all the things that I had to go through and what we got at the end.

Introduction

So I've heard about Oracle 10 XE (or Express edition) for some time, but somehow I wasn't too excited to go and try it. If you've been reading my blog then you know that I've been trying to get more into Oracle and see what it's all about. Don't get me wrong i've been doing SQL queries for a long time. Including building the right indexes and doing query optimizations. This time I wanted to learn how Oracle works so that I could understand the architecture. This is also a great thing to know, because once you know how things work you have a better idea of what to do and what not to do. You'd be surprised how little developers know about the databases that they use daily. Also overall Oracle Database software is very impressive and has a ton of very cool and advanced features. In many ways it's also technically superior to some other products that I had a chance to work with before. However Oracle licensing is very very screwy and complicated. It's so complicated that people have created start ups that exist to help customers optimize the licensing of Oracle to the customers needs. Also Oracle tries to make you pay at any opportunity that it can. One example is that the first link on the right side of the Oracle XE homepage is a guide on how to convert an XE to a Standard edition. I was quite confused as to why it was there and what was the point until I got the agenda of the link. If you decide to pay up there's a whole guide to get you to a supported edition and platform. Another item to add here is that Oracle provides a lot of documentation that you can download or browse online. However finding what you need is another story.

So I am on a quest for knowledge. After finishing Tom Kyte's book and subscribing to a number of Oracle podcasts i started my journey into the Oracle world. This is the world where Oracle DBA's and professionals live. During my quest I started hearing some interesting success stories about how great Oracle products were. Most of this stuff I don't pay much attention to since it's almost always tied into marketing. One thing that was interesting though was the mention of something called APEX or Application Express. It's also something that comes with the free download of Oracle 10g XE. After doing a bit more research i found out that Tom Kyte was actually quite involved with the XE product and is one of the key people behind XE support forum.

So about a month ago (before my trip to PHP|TEK 2008) I downloaded Oracle 10g XE and had a goal of getting it running on my Windows laptop during the conference and to see what this APEX thing was all about. From what I heard was that you can build a complete web application very very quickly. This application would also provide professional web interface and enterprise level security. I was quite curious to the point of possibly making it stack up against LAMP/WAMP. For those that don't know the terminology basically to compare it to PHP and MYSQL project implementations. And to see how the two compare.

Things started off rather fine on my home desktop system. But when I tried to get Oracle going on the laptop that I took to the conference things didn't go so well. There's an Oracle "XE" support forum. The only catch is that it is a private forum which requires a separate registration. To make things even worse than that my registration resulted in a cryptic error message. So no Oracle "XE" forum for me. No support from Tom Kyte. There's also not much information in the provided documentation. I tracked down Christopher Jones during the conference and showed him the errors I was getting. He tried to help by escalating things within Oracle, however until today I am unable to access the XE forum. I tried to Google for answers, but found more questions and more frustrated users.

Somehow the web interface that had to work after install didn't. I was also quite lost and clueless as to why. Oracle installs a whole directory tree full of various strange looking files. I was lost. It seems that log files are all over as are config files and other things. I am NOT an Oracle DBA I don't have 10-15 years of Oracle DBA experience. I was stumped with no clues to help on my quest. I tried rebooting my Windows laptop. Reboot helped and I finally got the web interface to start and I was finally able to access the web management interface (which is powered by APEX 2.1) I was ready to start my challenge and to see if I can get it done.

Challenge

So here was a challenge I put in front of myself:
1. Getting Oracle 10g XE installed and running.
2. Taking an existing MySQL database and converting it to Oracle 10g.
3. Using APEX to generate at least some form of a prototype of a web application.
4. Taking the data and the APEX application and moving that to a separate computer.

As you can see this is not a list of simple things to do. I was in over my head. I was optimistic though. While wondering through the Oracle site I saw a mention of a tool that promised to convert MySQL database into an Oracle one. The tool is called Oracle Migration Workbench (http://www.oracle.com/technology/tech/migration/workbench/index.html). I even managed to read the docs and the release notes. I was ready to take it for a spin. So I grabbed the free download and hit a wall. It kept asking for a plugin to use against my database. Looking at the plugins page there's no plugin for MySQL. I go through the Release Notes again. It clearly states that it supports MySQL and yet nowhere does it say where to get the plugin. So I go to the product forum link and start wondering there and seeing users having the same problem. At one point I see a suggestion to use SQL Developer. Apparently the Oracle Migration Workbench I was using is obsolete? or is it? Also as far as I know SQL Developer (http://www.oracle.com/technology/products/database/sql_developer/index.html) is a developer tool not a migration tool. I guess it does that now? I also find a link to Oracle SQL Developer Migration Workbench (http://www.oracle.com/technology/tech/migration//workbench/index_sqldev_omwb.html). With a download link to SQL Developer 1.5.1 on the right side.  Now I am totally confused. Are these two separate products or one? And why is there two different websites and yet the download has the same name and the version numbers.

Anyway I found some instructions and managed to grab the MySQL JDBC connector piece which I then used in SQL Developer. One nice thing with the SQL Developer site is the viewlets where they show you that MySQL migration works. So I tried to repeat the process and failed. I managed to install the MySQL Connector and things seemed fine until I did the conversion. After cleaning up the dataset things failed again. Apparently the way it works is that it tries to convert ALL databases and users from MySQL to Oracle. Since I setup a special user for the conversion obviously this conversion process would never succeed since there are not enough permissions to create other others. Ok for those that are lost or don't know how Oracle works compared to MySQL: In MySQL you have users and you have databases. In Oracle you have users and every user gets a "workspace" = database. So you need to be an administrative or (SYSTEM) user in order to add/remove users and their workspaces.

Anyway after I backed up and dropped the other databases (besides MYSQL, INFORMATION_SCHEMA and my MySQL database that I wanted to convert) things finally worked and I managed to get the data into Oracle. Another interesting thing is that there's no such thing as "Auto Number" in Oracle. In Oracle we got sequences which are stored in a separate location and are accessed in a specific way. So the conversion tool was smart enough to setup triggers to fake the "AutoNumber" bit. This one is quite cool actually. The behavior is the same as working with MySQL and the AutoNumber gets an assigned increasing value. The only difference is that the Oracle sequences always go up and there are no upper limits since Oracle NUMBER type is not the same as MySQL integer and number types. So after some struggle Step 2 is done.

To be continued...

Gena01

[james]

I have read your info, I was planning on developing oracle 10g xe for a church. So basis on information your It may not be a good ideal to use oracle 10g xe?

[Gena01]

It really depends. I don't know what you were planning to do and what problem(s) you are trying to solve. There are always two sides to the coin. My personal opinion is to find the right tool for the job. There is a number of things that Oracle XE is great for. And I am sure that there are others things where another tool is better.

Gena01

[Dave.]

Hello there.
I've implemented XE on my laptop running Windows XP Pro(SP3).
It was an easy install, and when I call up the localhost:8080/apex address, the ApEx home page comes up no problem.
My issue comes up when I try to upgrade to Apex 3.1 .. the new install is not able to see the images directory.
I'm not sure where to go from here, but hope reigns eternal
-D.

[Gena01]

Ah. Broken images is something I ran into. Apparently the upgrade instructions have changed between 3.0 and 3.1. When you do the upgrade to 3.1 there's less parameters to type in.

Here is the link to 3.1 upgrade page: http://www.oracle.com/technology/products/database/application_express/html/3.1_and_xe.html

And to the 3.0.1 upgrade page: http://www.oracle.com/technology/products/database/application_express/html/3.0.1_and_xe.html

Notice that the upgrade command differs in one parameter.

Also there is another option that comes with the APEX 3.1 archive. In apex\utilities directory there's a script reset_image_prefix.sql

Here is a link to a blog entry on the topic: http://www.techxplore.net/2008/03/11/application-express-31-upgrade-missing-image-and-javascript-error-solution/

Gena01

[vikas]

how to create a horizontal tree in oracle 10g

[Tags:]

Jump to: Please select a destination: ----------------------------- General ----------------------------- => Gena01 Blog => Website ----------------------------- Software Development ----------------------------- => My Software => Win32Get => Win32Pad => Win32Whois