Component Workshop Ltd

This is profound in two ways, firstly and most obviously it costs the client less, and enables us to create better relationships. But this productivity gain also allows us to get functionality in front of the user quicker and as such we can identify requirement changes and fix bugs sooner, meaning we deliver better quality code first time.

Agile

We work in an Agile manor and while we can do this regardless of the technology we use, Rails lends itself to an Agile process perfectly.

Solid Design Patterns

Rails encourages the developer to employ consistant design patterns, the rails guys call this Convention over Configuration which promotes maintainable well tested code.

Separation of Concerns

Rails goes beyond Separation of Concerns by removing Concerns, Object Relational Mappers take out 1000's of lines of code that would take up 50% to 80% of the developers time, simple Routing patterns eliminate tones of boiler plate code.

The baked in support for Model View Controller means makes its easy to keep things in the right place.

Open

Over a 10 year contacting career I hit so many hurdles using web frameworks from commercial vendors, from the ASP.NET framework to JSF I found that whenever I wanted to bend these frameworks, they snapped! And the closed source meant that I was back to the drawing board having to invent an ingenues hack, Rails however is open source and the code (especially the rails 3 code) is well written and easy to extend (partly due to the Ruby language), I can easily bend it in any way I choose.

Database Agnostic

As I mentioned the Object Relation Mapper reduces a tone of code and save you even more time, but the abstraction from the database goes even further than that, A rails application written against MYSQL will work against any Database in production, in fact we have an app that is running on SQL Server for one client and Oracle for another, while we host it for other clients on MYSQL.

Just a few reasons why we use Rails, I hope you will too.

The code snippet below shows the Oracle XML functions in action. It is the beset way to understand how it works:

CREATE OR REPLACE
FUNCTION get_accounts_by_email_address(xml_string clob) RETURN SYS_REFCURSOR
IS

  return_data SYS_REFCURSOR;
  xml_data XMLTYPE;

BEGIN

  xml_data := XMLType.CreateXML(xml_string);

  OPEN return_data FOR
    SELECT DISTINCT a.*
      FROM Accounts a
      INNER JOIN Contacts c
        ON a.AccountId = c.AccountId
      INNER JOIN table(xmlsequence(extract(xml_data, '/EmailAddresses/EmailAddress'))) emailaddresses
        ON extract(value(emailaddresses), '//EmailAddress/text()').getStringVal() = c.EmailAddress;

  RETURN return_data;

END

We pass an XML fragment in as a clob. In this example, the fragment would look like this:

<EmailAddresses>
  <EmailAddress>hubert@pretend.com</EmailAddress>
  <EmailAddress>marjory@acme.com</EmailAddress>
  <EmailAddress>kenneth@fish.net</EmailAddress>
</EmailAddresses>

Our database has an Accounts table that can be joined to the Contacts table in a one-to-many relationship. Thus, if we have an email address, we can match it to an account. The method of passing XML in to a procedure gives us a good way of querying an arbitrary number of values in a single request -- we could write a function to return an account for a single address very easily, but we would need to call it n-times for n-addresses.

In the function header we define the variable xml_data as XMLTYPE. This is Oracles native XML type. We initialize the XML document by calling xml_data := XMLType.CreateXML(xml_string) and passing it our clob. Now we can directly query the XML as if it were a table, and join the results (or do any other typical database task, such as INSERT, DELETE or UPDATE based on the XML values). You could create a cursors and loop through the XML nodes if you wanted.

The important code is in the last INNER JOIN:

INNER JOIN table(xmlsequence(extract(xml_data, '/EmailAddresses/EmailAddress'))) emailaddresses
  ON extract(value(emailaddresses), '//EmailAddress/text()').getStringVal() = c.EmailAddress;

We turn EmailAddress XML nodes in to rows of data by selecting the nodes using an XPath query through the extract() function, and then passing it first to xmlsequence() (to create individual rows for each result) and then table() (so the results can be queries using JOIN or ON). In our join caluse we must extract() the value of the XML node before we can compare it to the table we're joining to. The extract method takes the XMLTYPE instance as the first parameter, which in our case is our new table created by the first line (and given the alias emailaddresses), and an XPath query as the second value. An optional third parameter lets you specify the namespace.

I hope you find this article useful. The Oracle documentation isn't bad, and you should definitely check it out if you're writing XML functions in Oracle. The main reason for this article is that there is very little code that actually demonstrates, in simple examples, how to string the various XML functions together. If you have any feedback or would like to see anything else, please get in touch (through the contact page) and let us know.

Here are two useful functions for processing dates. They take a valid date, e.g. “01/05/2007 10:22”, and either set the time to midnight (the start of the day) or 23:59 (the end of the day).

This allows us to use the dates to calculate to the whole day, rather than to a fraction, and to perform basic date management in cases where the time is not an important factor.

The functions:

function Date_ToStartOfDay (d date) return date
as
        return_value date;
begin

        return_value := to_date(to_char(d, 'DD-MM-YYYY'), 'DD-MM-YYYY');
        return return_value;

end;

function Date_ToEndOfDay (d date) return date
as
        return_value date;
begin

        return_value := to_date(to_char(d, 'DD-MM-YYYY') || ' 23:59', 'DD-MM-YYYY HH24:MI');
  return return_value;

end;

A few months ago, I started a contract at a company that almost exclusively use Oracle for their database needs. I am a .NET and Microsoft SQL Server developer by trade. I didn’t lie on my CV – I made it quite clear that my Oracle experience is minimal – and in the interview I again explained that I am a Microsoft SQL Server person. I got the job anyway, and realised that I’d better prepare myself sharpish.

I bought the famous O’Reilly book ‘Oracle PL/SQL Programming’. It proved absolutely invaluable, and I wouldn’t have survived without it. Even if you know T-SQL inside out, you’ve got an uphill struggle if you want to become familiar with PL/SQL in a hurry. This book will make your life much, much easier. There’ll be a link to the book on Amazon at the end of this article.

This is a really long article that will give you many pointers to help you deal with the migration. They are the lessons I learned, and I hope they are useful.

We’ll deal with the language differences later. Before you worry about that too much, you really need to be up and running with a working environment. Once you can try things out it becomes much easier. We’ll install Oracle, install some useful tools, try connecting and running queries, and then we’ll write a small .NET app that connects and executes commands against the Oracle database.

Some assumptions:

Assumption #1: you’ve already downloaded, or you’re about to download Oracle Express Edition 10g release 2.

Assumption #2: you have Visual Studio 2005 professional installed. I suspect the express tools support everything we’re about to do, but I’ve never used them so I can’t guarantee it. You can also forget about the .NET bits if all you’ll be doing is writing PL/SQL and running queries.

Assumption #3: you’re a SQL server expert. I realise that this is quite an assumption to make, and will possibly annoy some people who are trying to get started with Oracle but coming from a different background. I make no apologies. SQL Server is my background and it is my intention that this little guide will help SQL Server experts switch to Oracle as fast as possible.

If you’re familiar with the differences between SQL Server and SQL Server Express you’ll understand the limitations of Oracle Express. It’s a great way to get started, but will only do for light usage in a production environment. You can also download the full Oracle database server and use it under a developer license if you wish, but Express is the easiest version to get up and running quickly.

Installation is very easy. You need to provide a password for the users ‘SYS’ and ‘SYSTEM’, but that’s pretty much it. It’s worth noting a couple of things here. First of all, Oracle doesn’t support the concept of ‘databases’ that you’re used to in SQL Server. Instead, you will use schemas to organise your objects. Microsoft SQL Server also supports schemas, of course, but you usually end up using dbo all of the time which makes them kind of redundant. The SYS schema (and therefore the SYS user account) is where the Oracle system objects are stored. These are the equivelants of the stuff that SQL Server keeps in the MASTER database. You will never log in as SYS.

SYSTEM, on the other hand, is the account you will need to log in with to create your database users and schemas. It is to Oracle as sa is to SQL Server.

Oracle’s way of managing multiple databases is through named instances. Microsoft have named instances for SQL Server now too (so I’m not going to go in to great depth about it here). Briefly, a named instance of a database runs in total isolation from any other named instances. When you install the full version of Oracle, you get to choose a name for that instance during the install. The default is ORCL, so you may well see this name on servers where just one instance is running. The Express edition only supports one instance on a server, and that is named XE. You will use this instance name when you connect from .NET, and this is why I am taking the time to explain it now.

One thing you will quickly notice is that Oracle doesn’t have an enterprise manager. It has a web admin that is slightly sub-standard but fully usable. It also has a huge amount of documentation that is, for the most part, very good. There is also another tool – the equivalent of the now (sadly) deceased Query Analyzer – and that is SQLPlus. It’s a console application that you will probably use quite a bit (there’s a ‘Windows’ version too that comes with full-Oracle, but you must avoid it at all costs, because it is Evil).

Oracle

The first thing to do is try out the database. Oracle XE comes with a demo schema called HR, similar in principle to the Northwind database. To get at it, we need to unlock the hr user account. You do this in the web interface, it’s a very simple task. You should give it a password when you unlock it. I use 'hr', for simplicity.

Once the account is unlocked you should try logging in using SQLPlus., which is found in the Oracle programme folder in your Start menu. SQLPlus is a console application, and doesn’t offer many clues when it is first fired up. You need to type CONNECT <enter>.

Now log in as hr and using the password you set when you unlocked the account.

Now try SELECT * FROM Employees; &ltenter> (the semi-colon is important – because we’re editing one line at a time, the query will only run when you terminate your statement, not when you hit enter).

Hmmm. The results grid is a bit rubbish, isn’t it? I bet you’re thinking “Yep, it’s useless. I suppose you’re going to tell us how to make it better now?”

Um, no. Sorry. I would love to tell you how it can be made more readable, but sadly it’s stuck that way. The tool is still very useful and you will grow to love it if you’re doing any serious Oracle development. There is, thankfully, a much better tool that really kicks the pants off not only SQLPlus, but also Enterprise Manager. It’s called SQL Developer and you can download it for free from the Oracle website.

.NET

Now lets execute our ground-breaking employee search query in a .NET application.

I suggest you start a new ASP.NET application and create a page with a DataGrid. In your project, you need to add a reference to the OracleClient library. Microsoft ships this library with .NET and, as far as I can tell, it’s pretty much exactly the same as the one that Oracle provide on their website but without as much cruft.

In your VS project’s solution explorer, right click the grey References folder and choose Add. Scroll down in the Browse tab to find System.Data.OracleClient. That’s all you need to add. Now add a using for that namespace (or whatever the VB equivelant is, if you insist on using toy languages).

In the page’s OnLoad method, we’ll add something like this:

>OracleConnection c = new OracleConnection();
c.ConnectionString = "Data Source=XE;UID=hr;PWD=hr;"; // Change password as required
c.Open();
OracleCommand cmd = new OracleCommand("SELECT * FROM Employees", c);
MyDataGrid.DataSource = cmd;
MyDataGrid.DataBind();
c.Close();

You, of course, will need to make certain changes depending on passwords and the name of your grid etc.

I think you can see the similarities between the Oracle pre-fixed classes and those that you’re already used to using. They are, to all intents and purposes, the same.

To create a DataSet, use these lines instead of the data binding in the above example:

Again, very similar.

Once we’ve written some PL/SQL we’ll come back and do things properly with the help of stored functions and procedures (because we don’t build SQL strings in our C#, right?)

PL/SQL

This is the point where things get tricky. We’ll quickly run over the standard SQL query language, and then move on to PL/SQL.

The two servers are fairly consistent in their basic handling of SQL. SELECT * FROM table WHERE something = a value; will work on both servers. The same is true for INSERT, UPDATE and DELETE.

Oracle now supports INNER and LEFT OUTER JOIN using the exact same (ANSI standard) syntax as SQL Server. It wasn’t always the case, and you may occasionally come across some strange in-line joins featuring + on the end of certain tables. That means left outer join, for some reason.

Oracle insists on single-quotes for strings (e.g. 'my string') and double quotes for identifiers. This is the ANSI standard, and Microsoft supports it too, through the use of a session parameter. I strongly recommend that you get used to working this way (rather than using square brackets, which I do think look cleaner but are a Microsoft invention). Like with SQL Server identifiers don’t need to be quoted, though it doesn’t hurt.

You concatanate strings using || instead of + (plus is reserved for mathematical uses). This, again, is an ANSI standard (I think). It also applies to variables:

DECLARE

  lFirstName varchar2 := 'Adrian'
  lLastName varchar2 := 'O''Connor'

BEGIN

  SELECT lFirstName || ' ' || lLastName

END

lFirstName + ' ' + lLastName would generate an error.

The names you choose for objects in Oracle can be no more than 30 characters long. It’s not very often you need that many characters, but it does happen and you need to be aware of the limitation.

Oracle procedures, function, packages (and certain other objects too) support the Oracle-only syntax of CREATE OR REPLACE. That’s a great addition to the language and saves you having to script the IF EXISTS ... DROP code that you are probably quite used to.

Packages are worth a special mention because they have no equivalent in SQL Server. They are a way of grouping procedures, functions, types and certain other things together in modules. They can be a little bit cumbersome (at times), but they are ultimately very useful. There are two parts to a package: the specification (where you declare the functions) and the body (where the actual code lives).

User defined types are far more common in Oracle applications than is the case with SQL Server. Cursors are more common too. Let’s create a procedure in hr that returns some data from employees, based on some search criteria passed in as a parameter. First of all, this is the SQL Server version of the code:

CREATE PROCEDURE SearchEmployees (@search_text varchar(30)) AS
SELECT *
    FROM Employees
    WHERE name like '%' + @search_text + '%'

Nice and simple. Our select statement will automatically create a results set that will be returned as a cursor to the client – usually server-side forward-only (depending on client configuration).

Now the same thing in Oracle:

CREATE OR REPLACE FUNCTION SearchEployees (pSearchText varchar2)
RETURNS SYS_REFCURSOR
AS
    results SYS_REFCURSOR
BEGIN
    OPEN results FOR
        SELECT *
            FROM Employees
            WHERE name LIKE '%' || pSearchText || '%';
    return results;
END

Whoa there! is what you’re probably thinking. It certainly is quite a different beast to T-SQL.

First of all, what are we doing writing a TABLE function where we should be using a procedure?

Well, Oracle functions are not quite the same as SQL Server functions. In fact, they are much better – they are equivalent to regular procedures, but procedures don’t return anything whereas functions do. It is something I urge you to consider for a few moments. Procs /can/ return data, using OUT parameters. Lots of developers use these out parameters where a function should have been used. You, of course, know better. You should only use procedures to modify code. You should only use functions to return data.

SYS_REFCURSOR is a special type. The ref means that we don’t send the data in one batch – just a pointer to it. It’s a server side cursor.

Earlier, I mentioned user defined types. They appeared in SQL Server 2000 and confounded us because they had no apparent use. In Oracle they do have a use – they let us create statically typed records and arrays. We can use these instead of SYS_REFCURSOR where we want to force our query to return particular data.

For example, instead of SYS_REFCURSOR, which is dynamic in-so-far as it takes whatever you throw at it, we could have used something like this:

TYPE EmployeeData Employees%ROWTYPE

and returned EmployeeData instead. That % modifier is a little bit of magic that I wish Microsoft would add to SQL Server – it creates an array declaration, based on the table columns. You can also do this:

myVariable Employees.name%TYPE

which creates a regular variable based on the column’s data type. That is an excellent feature.

I suggest you use SQL Developer to enter the above function in to your hr database. We’ll call it in .NET once we’ve discussed a few final points on PL/SQL.

I don’t want this article to become some kind of PL/SQL tutorial. I just want to give you an idea of how certain things that you are used to in SQL Server translate to Orale.

Several final things to note:

Variables don’t start with @. You should find a pre-fix that you like (e.g. p for parameters and l for local variables) otherwise you’ll conflict with column names.

Variable declaration happens outside of the function block. There is a small header where you declare variables and you can assign default values. PL/SQL uses the Pascal style := for assignment.

DECLARE

    lVariable numeric := 0;

BEGIN

   ...

END

SELECT myColumn INTO pMyVariable ...

Oracle doesn’t support IDENTITY, you need to use a Sequence instead.

Oracle doesn’t support temporary tables (at least not as you are used to them). You should try and remove your need for temporary tables (a good idea anyway), maybe using ROWNUM. If you still need the temporary table, I suggest you use a permanent table with a key that is unique (maybe from a sequence).

Calling Functions and Procedures from .NET

Here is the code to call a procudure followed by the code to call a function. It’s very similar to the code you use to call stored procedures in SQL Server, so I shan’t try and explain it. It is my hope that the first time you find yourself needing to call a function, you can use this as a template and then go from there.

Calling a procedure:

OracleCommand Command = new OracleCommand("MyProcName", _Connection);
Command.CommandType = CommandType.StoredProcedure;

Command.Parameters.Add("pFirstName", OracleType.VarChar)
Command.Parameters["pFirstName"] = "Adrian";

Command.Parameters.Add("pLastName", OracleType.VarChar)
Command.Parameters["pLastName"] = "O'Connor";

Command.ExecuteNonQuery();

Callling a function:

OracleCommand Command = new OracleCommand(txtQuery.Text, _Connection);
Command.CommandType = CommandType.StoredProcedure;

Command.Parameters.Add("pFirstName", OracleType.VarChar)
Command.Parameters["pFirstName"] = "Adrian";

Command.Parameters.Add("pLastName", OracleType.VarChar)
Command.Parameters["pLastName"] = "O'Connor";

Command.Parameters.Add("ReturnValue", OracleType.Cursor)
Command.Parameters["ReturnValue"].Direction = ParameterDirection.ReturnValue;

OracleDataAdapter da = new OracleDataAdapter(Command);

DataSet ds = new DataSet();
da.Fill(ds);

You can also ExecuteReader, but bear in mind that until you Close your Reader your Connection will remain in use. This is exactly the same behaviour as for SQL Server.

Closing Thoughts

I appreciate that this article probably lost some of its usefulness by trying to cover so much. However, I do think that all of the information is relevant and I hope that you found it useful. Please let me know if I can improve it, or if you’d like to see several shorter guides.

Anyone who needs to learn to programme PL/SQL really should get this book: Oracle PL/SQL Programming (O’Reilly). It saved my life many times.

In Microsoft SQL Server, you can use the keyword TOP to restrict the number of rows returned by your query, like this:

SELECT TOP 10, * FROM Staff ORDER BY EmploymentStartDate;

In MySQL you can use the limit keyword:

SELECT * FROM Staff ORDER BY EmploymentStartDate LIMIT 10;

With Oracle it isn't so simple. At first glance, it looks as if the ROWNUM virtual column might do the trick, but sadly it doesn’t. ROWNUM is the actual position of a row in the physical database on disk, so it doesn’t correlate with our ORDER.

There is an alternative to ROWNUM that looks like it might work: row_number(). row_number is a 'window' function. It looks at the data after the where clause is applied, and is applied in an order that we specify using the keyword OVER. Unfortunately, because it happens after the WHERE is evaluated, it makes it impossible to use for our purpose of only selecting a certain number of rows. The following code won’t work:

SELECT *, row_number() over (ORDER BY published_date desc) AS rownumber 
    FROM Staff WHERE rownumber <= 10 
    ORDER BY EmploymentStartDate;

Instead, to use row_number() to filter the number of rows returns, we must use a subquery. This is because the sub-query is executed before the outer query - the rownumber has been generated and is available to the WHERE clause.

Here is the working query, implemented as a sub-query on an inner join. Note that we need to join the ordered table to the original table, so we have to select a unique key (usually the primary key) along with the row_number:

SELECT s.*, staff_order.rownumber
    FROM Staff s
    INNER JOIN (SELECT StaffId, row_number() AS rownumber
        over (ORDER BY EmploymentStartDate desc) AS rownumber FROM Staff) staff_order
    WHERE staff_order.rownumber <= 10
    ORDER BY staff_order.rownumber;

The above code will select the 10 most recent employees. Note the over() in the subquery (this applies the sort order of row_number). Note also that we select rownumber in the main select, that we use the where clause to limit the number of rows (acheiving the equivalent of TOP 10), and that we order the main record set by rownumber.

If you want to restrict the results (e.g. WHERE first name is something or other), you must add the WHERE to the sub-query. The outer query will automatically be filtered because we are INNER JOINing the sub-query on StaffId, so only matching StaffIds are there to be joined on.

This is far more complicated than with other database systems, but once you get it working it works well. Sub-queries are less than ideal from a code-maintenance point of view, however, so here is the same code written using WITH:

WITH staff_order AS (
    SELECT StaffId, row_number() over (ORDER BY EmploymentStartDate desc) AS rownumber
        FROM Staff
        WHERE Status = 1
)
SELECT s.*
    FROM Staff s
    INNER JOIN staff_order so
        ON s.StaffId = so.StaffId
    WHERE so.rownumber <= 10
    ORDER BY so.rownumber;

I hope you find this code useful!

For this to work you must be logged in to the Windows Server that is running Oracle as Administrator. Here are the steps to reset the password:

Start SQL*Plus (Start -> Programs -> Oracle – Instance Name -> Application Development -> SQL Plus).

At the login prompt, in the user-name field, type ”/as sysdba” (including the forward slash, but without the quotes). Click OK.

You are now connected as system with full DBA rights.

To reset the password of the SYSTEM password (or any other user password), run the following query:

alter user system identified by NewPasswordHere;

Change the password to whatever you want it to be. Note also that it doesn't need to be in single quotes, as you might expect. Hit ENTER to run the query and you should receive positive affirmation of the change:

“User altered.”

If you don't have SQL*Plus (for example, if you are using Oracle XE), open a console (Start -> Run -> type 'cmd.exe' followed by <Enter> and then run sqlplus from the command line:

sqlplus / as sysdba

Once connected, use alter user as described above.

Apart from the more obvious uses, this tip is particularly useful if you’ve imported a database dump from another server and don’t know the system password.