At least 50% of the time a client will want us to use a specific technology (.NET or Java), this makes perfect sense as a lot of our clients are corperates who have invested a lot of time and money in a particular technology.
The great news for us, and the other 50% of our clients is that we choose to use Ruby On Rails whenever possible. The reasons are many, but a few of the more profound ones are:
Thats right, we are about 2 (maybe 3) times more productive with Rails than .NET or Java, now this isn't because we are primarily Rails guys in fact I have spent many more years working with both .NET and Java than I have with Rails, the simple fact is that Rails gives us a head start as we get so much for Free.
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.
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.
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.
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.
SQL Server 2005 Maintenance Plans have an option for a Maintenance Cleanup Task that is designed to help you delete backup files older than a certain age. However, this feature is buggy, and you might find that backup files are not being deleted, with the result that your disks can become full. This article offers some advice on how you might get this feature working.
There are several things that you can do that might help to resolve this problem. First of all, you must ensure that you are running at least Service Pack 1. SP1 fixed a problem with the Hours/Days/Weeks drop down list. If you have not installed any Service Packs for SQL Server, I would strongly recommend that you do so -- Microsoft fix important security issues with their updates.
Assuming that you are running a recent Service Pack, these are the steps that will probably resolve your issue:
- Remove the dot from the File extension field (i.e., type bak instead of .bak)
- Add a trailing back-slash to the Folder field (i.e., E:\Backups\, not E:\Backups)
After taking those two steps, it will probably work. If not, these are other suggestions that you might try:
- Try using wildcards in the File extension field (E.g., *, .*, *.*)
- Try using different case in the File extension field -- it might be case sensitive (i.e., BAK might be different to bak and Bak), and should match your actual files
If you select from multiple tables using a list of comma separated tables after your FROM keyword (E.G., SELECT * FROM table1, table2) this article explains how to use the far better ANSI standard INNER JOIN to make your queries more readable and, therefore, maintainable.
Many years ago, when I started learning SQL, I used Microsoft’s documentation (because I was learning on SQL Server 6.5) and much of my early knowledge of SQL was based on their way of doing things.
In the Microsoft documentation, nearly all of the examples used the short-hand notation for joins:
SELECT * FROM TableOne t1, TableTwo t2 WHERE t1.SomeKey = t2.Id AND t1.SomeValue = 'search'
I never really liked this, but I couldn’t quite explain why.
It was only when I came across a ‘standards compliant’ ANSI SQL tutorial that I realised that this was, in fact, completely the wrong way of doing it.
Sadly, I still see lots of code being written this way. I recommend that all developers who are writing SQL code should force themselves to use the standards compliant syntax.
The correct way is to use the key words INNER JOIN:
SELECT * FROM TableOne t1 INNER JOIN TableTwo t2 ON t1.SomeKey = t2.Id WHERE t1.SomeValue = 'search'
What you are saying is “join each row of TableTwo to the results in TableOne where the join criteria match.”
It’s more verbose, but it is also much clearer. Clarity is a key feature of good programming style.
The above example is only simple, but as your queries get more complex the benefits grow exponentially. Using the short-hand syntax, our WHERE clause is doing two jobs – it is joining our tables and filtering the results. By doing two jobs we are making it harder to read and harder to understand. With the INNER JOIN, our joins are explicity declared with the table that is being joined. The WHERE clause is filtering the records after all of the joins have completed.
It is also worth noting that you must use the standard language for LEFT OUTER JOINs, a very powerful type of join.
IN and NOT IN are often the cause of otherwise inexplicably slow procedures in SQL Server. This article briefly explains why they should be avoided and how to better implement the same functionality.
IN and NOT IN are a feature of Microsoft SQL Server that should be avoided at all cost. The use of IN can cause a massive bottleneck in your application.
Just so you know what I'm talking about, here's an example of a select statement using IN:
SELECT * FROM TableOne WHERE SomeId IN (SELECT SomeReferece FROM TableTwo)
An example of NOT IN:
SELECT * FROM TableOne WHERE SomeId NOT IN (SELECT SomeReferece FROM TableTwo)
Apart from being messy, the problem is this: SQL Server does not know ahead of time what values are going to be returned by the IN query. This means that it has to run the same query over and over again for each row in TableOne (in our example). That causes a massive overhead.
NOT IN is even worse. With IN, as soon as the sub-query returns a matching value it knows that it can stop searching and move on to the next row. NOT IN requires a check of every single row of the sub-query, because without checking every single row we can’t know if our value is not in that list.
Imaging that TableOne returns 200 rows, and that TableTwo has 500 records. For both examples, SQL Server will run 201 queries. For the first example, we will need to scan between 220 and 100,000 (approx) rows to complete the query, based on the data. For the second example we will always need to scan 100,000 rows. That’s a lot.
Indexes can cover-up the problem somewhat, but you can’t get away from the problem of number of queries – especially if this stops SQL Server from creating a ‘plan’. There are better, more elegant solutions. The two most common are shown here.
To replace IN, we use an INNER JOIN. They are effectively the same thing. This code does exactly the same as example 1:
SELECT t1.* FROM TableOne t1 INNER JOIN TableTwo t2 ON t1.SomeId = t2.SomeReference
We will only ever get rows for t1 where there is a corresponding entry in t2.
NOT IN is effectly saying "where t2 doesn’t have the corresponding row". That requires a left outer join. I shall explain LEFT OUTER JOIN more fully in another article, but what we need to know is that it will return data if it can, other wise it will return an empty result. We can check for the empty result by seeing if the joined column IS NULL. It’s much easier than it sounds.
The following code achieves exactly the same results as example 2 above:
SELECT t1.* FROM TableOne t1 LEFT OUTER JOIN TableTwo t2 ON t1.SomeId = t2.SomeReference WHERE t2.SomeReference IS NULL
Before I finish, I shall say that there is one place where IN might just be OK (although even that is debatable). That one case is when you are supplying a hard-coded list of numbers. E.g.:
WHERE TypeId IN (1, 2, 3, 5, 9)
That is the only time.
Indexes are an incredibly important tool that, if used correctly, will help you see huge performance improvements in your database. This article takes you through the basics.
As a database grows, queries that were once fast slow down quite considerably. If you have any queries of even slight complexity (for example using joins and group by) the performance problems start to appear all the sooner.
The reason is simple: the database needs to find the data that is part of a query each time the query runs. If there is lots of data, and if it is in no particular order, this can be a slow and intensive process.
To speed things up, we use indexes.
Two types of Index
A clustered index is an index that affects the order in which data is stored on the physical disk. If I have a phone book, and I create a clustered index on the LastName field, the record for, e.g., Mr Cumberdale will be stored before, e.g., Mrs Stewart-Baxter.
A non-clustered index is more like an index in a book. It points to where a record can be found.
Both indexes are optional for a table in a database but most tables contain a clustered index by default. Only one clustered index is possible for a table (since the data can only be stored once and in one physical order).
When you create a table that contains a Primary Key, that field will typically become the clustered index automatically. This is by far the best typical usage for clustered indexes on most tables, and you should leave it this way until you more fully understand indexes. Even then, you will almost certainly leave it this way more often than not.
How to Index
In Enterprise Manager (SQL Server 2000), right click the table to be indexed. In the pop-up menu, choose Manage and then Indexes. You can click ‘Add’ to create a new index.
In SQL Server Management Studio (SQL Server 2005 and later) ‘open’ the table’s icon in the navigation pane, and then open the sub-folder ‘Indexes’. Right click the Indexes folder and choose ‘New Index’.
I usually name my indexes “Idx_TableName”. If I need more than one index (mostly you won’t, and you should certainly try and avoid it) I use another _Suffix to describe the reason for the index.
You click the tick-box next to each column name to add it to the index. In SQL Server 2005 you need to click the ‘Add’ button to see these check-boxes.
What to Index
You must not index all of the columns in a table. Instead, you should index the columns that are used for joining, searching and ordering.
For example, consider this query:
SELECT PersonId, FirstName, LastName, PhoneNumber, CompanyName FROM People p INNER JOIN Company c ON c.CompanyId = p.CompanyId WHERE p.LastName LIKE 'a%' ORDER BY p.FirstName, p.LastName
We’re going to index the People table.
First of all, I always index the primary key column, even if it already has an index of its own. I don’t believe this is necessary, but I used to think that it helped the query ‘link’ the data in one index to the real data (via the clustered index) and it certainly doesn’t hurt.
Next, we index columns that we JOIN on. In this case, CompanyId. This is quite crucial, especially where either table is large.
Additionally (and equally importantly), we index columns that we search on in our WHERE clauses. LastName is the only column in this particular query, but FirstName is another likely candidate.
Finally, we index columns that are part of the ORDER BY: FirstName and LastName again.
So, our index will contain the following columns:
Notice that we didn’t index PhoneNumber – that’s because we don’t search on it. We can imagine other non-searched columns – Notes, FavouriteColour, etc etc. They would not be indexed either.
Column Order in Indexes
The order of the columns in your indexes is important. If a column is used for joining to other tables much, it is a candidate for being very near the top of the list, after the primary key.
Out of the non-joined columns (e.g., those used purely to search) that remain in the index, you should try and consider the type of data in those columns. Columns that are ‘more distinguishing’ should be placed nearer the top. E.g., City should be placed before Country, since for most records Country will be the same (and will not distinguish one record from another).
However, you might search far more often on one column than another, even though it is less distinguishing. In this case, it makes sense to place the more-often searched column nearer the top. E.g., Town vs Street.
Indexes slow things down!
Please understand, indexes will make your database significantly faster. However, indexes only improve the performance of SELECTs. They actually slow down UPDATEs, INSERTs and DELETEs. This is because the index needs to be updated and sometimes rebuilt when data changes. Thus, the smaller your index, the less impact it will have when data changes.
If you have a clustered index on anything other than an auto-incrementing IDENTITY field, you must be aware that updating the data in that field will potentially require lots of data to be physically moved between files. This can have a significant impact on performance!
Practice makes perfect
There is no substitute for experience. The more you work with databases the more you will naturally understand what to index and when. It is a vital skill that makes the difference between a sluggish system and a lightening fast one.
I hope this advice is useful to you. Please feedback if you notice anything that I have missed or could improve.
When you write stored procedures, which are an excellent way of encapsulating functionality and improving performance, you should avoid the use of EXEC and EXECUTE keywords at all costs. This article explains why.
This advice applies to Microsoft SQL Server. It might also affect other database servers too.
When you write stored procedures, which are an excellent way of encapsulating functionality and improving performance, you should avoid the use of EXEC and EXECUTE keywords at all costs.
Many developers try and break down larger procedures to create smaller modules. The temptation, from a code-cleanliness point of view, is strong, but as soon as you do this you will cripple the performance of your procedures. Even if your procedure is not critical, you must be aware of the knock-on effects that a long-running query can have on the rest of a database or database server.
The reason that EXEC causes such a slowdown is simple: the server can not plan ahead for queries that are executed as a result of EXEC.
SQL Server can only work so incredibly fast because it can (ordinarily) plan ahead. These plans involve not only choosing between tables versus indexes, but lots of very fine detailed information about the methods it will use to perform each individual component of a query (taking into account ordering, joins and many other elements). Without a plan, it has to do this each time it encounters a query, and it takes a long time.
Well written procedures are good because a plan can be created the first time a stored procedure is called and then cached. That’s why the first time you run a new or modified stored procedure it might take 1 second to run, but subsequently it can return instant results.
By calling EXEC, you are preventing SQL Server from creating a plan for the entire procedure and every single query that it calls, either directly or indirectly.
Learn how to page data inside SQL Server using the rank() function to create virtual row numbers for your recordsets. For high volume databases or large recordsets, this method is significantly faster than returning all of the data to your client application and paging it in memory
I have always been an advocate of performing as much data-shaping as possible inside my stored procedures. Stored procedures are far better (and significantly faster) at working with data than .NET can ever be.
For serious applications, this meant handling ‘paging’ inside the procedure. Typically, my procedures would take a page number as a paramter and, using an in-memory TABLE variable with an IDENTITY column, return just the results for the current page.
With SQL Server 2005 and 2008 there is a better way. The system function rank() can work out a row number for our results on the fly. As always, examples are for NorthWind:
SELECT *, rank() OVER (ORDER BY ProductName) FROM Products;
If you run the query, you will see that the last column is an auto-incrementing field.
That looks great - absolutely perfect for paging, and some of you even probably tried doing something like this already:
SELECT *, rank() OVER (ORDER BY ProductName) AS rank FROM Products WHERE rank BETWEEN 11 AND 20
Yikes. It looks good, but sadly the result of rank() isn’t available to the WHERE clause (because rank runs after the WHERE to number only the results that actually match our criteria).
We can get around this by ranking our results in a sub-query. An outer query can restrict the rows based on the value produced by rank. This means that our sub-query must contain the main where clause for any search criteria we might have (because only the successful matches will be ranked), and the OVER (ORDER BY … must be the order we wish our results to be returned in. Here’s the full query (written to use WITH instead of a regular sub-query):
WITH ProductRowNumber (ProductId, RowNum) AS ( SELECT ProductId, rank() OVER (ORDER BY ProductName) AS RowNum FROM Products ) SELECT * FROM Products p INNER JOIN ProductRowNumber pn ON pn.ProductId = p.ProductId WHERE pn.RowNum BETWEEN 11 AND 20 ORDER BY pn.RowNum
You would need to work out the values passed to BETWEEN based on the current page number (and size), but other than that it’s ready to go.
SQL Server’s support for ANSI SQL includes the WITH keyword. With a little effort, this little gem lets you remove awkward sub-queries from the body of a SELECT statement, turning them in to tables that can be joined like any other.
Sub-queries are bad for several reasons. Most of all, they make queries harder to read. There is no obvious way to indent a sub-query, so it kind of sits in the middle of what would otherwise be a well formated statement. This can obscure the true behaviour and intentions of the query. Furthermore, they can severely hurt performance. SQL Server might need to execute a complex sub-query seperately for every single row that is in an outer results set. If we’re interested in performance and scalability, we need SQL Server to be able to execute our entire query with just one plan.
Some queries are often just uneccessary and ill-thought out. I have seen sub-queries where joins should have been used on many an occassion. This kind of subquery is always hurting perofrmance. As a general rule of thumb, if your sub-query is not performing any aggregate calculations (COUNT, AVG, MAX with GROUP BY) then it can probably be re-written as a join.
Here, however, we are not intersted in that kind of mis-use. I am going to show you how to turn a query containing a genuine sub-query in to a far more readable block of code.
The following query, complete with a typical subquery, returns the most expensive product for each category in a sample database (we are, of course, talking about NorthWind):
SELECT c.CategoryName, p.ProductName, p.UnitPrice FROM Categories c INNER JOIN (SELECT CategoryId, MAX(UnitPrice) AS MaxPrice FROM Products GROUP BY CategoryId) maxprice ON maxprice.CategoryId = c.CategoryId INNER JOIN Products p ON p.CategoryId = c.CategoryId AND p.UnitPrice = maxprice.MaxPrice ORDER BY MaxPrice DESC
What we will do, using WITH, is remove the subquery from the main body of our query, and reference it using an alias:
WITH MostExpensiveProducts (CategoryId, MaxUnitPrice) AS ( SELECT CategoryId, MAX(UnitPrice) FROM Products GROUP BY CategoryId ) SELECT c.CategoryName, p.ProductName, p.UnitPrice FROM Categories c INNER JOIN MostExpensiveProducts mep ON mep.CategoryId = c.CategoryId INNER JOIN Products p ON p.CategoryId = mep.CategoryId AND p.UnitPrice = mep.MaxUnitPrice ORDER BY mep.MaxUnitPrice DESC;
In our first query where we had a subquery, we now have the table name “MostExpensiveProducts”. It is much easier to see, at a glance, what this table is doing.
Although it is only a hunch, I suspect that SQL Server is also better able to create a plan for the aliased query that will not become invalid. That’s just a hunch.
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 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.
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).
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; <enter> (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.
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?)
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:
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 ... ENDVariable 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.
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.
We are a small British company that produces business-oriented software and solutions. These articles are a product of our daily work - information that we think might be useful to share. We hope you find them useful.
These are some of our products. Several are open source, some are web-based and others are proprietary:
- .NET (10)
- Apple (2)
- Business (5)
- CSS (1)
- HTML (2)
- Innovation (4)
- Java (4)
- Leadership (1)
- MySQL (2)
- Oracle (6)
- Postgres (1)
- Programming (5)
- Rails (4)
- Ruby (10)
- SQL Server (9)
- Subversion (1)
- Web (5)
- Windows Server (2)