Just thought I'd stick these out there for anyone else who might run across them. Those of us reared under the friendly wing of SQL Server are in for regular surprises when interacting with Oracle... But hey, what doesn't drive you mad makes you stronger, right?
1. Using a DDL statement inside a transaction automatically commits any outstanding DML statements. I ran into this the other day when I was trying to have a transaction that added a row to a table and added a trigger (dependent on that row) to another table. (This is actually part of my implementation of an OracleCacheDependency, which I intend to share in an article at some point.) If you stepped through the code, everything appeared to function as expected, the exception would be thrown on the add trigger statement, RollBack would be called on the OracleTransaction, and... the new row would remain in the database.
It was actually driving me buggy. I was beginning to wonder if Oracle supported ADO.NET transactions at all because every example (all two of them) that I could find looked just like my implementation. I even tried both the System.Data.OracleClient and the Oracle.DataAccess.Client, which, by the way, require different implementations as the Transaction property on the Oracle-provided provider is read only (you have to create the commands from the connection after starting the transaction, which is, umm, inconvenient in some scenarios).
So I was pulling my hair out, about to give up, when I ran across a single line in the help docs that says "The execution of a DDL statement in the context of a transaction is not recommended since it results in an implicit commit that is not reflected in the state of the OracleTransaction object."
Okay, I guess I'm just spoiled by Microsoft (yes, I am), but I would expect an EXCEPTION to be thrown if I try to do this and not have the code happily carry on as if everything was hunky dory. You'd think that a database that is picky enough to be case sensitive might be picky enough to not let you accidentally commit transactions. And that leads in my #2 gotcha for the day.
2. Oracle is case sensitive when comparing strings. Let me say that again (so I'll remember it). Oracle is case sensitive when comparing strings. Now this point, in itself, is not particularly gotchaful; however, when coupled with a red herring bug report, it can really sneak up on ya and bite ya in the booty. This is just one of those things that you need to keep in the upper buffers when debugging an app with Oracle.
3. (This one is just for good measure; I ran into it a while back.) Oracle 10 no longer uses the old Oracle names resolution service. This means that if you try to use the nifty Visual Studio Add-in and your organization is still using the old Oracle names resolution, you'll have to create manual entries in your tnsnames.ora file(s) just so that you can connect. Even when you do this, it has to be just so or it won't work.
I've had it where you can connect in the net manager but can't connect in the Oracle Explorer using the connections, which is sees and reads from the tnsnames file. In particular, if I removed the DNS suffix from the name of the connection (to make it pretty), it wouldn't work. It'd see the connection but not be able to connect.
4. (Another oldie, but importantie.) Oracle, as of now, does not support ADO.NET 2 System.Transactions at all, if you use the Oracle-provided provider. From what I could tell, although I wasn't able to test successfully, the Microsoft-provided one looks like it should, at least it should use DTC, but the jury is out. Feel free to post if you've gotten it to work.
5. There is no ELMAH provider for Oracle. I implemented one, though, and will be sharing in an article at some point. Feel free to email me for it in the meantime.
6. There is no Oracle cache dependency. See #5.
7. There is no Oracle roles, membership, etc. provider. Sorry, I've not done that yet.
There are other bumps and bruises that you will get when dealing with Oracle if your main experience is SQL Server. Many of them are just due to lack of familiarity, but there are some issues that I think truly make it a less desirable environment to work with. So I thought I'd just share a few of them here for others who might find themselves in similar binds and need the help, which is so hard to find for Oracle.
The opinions expressed herein are solely my own personal opinions, founded or unfounded, rational or not, and you can quote me on that.
Thanks to the good folks at dasBlog!
Copyright © 2017 J. Ambrose Little