Archives
-
Oracle Peeves
I'm changing jobs soon and after nearly 3 years of working with Oracle, and little chance of my using it in the near future, I'd like to share (vent) some of my top Oracle peeves.
I'd like to state up front that I don't hate Oracle, and I haven't had enough experience with other databases to know exactly how good or bad Oracle is in comparison. These are just some little things (hopefully valid) that have irked me over the years [:)]
30 character name limits
I'm not a fan of Hungarian notation or most abbreviations (we spend far more time reading code than writing it so any time saved typing will be lost deciphering, besides auto-complete anyone?) so Oracles 30 character limit on names has annoyed me to no end. It usually isn't a problem with table or column names but when you have a foreign key naming standard that is comprised of the table the key is coming from and going to, things start to get hairy:
E_S_SEC_E_PROC_SUB_TYPE_FK1 [^o)]
select * from dual
To be honest I've always found the dual table kind of facinating although Oracle's limitation does make some do crazy things.
SQL statements automatically handling no_data_found
In PL/SQL when you want to populate a variable with a value from a table you use "select into". The select into statement has to return one row. If none are returned then you get a no_data_found exception. If more than one is returned then you get an aptly named too_many_rows exception. Sound fairly simple? The gotcha is that if the exception is thrown by a function being used inside a SQL statement then it is automatically caught. The hows and whys of this behavior confused the bejesus out of me until I came across this Ask Tom question.
Varchar2
A general grab-bag of aggravating issues around strings in Oracle:
- Apparently Oracle promotes the use of varchar2, rather than varchar, because it wants to avoid problems if the SQL standard is ever changed so that possible new varchar behavior doesn't cause bugs in existing code. DBAs are big on backwards compatibility so a change like that seems a pretty big if to me. Personally I think Oracle should have just stuck with varchar. If the standard ever changed in a way that caused bugs with existing code either used a database flag to turn it on or off or at that point introduce a varchar2 with the new behavior.
- By default the length on a varchar2 column specifies byte length, not the number of characters. Most commonly used English text is single byte but it can be a bug lying in waiting if you are unaware of it.
- Empty strings are considered null. '' = ''? Not in Oracle!
- The string concatenation operator in Oracle is ||. Yuck. To be fair to Oracle this is the operator specified in the ANSI SQL standard.
Memory UsageOracle likes it's RAM. Having a dedicated database machine (at least if you're writing, running and debugging .NET applications from Visual Studio) is pretty much compulsory. Oracle having Java embedded inside it can't help.
Select Into
In PL/SQL you can't use the result from a select in an expression. Instead you have to declare the variable, select into the variable (don't forget no_data_found and too_many_rows error handling!) and only then use the variable in an if test or whatever. Painful.
Package Headers
Packages are a Good Thing™ and I have a feeling I'll miss them in SQL Server land but separate headers to control external visibility is so 1990. Having to modify the header signature along with the body is annoying.
Declaring all variables at the top of a procedure/function
Woah. Sixth Form Certificate programming class Pascal flashback.
Installing Oracle on Linux10g or 9i? R1 or R2? x86 or x86-64? Redhat Enterprise Linux, Fedora Core, SUSE Enterprise Linux or something else?
There are a lot of permutations and luckily for you there are instructions for all of them because Oracle installs on each of them differently. Hurrah!
When you do find the guide you are looking for don't worry, you are halfway there! Only 21 pages of instructions to follow...
Oracle .NET client size
209 megabytes. Compressed.
Update:
To be fair to Oracle, its quirks and WTFs can't compare with the silly things some developers do with it.
-
Adios 2.0
I upgraded to Community Server 2.1 last night and I have to say that the experience was surprisingly painless, even for someone like myself who is not use to SQL Server's heathen ways.Apart from the SQL upgrade script balking because of an old record missing from the cs_SchemaVersion table (I just added it manually) the updating the database went without incident. Upgrading the web application was slightly more time consuming as I had to merge my changes to the *.config files with the new versions but after that I just copied my custom theme directory across and then uploaded it to the server.
Now I just need to find a new blog feature in Community Server 2.1 that I will actually use to make the upgrade worth it [:)]
Update:
The admin area is much improved. Most the rough edges around the post editor and post grid have been smoothed out. Also the new "named" URLs are much better than the old number system. I went through and updated all of the previous posts to use them (the old URL still works).
Overall I give 2.1 two thumbs up [Y] [Y] (did I mention the smilies in blog posts? [:)])
-
WebRequestDetailedErrorEvent
Download DetailedErrorEvent - DetailedErrorEvent dll and C# source code
One of the least talked about new ASP.NET 2.0 features, and one of my favorites, is the health monitoring system. Designed to help you monitor the status of a deployed website, the ASP.NET health monitoring system makes what could potentially be a chore easy. With just a few lines in your site’s web.config you can automatically log a variety of events to a file, a database or even send an email to an email address.
While overall health monitoring it is a great new feature for ASP.NET, the WebRequestErrorEvent class provides disappointingly small amount of information. Raised when an unhandled exception is thrown during a web request, only basic information about the request that caused the error is provided such as the URL, the user host address and the authenticated user. Vital information like the request headers, form values, cookies and server variables are missing, making debugging the error more difficult than it needs to be.
WebRequestDetailedErrorEvent
The WebRequestDetailedErrorEvent class inherits from WebRequestErrorEvent and includes the information that the base class is missing.
One interesting aspect of the health monitoring events is that they are written asynchronously. While this allows them to be buffered and written without impacting performance, it also means that a copy of the request details need to be taken as HttpContext.Current will be null when the FormatCustomEventDetails method is called.
private void Init(Exception exception)
{// FormatCustomEventDetails is called outside of a web request// need to take a copies of all the web request information to logHttpContext context = HttpContext.Current;
if (context != null)
{HttpRequest request = context.Request;_cookies = new HttpCookie[request.Cookies.Count];
request.Cookies.CopyTo(_cookies, 0);
_httpHeaders = new NameValueCollection(request.Headers);
_form = new NameValueCollection(request.Form);
_queryString = new NameValueCollection(request.QueryString);
_serverVariables = new NameValueCollection(request.ServerVariables);
}
else{_cookies = new HttpCookie[0];
_httpHeaders = new NameValueCollection();
_form = new NameValueCollection();
_queryString = new NameValueCollection();
_serverVariables = new NameValueCollection();
}
}
WebRequestDetailedErrorModule
Unfortunately there is no seamless way to automatically raise the new web request error event instead old so a new HttpModule is required to do it for us. WebRequestDetailedErrorModule attaches a method to the Error event of the HttpApplication object and then raises WebRequestDetailedErrorEvent when a web request exception goes unhandled. Not all exceptions raised the default event so some logic is required to filter them out.
private void RaiseErrorEvent(object sender, EventArgs e)
{HttpApplication application = (HttpApplication)sender;
HttpContext context = application.Context;Exception exception = context.Error;// unwrap exception if top exception is an HttpUnhandledExceptionif (exception is HttpUnhandledException && exception.InnerException != null)
exception = exception.InnerException;
HttpException httpException = exception as HttpException;
// don't log file not found exceptionsif (httpException != null && httpException.GetHttpCode() == 404)
return;// viewstate exceptions raise a WebViewStateFailureAuditEvent, not an exception eventif (httpException != null && httpException.InnerException is ViewStateException)
return;WebBaseEvent.Raise(new WebRequestDetailedErrorEvent("An unhandled exception has occurred.", this, WebEventCodes.WebExtendedBase + 3005, 0, exception));
}
The old event will still be raised so if you may want to modify your web.config to not log events numbered 3005.
Usage
To use the WebRequestDetailedErrorEvent simply add the module to your website’s httpModule section.
<httpModules>
<add name="WebRequestDetailedErrorModule" type="Newtonsoft.DetailedErrorEvent.WebRequestDetailedErrorModule, Newtonsoft.DetailedErrorEvent"/>
</httpModules>
And that’s it. The new error event is now raised whenever an unhandled exception is thrown from a web request. Note that if you haven’t already setup ASP.NET to log health monitor events you can find out how here.
Download DetailedErrorEvent - DetailedErrorEvent dll and C# source code