Plip's Weblog

Oracle Express Edition

Well, I started on my learning journey, and the first victim of my learning was Oracle.

I opted for the Express Edition, mainly because it's free (well I'm not paying to learn it!).

This post is in the guise of me learning, so much of my assumptions may not be true and I emplore those of you that know better to correct me. Also it's coming from a chap who's only ever used SQL Server, so look at is as Oracle Eye for the SQL Guy (as it were - not sure that show would really take off!).

Aims

• Installation and Configuration.
• Understanding of Oracle terminology.
• Basic Administration - Create a Database Schema, Table, Stored Procedure & User.
• Access the Database from a .NET Managed Provider.
• Discover an Oracle community and check out how friendly and helpful the community is.

Installation and Configuration

After downloading the OracleXE (Express Edition - oh how the Marketing Department must have cheered when they came up with that name), I dragged it across onto a specific Virual Machine (Windows XP Professional) I'd created just for Oracle. Logged in as an Administrator on the machine and then ran the setup file. I'm pretty sure, that other than asking me to provide a Password for the sys and system accounts which appear to be the equivelant of the SQL Server sa account, the setup ran without any interaction from myself.

Once installed a new entry had appeared in my Program Files and a Desktop shortcut to Get Started with Oracle 10g Express Edition had appeared on the desktop.

Being a SQL Server user primarily, I knew that the place I wanted to be was Oracle's equivelant of the Microsoft SQL Server 2000 Enterprise Manager or SQL Server 2005 Workbench (Actually why did Microsoft change the name of the tool? Most frustrating.), so I went for a rifle through the Program Files entry to see what was in there and there was a link to Go to Database Homepage which was the only vaguely related link I could find.

On opening the Database Homepage I was presented with a Login dialogue (in a browser window) that had no username or password prefilled. Which would not have been a problem, unless I'd forgotten that on Oracle the administration account is sys and not sa - that took a few seconds of blankly looking at the screen wondering who I should be logging in as!

One thing that was cool, (and the Americans of you reading this really won't care, but for everyone else in the world you will) Oracle picked up my machine was configuired to run as British English (en-GB) and actually set that as it's default, not the usual en-US you get with most other products created by American companies - a very nice touch indeed.

On logging in I was presented with a screen with a few large buttons at the top and a couple of graphs down the site, which takes us nicely into our next topic, of understanding Oracle's terminology.

Understaning Oracle's Terminology

After exploring the interface a little I've discovered the following SQL Server <--> Oracle translations.

Administration is the area in which you can manage the Physical Stoage limits and usage, current Memory consumption, create and manage Users, monitor the Performance of the Server and check out the overall settings of the Server installation. This section is kind of a mix of items you'd finder under the Management & Security settings of SQL Server. In all I've not had to touch any of the settings in here - which is a relief, I'm a developer not a DBA!

The Object Browser is akin to the Tree View you'd find in SQL Server 2000 Enterprise manager, giving you access to all the objects within the Database server. I can't quickly see a way to choose which Database to create or manage items in, so that's a little frustrating.

Next to the Object Browser is a section called SQL quite a broad a sweeping heading one might think, but from what I can tell SQL is the equivelant of the SQL Server Query Analyzer, an area for you to create and modify the SQL Statments you wish to run against your Oracle installation. There is a general SQL Execution section, a Query Builder (which is messy because it's all Browser based) and an area called SQL Scripts, which I believe is for running the Oracle Equivelant of Stored Procedures (but don't hold me to that) - it might just be like saving SQL to a file and running it in Query Analizer yourself!

Back on the Homepage again there is one final section called Utilities which actually looks very interesting, it appears to have the Oracle verion of DTS (or SQL Server Integration Services) in here as well as an internal Reporting Facilility for Analyzing the Database server and how it's objects all interact (find tables without Primary Keys etc - nice idea). There is also a mysterious Generate DLL section in here, which appears to be for creating DLL's which can be used to backup and restore the database schema (I think!).

Although throughout the program there is very little in the way of description there is a full help system integrated into the web site, now the quality of the help is okay - very concise and clean, but it feels much more like a dictionary - in that it explains what a word means as opposed to telling you how you'd create it - I'd never though I'd find myself saying this but I much prefer the MSDN style of documentation and help where you get inline examples which show how to use a feature or utility.

All in all, the terminology seems pretty self explanitory - nice easy navigation and the dictionary definition help system have helped me get through the initial browse around. It's become very clear to me though that I'd much prefer a Windows application to Administer the Database Server as opposed to the Web Application, rich coming from an ASP.NET developer I know, but I just don't feel I trust the Web Interface, I much prefer a real button that is connected by cogs and wheels to the backend system when it comes to something as critical as the Database server.

Basic Administration - Create a Database Schema, Table, Stored Procedure & User

If anything of all the aims I'm trying to get through, this section should be the easiest, I mean SQL is the same on every system right? ;-)

Creating a Database is imperative to starting off with a clean slate, an isolated area for your application development, so that's where I wanted to begin.

Can I work out how to do it through the Web Interface?

Can I hell. There is nothing that I can see anywhere in the Web Interface for Creating a new database, now that doesn't mean it's not there, it just means either I'm being dense and it's looking right at me or that it's called something completly different to "Create Database".

So, I decided to give the old fashioned SQL Route a go and I ran this statment:  create database PhilsSandbox

On running the statment I was presented with this cryptic error message: -

ORA-01501: CREATE DATABASE failed
ORA-01100: database already mounted

Now, am I running into a limitation of the Oracle Express package? Can it only have one Database? I'd already listened to a Podcast on Oracle XE by Tom Kyte but don't recall anything about a single Database limitiation, just a single processor and 4GB database size limit. I went back to the Oracle site to see if I could find anything. From the Oracle XE homepage here's what I could find: -

"Oracle Database XE can be installed on any size host machine with any number of CPUs, but this free version of the world's leading database will store up to 4GB of user data, use up to 1GB of memory, and use one CPU on the host machine."

Again, nothing about the Database creation limitation, if there is one, it's possible at this point I've goofed up big style, so my next task was to go hunting for the error codes printed out with the exeption above.

Whilst on my Travels I found this Gem of a page on the Oracle site, lord knows what's going on there.

Then I stumbled across the answer in a great Article about Oracle XE after running a Google Search for "Create Database" Oracle XE there is a Limitation!

"The third limit is that only a single XE database can run on any given computer. The important point here is that you don't need a database for each application you create, as you might for some competing databases. Instead, Oracle uses the concept of schemas to separate applications."

So, on to creating a Schema, a little more digging in the help system led me to the following paragraph which explains how Schemas and Users are linked together on Oracle: -

"When you create a database user, you also create a schema for that user. A schema is a logical container for the database objects (such as tables, views, triggers, and so on) that the user creates. The schema name is the same as the username and can be used to unambiguously refer to objects owned by the database user."

At last, it's all much clearer to me, finally I can get on with the actual job of creating a table and manipulating it using SQL as well as the IDE. So I created the user PHILSSANDBOXUSER logged out as system and then logged in as PHILSSANDBOXUSER.

Once in under the new username creating a table was relativly simple using the on screen wizard, something that did stand out was that there were only about ten DataTypes presented to me, with no Boolean DataType, something to look into later I think. Anway, the table creation was very simple as was adding some sample data to the table - very easy and obvious. The next step is to create a stored procedure to return a row of Data based on an ID.

So where better to begin than the Oracle Documentation? I did a quick search in the internal help and it pointed me to the Oracle Web Site and this article on Creating Stored Procedures in Oracle (Which you need to log in to see for some inane reason).

CREATE PROCEDURE GetDatabase (Database_Id NUMBER) AS
BEGIN
 DELETE FROM Databases 
 WHERE Databases.DatabaseId = GetDatabase.Database_Id;
END;
/

This worked great, I got the procedure created but then realised I didn't have a clue how to go about executing it from Oracle! I found a piece in the Oracle Documentation which covers Calling Procedures that states it's done like this: -

BEGIN
GetDatabase(1);
END;

So I ran that and got the following back: -

Statement processed.
0.01 seconds

I think that means it worked! Although I don't see the Data which the Stored Procedure selected, so we'll just assume it's all working and move on! (Then I realised I'd typed DELETE and not SELECT - duh!) The Records were gone so it must have worked! Yay!

So there we have it I created a User, Schema, Table and Stored Procedure and executed the Stored Procedure. All relativly simple, even with my never having used Oracle before it was all pretty self explanitory and easy to put together.

Access the Database from a .NET Managed Provider

I was pretty sure that I could do this pretty easily, my experience writing the Professional ADO.NET 2 book has given me a very deep understanding of ADO.NET and how all the outlying sections hang together. So I knew all I had to do was create a new project in Visual Studio and then reference the System.Data.OracleClient DLL in my application so that I could access the OracleClient namespace.

Once I'd done that I knocked together the following code in a few seconds (Thanks to ConnectionStrings.com for help with getting the String right): -

using (OracleConnection Conn = new OracleConnection("Data Source=127.0.0.1;User Id=philssandboxuser;Password=password;Integrated Security=no;"))

using (OracleCommand Comm = new OracleCommand())

   Comm.CommandType = System.Data.CommandType.Text;

   Comm.CommandText = ("SELECT * FROM DATABASES");

   using (OracleDataReader reader = Comm.ExecuteReader(CommandBehavior.CloseConnection))

      while (reader.Read())

         Console.WriteLine(reader["DatabaseName"]);

Now, I know what this means because I'd read about it previously on the Oracle Download page I was running the code on my main machine, trying to talk to the VMWare machine where I had Oracle installed. I needed to install the client software to let my machine talk to Oracle, even through I'm using the built in .NET Provider(!). The same page also said that you don't need to install the Client on the machine which already has Oracle installed - so I took the safe option and just installed the .NET Framework 2.0 on the Oracle box. Wimp? Me? Yes. Oh and 10/10 to the ADO.NET team for placing a meaningful error message in there - I could well imagine something like "Connection could not be opened" as the error message in previous incarnations of .NET or ADO ;-)

Anyway, after copying the console application over to the VMWare image and running the code it printed out the entries of the Database table to the screen. Job Done. Now I know I can interact with Oracle Programatically I can stop using the Admin Interface ;-)

There's the offical Oracle Community called the Oracle Technology Network (what other kind of Network they'd have I don't know but theere you go). The network has forums and blogs and a whole host of items, it looks very active and I'd say is probably a very good place to start with any issues you have, even if it's just to search through the archives.

A much more active community I found was the OraFAQ site, which has some very active forums.

Downloads

You'll need to get down the Express Edition Engine (Oracle Database 10g Express Edition) from the following page if you wish to install it. You can also0 get the client tools there too.

http://www.oracle.com/technology/software/products/database/xe/htdocs/102xewinsoft.html

Pros

• It was relativly simple to find the downloads and work otu which Edition I wanted, I think the word "Express" has become a cross company standard for "Developer Edition & Free".
• Very easy install, I just needed to provide a password for the "system" account - the rest was automatic.
• Works fine on a VMWare image - no issues at all.
• Full sample walkthroughs provided with the application - let me create my first "application" within about 10 mins.

Cons

• Web Based Interface for the Administration tool - I'd much prefer a SQL Enterprise Manager/Workbench type tool if one exists.
• The Interface generally feels quite slow, this could be because it's inside a VMWare Image or because its a Web Based application, but it certainly feels like I'm spending a lot of time waiting for things to happen. 
• It's not particularly intuetive from a SQL Server background, I want to create a new Database, but I can't find the option for example. (It's Schema you muppet!)
• I had to register to download - lord knows what type of Oracle Spam I'll recieve now ;-)

Summary

Cool learning experience, and I can certainly see myself going for work where Oracle is involved now - in one day I've covered lots of areas and trained myself how to use a new Database server as well as talk to it from .NET. A real testemant to Oracle and their usability people.

I still think I prefer SQL Server - that might just be because it's my Microsoft security blanket though!.

Hopefully this piece has helped you - let me know what you think, let me know about your experiences with Oracle.

I want to say a special thanks to Wally McClure of www.aspnetpodcast.com for helping me understand some of the more intricate features of Oracle.

Kind Regards,

Phil.