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!”)
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.
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.
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.
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.