Over the past two weeks I’ve been struggling with adding support for Oracle in Squidhead. (Expect a release early next week.) Oracle was more different from both Microsoft SQL and MySQL than they were from each other. It got me thinking about the three and their various pros and cons for code generation, and their pros and cons for me doing code generation with stored procedures. I figured it could make a good blog article. (Or someone could see my post and say “Hey you didn’t have to do all that because of the blah command!”)
Microsoft SQL
This was the first one I started with, so the others are being compared to it. It comes with the built in stored procedure sp_help, which is absolutely brilliant. It gave more information for tables than I ever needed. It also gives most of the information for a stored procedure that I need. Although there are a few things lacking which made me have to resort to using sp_helptext, and then parsing the actual stored procedure. I had to do this to get the number of result sets that a stored procedure returns. (Because if a stored procedure returns more than one record set, Squidhead writes the method to return a structure of queries instead of just a query. ) Also I had to do this to get the metadata of the arguments being passed to a stored procedure.
MySql
Of the three this was the easiest to do. I had a flash one night (“just implement sp_help’s queries in MqSQL”) and it took me less than 24 hours to add it. Nonetheless I still had to query a lot of system tables directly to get the results I wanted.
There are few other limitations that bug me, but they aren’t related to code generation. Things like stored procedures not being able to take default values in their inputs, and views not being able to contain subqueries. I’m sure as those features mature though, we’ll see better results there.
Oracle
Finally, we reach Oracle. It took me a week of hair pulling, keyboard slamming and muttered curses to get it right. But finally I did.
What I didn’t like:
One has to create a cursor to contain output recordsets for a stored procedure. That’s so different than the other systems that I wanted to kill someone. Although, I must confess it makes counting output record sets much easier.
That everything is limited to a name length of 30 characters. So I had to get creative with how to deal with stored procedures with names like usp_entryToComment_list_foreign_key_labels. The solution I came up with was to abbreviate them and rename them higher up in the Squdihead stack so that the functions have the “correct” names.
What I liked:
Packages. Packages are sort of like classes or CFC’s for Oracle operations. The encapsulate code into discreet chunks. I basically create a package for every table so my stored procs have names like ENTRYPKG.READ which is better in my mind than usp_entry_read as that’s just a way I use to group and sort stored procedures anyway.
The sheer amount of data in the various data dictionaries. I don’t have to parse any code strings to get any metadata. That’s much easier.
“CREATE OR REPLACE.” Truly awesome. Cuts down on the amount of steps I have to do to recreate a stored proc or package. I wish I had known it exists for MySQL.
Conclusion
It’s kinda funny, and it may be cognitive dissonance talking, but of the three, I really like Oracle the best for code creation and metadata. Overall, I still lean towards Microsoft Sql as it is the one I know the best. Also whatever your thoughts on Microsoft Sql Server Management Studio, it’s so much better than SQL developer for Oracle. But that’s a completely different topic.
tpryan.blog 
I would have to agree with your comments about oracle… it’s all about sql… everything is available via tables, simple. sql 2005 moved in this direction.
The thing is tho, for 95% of db work, a bound CF query is the same as a SP… CF creates a cursor and it runs pretty much the same as a SP. Unless your doing a lot of work with the data, I would be avoiding SP’s…
it’s much easier to version, change and test an application using just a CF layer for DB interaction
LikeLike
Great article. I’ve often wondered how easy/hard it would be to go from Microsoft SQL Server to Oracle for a long time. I consider them the grandfather of databases since the first Oracle DB was offered in 1979. If you work more with the db please post your experiences. Thanks.
LikeLike
I have worked extensively with both databases. Oracle has gone a long way to be more user friendly since the 9i and 10g releases.
Oracle now support the standard outer join syntax and case statements (even tho oracle’s decode is nicer) which makes migration easy.
There is the XE version which is free for up to 4Gb of data. Beyond that, the major difference between the two is sequences, which is how oracle handles auto-increment pk’s.
Sequences are a lot cleaner and more flexible from my experience. There are always times to use proprietary features in a database, it’s always better to use the more portable features in a database.
LikeLike
1.How can i create database using SQL Server in Visual Studio 2005?
2.Can i have the major syntax differences between MS SQL and MS Access
Thanks
LikeLike
What a nonsense in previous comment! Rubbish! I have several MS Sql severs and couple of Oracle boxes around here and done bunch of comparisons. And I can say – MS SQL not even close! Especially when it comes to scalability! What a disaster with MS with all the dead locks , recourse contention etc. MS SQL doesn’t scale well at all – I had a case when pretty powerful server was dying with 280 ~ 300 or so concurrent users. Performance would grind to a halt and it required great finesse on my part to keep it going. So even with an overabundance of hardware, can’t hack it. They’ve tweaked it, but there is still the point, like all things, of diminishing returns. Oracle is engineered to scale – and scale it does!
LikeLike
It was worth reading article and also the comments, can any one of you share any comparison between the price for both the databases, I mean which one is more economical.
LikeLike