The Case of The Missing Database Links

[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.

Screenshot%20of%20Oracle%2011g%20DB%20product%20center.png

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:

  1. 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 extensions.
     
  2. Test your custom code to confirm that it works with the current certified database release and doesn't break any existing standard EBS functionality.
     
  3. 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.

Related Articles

Comments:

Hi Steven,

Love these bones of contention ;-)

Standard functionality internally within the eBusiness Suite does use Database Links - specifically that to transfer FSG's (Financial Statement Generator reports) between instances (PROD/TEST/DEV). Looking at the R12 General Ledger Users Guide as of now I see:
Prerequisites
• You or your System Administrator must define database links.

You're saying here database links internal to EBS aren't supported yet they're used as part of the standard EBS product? Someone has the wrong message here.

I have to say Oracle Support has become dramatically better over the years, but I'm skeptical whenever I hear the words "Not Supported". Luckily my tech grounding is good and I know very well to treat certain Oracle Support responses with a grain of salt ... or ask my question in a different way ;-)

Gareth

Posted by Gareth Roberts on September 11, 2008 at 11:14 AM PDT #

Hi, Gareth,

Well you know me by now: I'm the one foolish enough to attempt communicating these kinds of heretofore-nebulous policies... and I invariably end up taking the arrows. Seeing me lying face down is usually a good indication to the internal Dev teams that something's amiss in the field. ;-)

I'm not familiar with the FSG functionality that you've mentioned, but from the short description you've provided, I surmise that this is be a one-time migration/setup related dependency for moving content between environments, not a runtime use of database links.

Regards,
Steven

Posted by Steven Chan on September 11, 2008 at 12:09 PM PDT #

What about distributed configuration then when you have "erp" and "aps" instances running. The connection between those is made via database links and used all the time?

I would think thats supported setup as starting from patches there is information provided how to apply them using this configuration.

Just curious - we did upgrade 9ir2 to 10g but didn't notice any issues with db links though.

Simo

Posted by Simo on September 11, 2008 at 03:47 PM PDT #

Hi, Simo,

Thanks for pointing this out. I'll look into this and post and update here with more details.

Regards,
Steven

Posted by Steven Chan on September 12, 2008 at 03:37 AM PDT #

Hi Steven,

I had come across this issue some time back. We had a pl/sql based concurrent program invoking a db link which failed to work within EBS. If the same pl/sql was executed as an anonymous block outside of EBS environment, it worked fine. A simple workaround made it work within the EBS environment. The workaround was to execute the statements with the db links by using the EXECUTE IMMEDIATE dynamic sql statement. This worked like a peach.

Regards,
Karthik

Posted by Karthik Rajasekaran on September 12, 2008 at 06:04 AM PDT #

Hi, Karthik,

Thanks for the comment. I'm sure that our readers experimenting with various approaches for database links appreciate your forging a path through the woods for them.

Regards,
Steven

Posted by Steven Chan on September 16, 2008 at 03:01 AM PDT #

Requirement :- We have requirement to push Data from Production Instance to Non Production Instance. This is only single Directional from Production Instance to Non Production Instance.

Possible Solution Approach :- Create Database Link in Production Instance pointing to Non Production Instance and push the data.

Question - > Is there any SOX or Security Risk involved in this ?
What is the possible solution approach for this requirement ?

Posted by guest on February 03, 2012 at 01:47 PM PST #

Hello, Guest,

We strongly discourage the use of database links, due to the issues noted above.

Every organization's security audits will vary in stringency, particularly around the interpretation of industry-specific regulatory requirements. Your own security auditors would be better-able than Oracle to comment on whether your proposal would pass their audits. I would recommend reviewing your proposal with them directly.

Regards,
Steven

Posted by Steven Chan on February 06, 2012 at 02:33 PM PST #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
4
5
6
7
8
9
10
11
12
13
14
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today