Jeez... where do I start...
1. The output of SQL Server stored procedures is easily used as a result set... for Oracle, you must build a reference cursor in a package. And, you can't just select from the package... you must open the reference cursor and step through it to see the return... absolutely inconvenient from troubleshooting, modifications, and testing.
2. SQL Server preserves the capitalization used on table and column names as they were in the Create statement... works real nice for mixed casing. Even the script generators in SQL Server preserve the casing. In Oracle, it all changes to upper case. Sure, you can write code in mixed case, but if you need to regen a table or the like, you get all upper case. Same holds true in any of the tools you may examine a table/column names with. That's why everyone who uses Oracle also uses underscores.
3. Want a naming convention? Well, even if you don't, you'd better come up with one for Oracle... object names can only be 30 characters in length and you'll chew a bunch of those up with all the underscores you'll want for readability. In SQL Server, they can be 128.
4. Sometimes, when creating sophisticated auditing, ya just gotta build a trigger a little larger than 32k bytes... can't be done in Oracle, though.
5. Just TRY to write a set based trigger in Oracle... just go ahead and try!
6. Need to do a little DDL in your Oracle proc? Be careful!!! Execution of DDL does an auto-commit even if you don't want it to. And to actually do DDL in a proc (including truncating a table), you have to use dynamic SQL because DDL isn't actually allowed in procs.
7. Don't even think about using a temp table in Oracle.
8. Forget about overloading variables in a single select in Oracle.
9. Oracle's error messages will actually lead you away from where the error in the code is. And don't you dare leave out a ";"... well, unless it's required to leave it out and then don't you dare put it in.
10. You can't do a simple select like SELECT 1+2 just for testing purposes or for any other reason in Oracle... at the very least, you'll need to write SELECT 1+2 FROM DUAL.
11. Oracle is case sensitive for strings. Some folks like that, but most do not.
There's a couple of nice-to-have features in Oracle like CONNECT BY for hierarchical data and the fact that they have true "BEFORE" triggers instead of those bloody INSTEAD OF triggers... and, if you really need it and you took the time to install it, it does have RegEx built in... but those little conveniences don't make up for the awful programming interfaces nor the awful requirements and limitations within code.
My opinion... If you're a good set-based programmer, Oracle will frustrate the heck out of you... you get a bigger bang for the buck with SQL Server.
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)