Honest Question on ODAC Support Quality
I am in the process of porting a massive application (5 million lines of code) from DOA to ODAC. The application uses an intermediate access layer, so my job is to port this layer from DOA to ODAC.
I started this over 8 months ago and have found quite a few bugs in ODAC. For each one, I opened a support ticket, often providing a working bugfix. I have a decent understanding of the Oracle Call Interface at a low level, and I’ve extensively customized DOA, so digging into ODAC’s sources has been doable.
Today I’ve practically completed the porting, but all of the tickets I opened months ago are still unresolved, even those for which I provided the bugfix!
The only ticket that was closed involved an EAssertionFailure inside the Package Wizard—and that only happened after I had been very noisy. It took two months of exhaustive effort to escalate through the helpdesk until the developers were reached. It was a draining process, and the helpdesk seemed to misunderstand that my complaints were about the entire support experience, not just that bug.
To summarize, here’s the list of issues I reported that are still open:
1. Package Wizard Generates Lines Too Long for Delphi Editor (#155160, "on hold" since 01 Jul 2024)
The Package Wizard creates source lines that exceed the maximum line length supported by Delphi's integrated editor (5000 characters), resulting in code that not only is unreadable by a human, but can’t even be properly opened in Delphi: it you open it in Delphi, delphi truncates all the lines exceeding this limit.
This seems like a very trivial fix: just add line breaks in the source code you are generating,
2.OCI-22141 Error for RAW Attribute in TOraObject with Unicode Session (#156300, "on hold" since 19 Jul 2024)
When using a Unicode connection, it's impossible to properly read/write attributes of Oracle objects with RAW data types. ODAC forces you to handle these values as strings, and the approach used fails when the session uses an Unicode environment (where each character is has a 2 bytes in memory representation). These attributes must be managed as byte buffers using the low-level OCI API. I provided detailed steps on how to fix it.
3.Cannot Pass Empty PLSQLTable Parameters to Stored Procedures (#161533, "on hold" since 09 Sep 2024)
The internal reason of this bug implies another ODAC bug: it is not possible to execute pl/sql blocks that add or remove rows to the PLSQLTable object being passed.
ODAC fails to pass empty PLSQLTable variables due to incorrect handling of the underlying Oracle OCI API. The underlying OCI API requires these tables to be passed as pre-allocated memory buffers along with TWO distinct parameters: one for capacity (the maximum potential number of elements that can be contained by the provided memory buffer, and this must be at least 1) and one for the actual number of used elements in this pre-allocated array. This second number is a input/output parameter: after the call it is updated to the new number of used elements, after the modifications operated by the plsql block.
ODAC, instead, exposes an API that defines a single 'length' value and uses it for both parameters expected by OCI API, also ignoring the new value after the execution. So:
it is impossible to pass in the correct values for an empty input array (which are capacity=1, used elements = 0)
it is impossible to pass the array to a plsqlblock that adds or removes elements because there is no way of knowing the output value of "used elements count", which ODAC happily discards.
I provided a bugfix that extends TOraParam with an UsedElements property to handle this properly, while ensuring compatibility with existing code. Despite positive feedback from the helpdesk, it seems that developers have not reviewed my fix, as I received no further feedback and the issue remains "on hold."
4. Bug in TCRBlobData.AddCRUnicode: LF at Block Boundary Not Converted (#158781, "on hold" since 10 Sep 2024)There’s a bug in CR/LF conversion for CLOB fields when the LF character is at the boundary of a memory block (CLOB data is streamed in blocks of bytes). I provided a test case and a fix, yet I’m still getting vague responses that the issue is under "investigation." I'd like to understand what are they still investigating, since I already did all the work.
5.TOraObject.AllocObject Takes 4+ Minutes Due to DescribeAttribute Recursion
When TOraObject attributes are described, ODAC recursively describes referenced types, leading to massive delays (4+ minutes in my schema with 2000+ Oracle object types). DOA handles this "on demand" without preemptive recursion, and I suggested ODAC do the same. I didn't provide a fix, so I expect this will be "investigated" indefinitely.
6.ODAC Allocates 3 GB for a Query with Only 32 varchar2 Fields
(I opened this ticket only last week, so I can't be really angry about this... I just have a very bad feeling about it given the past experiences).
ODAC uses OCIDefineByPos with OCI_DEFAULT for varchar2, causing absurd memory allocation. For example, if you execute a query with 1000 calculated varchar2 fields (SELECT dbms_random.string('X', 1) AS f1, dbms_random.string('X', 1) AS f2, ..., dbms_random.string('X', 1) AS f1000 FROM dual) and leave FetchRows at its default value of 20, ODAC attempts to allocate 65536 * 1000 * 20 = 1.25 GB of memory, which is insane.
Before you ask: TOraQuery.Unidirectional = true doesn't help: this happens well before data is actually fetched.
I suggested using OCI_DYNAMIC_FETCH to solve this issue, as it utilizes a streaming mechanism that significantly reduces memory consumption to the size of a very small single buffer (it could be a single 16Kbyte buffer, for example).
I’ve already fixed myself the very same issue in AllRoundAutomation's DOA, so I know a memory-efficient solution is more than feasible.
In case you are wondering where the 65536 bytes value comes from: oracle, for calculated string fields, reports the maximum possible string length, since it can't know during OCIParse the actual size of the strings that will be returned by the computation, so it reports the maximum Varchar2 size, which is 32767 if you have enabled extended varchar2s in your oracle installation. This needs to be multiplied by two if you are using an OCI environment, since under windows unicode strings use UTF16.
In Conclusion:
I am genuinely frustrated to see that urgent and blocking issues, which are preventing me from using ODAC in production, are being indefinitely postponed rather than resolved with the urgency they clearly deserve. Now, I am at the point where I need to go into production—I no longer have eight months of development time ahead before release: I have finished my part: I really need Devart to start fixing bugs.