Just a quick sentence to summarize the entire post: Oracle COM Automation doesn’t work.
¿What is Oracle COM Automation? From the Oracle docs: «enables you to use Component Object Model (COM)-based components to customize and enhance the functionality of the Oracle database on Windows operating systems, […] providing a mechanism to manipulate COM objects through either PL/SQL or Java». You can basically get or set the value of an exposed property and Invoke a method on an object.
We started developing using Oracle COM Automation because a customer needed to access a COM interface from Oracle. We made some basic testing and after a lot of trouble, we end up learning that the solution doesn’t work. Here’s why:
1- It has a major flaw. As explained before, you can use getProperty to get an exposed property’s value. Well, if a property has a null/blank value, getProperty will give you the last value returned by a getProperty. Just as this one in pseudocode:
* Property A = 'green' * Property B = 'yellow' * Property C = '' (blank, empty) getProperty('A') --> 'green' getProperty('B') --> 'yellow' getProperty('C') --> 'yellow' getProperty('A') --> 'green' getProperty('C') --> 'green'
As you may think, this renders the entire solution unusable. Only if you can be 100% sure that all the properties have a value, the solution can be used. This was discussed at Oracle forums here.
Even when Oracle COM Automation is a .dll (orawpcom10.dll, orawpcom11.dll, …), it has a small part of PL/SQL code. We tried to fix this bug digging into the visible part of the code, but it seems that the problem is in the .dll part and there is nothing we can do to fix it. Just this point makes us think if there is anyone in the world using Oracle COM Automation with this important problem.
2- It leaks memory. Each time you make a call to some COM Automation function, being it «invoke», «getProperty», «setProperty», etc. it seems the call is made through «extproc.exe». This process starts increasing the RAM used and I’ve seen it using more than 1 Gb RAM, and that’s destroying all the COM objects at each invocation and weird things like that.
We don’t know for sure if the problem is COM Automation related or «extproc.exe» related, but we’ve used «extproc.exe» several times (deploying .NET code into Oracle) and the process seems to manage its memory automatically. We can see as it increases its memory used and then it lowers it, but not with COM Automation. Newly, we can think this problem is COM Automation related and it could be a problem if you’re on a server that gives 24×7 service and you can’t reboot it frequently.
3- Lack of documentation. Not a «it doesn’t work» thing, but the entire solution is very badly documented. It’s being included from at least Oracle 8, but the docs are not complete, sometimes misleading and there are almost no examples around the Web besides an instructive post in DevX written by Natalka Roshak.
Oracle provides some example files of interaction between PL/SQL code and some Microsoft Office apps via COM interface, but that examples are extremely simple, not exploiting some features you’re going to need while interacting with more complex code, and the most important part: that examples doesn’t trigger the bugs mentioned in point 1 and 2.