Arctus Blog

If you are a developer used to working with Microsoft SQL Server, your first experience with Oracle is likely to be a bit frustrating. The two systems are more different than you would imagine, and there is no nice point-and-click learn-by-trial-and-error interface in Oracle. This article will hopefully provide a helpful push in the right direction if you find yourself needing to learn how to develop on Oracle.

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 wirthout 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 equivelant 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. [link]

.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:

OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet() // -- No Oracle prefix!
ds.Fill(ds);

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 occassionally 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 equivelant 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 delcare variables and you can assign default values. PL/SQL uses the Pascal style := for assignment.

DECLARE

    lVariable numeric := 0;

BEGIN

   ...

END

Variable assignment in a SELECT statement requires the INTO keyword.

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.