The Case of The Missing Database Links
By Steven Chan-Development-Oracle on Sep 11, 2008
[Sep 12, 2008 Update: Thanks to questions posted by readers in the comments and via private emails, I've confirmed that there are some EBS products -- including Advanced Planning (APS), Enterprise Data Warehouse (EDW), and Workflow -- that do use database links today. These products' usage of database links have been reviewed and approved by our architects because they do not invoke ATG (or AOL) code in any way, and because their functionality fundamentally requires a distributed architecture. More updates will follow on questions raised about G/L's use of database links via the Financial Statement Generator functionality.
Here's a scenario that might sound familiar to some of you: a customer built an extension for the E-Business Suite Release 11i using database links. When they upgraded their 9iR2 database to 10gR2, they were dismayed to find that those database links stopped working. They logged a Service Request and were surprised to be informed that, "Database links aren't supported with the E-Business Suite."
I ended up being involved in the resulting discussion. The support statement above isn't entirely correct, and the actual truth is more nuanced than you might think. This case illustrates something much more fundamental about "certification" and "support" of generic database features and options that all EBS developers should understand.
Does the E-Business Suite use Database Links Internally?
No. The E-Business Suite is explicitly designed around the notion of a single consolidated data model for all E-Business Suite products, regardless of whether they're in the Procurement, HRMS, Financials, or other EBS product families. Installing the E-Business Suite means that you get everything installed in one place, even if you're using only one product. This model allows you to roll out new functionality incrementally but still have a single global view of your business operations in one place.
Given this architectural strategy, database links between different EBS products aren't necessary. In fact, our internal EBS Development standards recommend against the use of database links many years ago, both because of the strategic requirement to support a global data model as well as technical reasons.
Here are some examples of the technical reasons: ATG code makes extensive use of Autonomous Transactions and dbms_session.set_nls in its core APIs. These functions will not work correctly when called in certain ways across DB links. We've also experienced issues with dbms_session.set_nls when doing commits within distributed transactions.
Can Database Links Be Used for Custom Extensions?
In short: a qualified yes. But this needs a bit more elaboration.
Database links are a standard Oracle database feature. Like most other Oracle database features, there are specific implementation guidelines and some technical limitations when using database links. These limitations and guidelines are documented in the core Oracle Database documentation.
If you're building an E-Business Suite extension relying upon DB links, you should:
Review the base Oracle Database documentation carefully. Any technical restrictions or limitations described in the generic Oracle Database documentation also apply to your custom EBS
Test your custom code to confirm that it works with the current certified database release and doesn't break any existing standard EBS functionality.
- Test your custom code whenever a new database patch or release comes out. New database releases and patchsets fix bugs, introduce new features, and update existing features. As part of our E-Business Suite certification projects, we perform extensive testing to ensure that new patchsets and DB releases don't introduce any regressions into existing EBS code (if they do, we fix those issues as part of our certification). However, we obviously can't make any statements about whether these new patchsets will affect your customizations -- that's something that only you can do.
What About Support?
This is where the specific example of DB links can be generalised to a bigger illustrative principle: If you've built a custom extension that breaks or works strangely after applying a database patch, you're welcome to log a Service Request to engage us in tracking this down. Here's what will happen next:
- Oracle Support will attempt to replicate the issue in a "plain vanilla" E-Business Suite environment -- i.e. one without your custom extension.
- If the issue can be reproduced, we'll issue a patch or workaround.
- If the issue can't be reproduced, we'll be forced to conclude that it's unrelated to the E-Business Suite itself. If you can document a generic test case against the core database functionality, then the Database Support team will get engaged and will issue a patch or workaround as necessary.
The Cost of Maintaining Custom Applications
Any seasoned IT manager knows that the cost of any custom application isn't confined just to the development cycle. After a custom application is rolled out, there are costs associated with maintaining that code. Depending on the complexity of the custom application, those maintenance costs can be very high.
If you plan to build extensions and custom code on top of the E-Business Suite, you must plan on testing your customizations with upgrades to Apps itself and the underlying techstack and deployment options. As complicated as some EBS patches to the functional products might be, the techstack updates can be equally complex. For example, in addition to certifications with new Oracle database server and application tier technologies, in the past decade we've added support for Single Sign-On, third-party authentication system integrations, Real Application Clusters, various types of load-balancers, reverse proxies, IPv6, various types of encryption, new browsers and desktop operating systems, and so on.
If you plan on using any of these technologies, you'll need to test your customizations to ensure that they work with our current certifications... as well as any new versions that we may certify. Certifying new releases, technologies, and deployment topologies with the E-Business Suite is a full-time job for several teams that report to me, so I'm acutely aware of the cost of these types of activities. It's critical to consider these total lifecycle costs if you're planning your own custom extensions to the E-Business Suite.